How to Create Dynamic List Boxes.

[tara99]

I was wondering if can some help me with creating a dynamic list box.

I have a combobox with the list of items (item1, item2, item3).
item1 has 5 sub items
item2 has 10 sub items
item3 has 2 sub items

I want the size of the list box to change depending on the number of sub items available.

I hope this is clear.

Thanks

[MMcCarthy]

I was wondering if can some help me with creating a dynamic list box.

I have a combobox with the list of items (item1, item2, item3).
item1 has 5 sub items
item2 has 10 sub items
item3 has 2 sub items

I want the size of the list box to change depending on the number of sub items available.

I hope this is clear.

Thanks

The only way to do this is using code. In the after update event of the combo box you will need to change the listbox properties.

SELECT CASE comboboxName
 
Case "item1"
   Me.listboxName.ColumnCount = 5
 
Case "item2"
   Me.listboxName.ColumnCount = 10
 
Case "item3"
   Me.listboxName.ColumnCount = 2
 
End SELECT
 
Me.listboxName.Requery
 
 

You can also change column widths in this manner if required.

Mary

[tara99]

The only way to do this is using code. In the after update event of the combo box you will need to change the listbox properties.

SELECT CASE comboboxName
 
Case "item1"
   Me.listboxName.ColumnCount = 5
 
Case "item2"
   Me.listboxName.ColumnCount = 10
 
Case "item3"
   Me.listboxName.ColumnCount = 2
 
End SELECT
 
Me.listboxName.Requery
 
 

You can also change column widths in this manner if required.

Mary

Hi Mary
The 3 items was an example, I have maximum of almost 15000 items??
I won't know how many will be there for each selection.

Obviously I won't be having list box as big as to 15000 items (need to scroll down the list), I want to be able to specify the maximum size and also if there is only 3 item then I want the list box to be as big as that 3 items.
Does this make sense??

Thanks

[MMcCarthy]

Hi Mary
The 3 items was an example, I have maximum of almost 15000 items??
I won't know how many will be there for each selection.

Obviously I won't be having list box as big as to 15000 items (need to scroll down the list), I want to be able to specify the maximum size and also if there is only 3 item then I want the list box to be as big as that 3 items.
Does this make sense??

Thanks

Tara

I need a lot more information. Where are these 15000 items being drawn from, table or query? Where are the sub items being drawn from? How are the distinctions made as to what belongs with an item?

Mary

[NeoPa]

Tara,

Just to be clear.
Do you mean that you have differing numbers of columns required for different records in your ComboBox?
Or maybe you select different sets of records within the overall dataset (Table or results from a query) depending on something else not yet stated?
You see, the answer depends on what you mean in the question. We can help more when we understand the question better. Sometimes it may seem we ask detailed questions just to be difficult ;), but really these 'details' can make it so much easier to answer a question properly.

[tara99]

Tara

I need a lot more information. Where are these 15000 items being drawn from, table or query? Where are the sub items being drawn from? How are the distinctions made as to what belongs with an item?

Mary

OK
I have a combo box with displays list of available NetworkID from a table called Security (This table has few field, I have just queried one field and that is NetworkID).

Then I have created a list box which shows the other field based on the NetworkID selection (I have used query to display the list box item).

In the security table I NetworkID that have access to one item, or 5 item, or 20 or even 15000.

Basically when user selects a NetworkID the list of item that this NetworkId have access t will be displayed.

So I thought it would be best to have a dynamic list box. So I can set the maximum number of item that it can display let say 15.
If the selected NetworkID have access to 2 item then the list box will be as small as 2 items, if the selected NetworkID have access to 30 item than it display all but the user will need to scroll down to see the rest.

Does this make sense??
Let me know if it is still not clear.

[MMcCarthy]

OK
I have a combo box with displays list of available NetworkID from a table called Security (This table has few field, I have just queried one field and that is NetworkID).

Then I have created a list box which shows the other field based on the NetworkID selection (I have used query to display the list box item).

In the security table I NetworkID that have access to one item, or 5 item, or 20 or even 15000.

Basically when user selects a NetworkID the list of item that this NetworkId have access t will be displayed.

So I thought it would be best to have a dynamic list box. So I can set the maximum number of item that it can display let say 15.
If the selected NetworkID have access to 2 item then the list box will be as small as 2 items, if the selected NetworkID have access to 30 item than it display all but the user will need to scroll down to see the rest.

Does this make sense??
Let me know if it is still not clear.

OK Tara, that makes a lot more sense.

You want the list box to display a series of records corresponding to the NetworkID.

You need to set your list box Row Source to a query using the combo box selection in the criteria. Something like the following:

SELECT * FROM TableName 
WHERE NetworkID=[Forms]![FormName]![ComboboxName]
 

Now in the after update event of the combo box you will need the following code.

Me.listboxName.Requery
 

Mary

[NeoPa]

Tara,

That was a lot more helpful. I'm sorry I didn't get to reply but I couldn't get into TheScripts at all yesterday. Let us know if Mary's answer leaves you with any questions still.

-Adrian.

[tara99]

OK Tara, that makes a lot more sense.

You want the list box to display a series of records corresponding to the NetworkID.

You need to set your list box Row Source to a query using the combo box selection in the criteria. Something like the following:

SELECT * FROM TableName 
WHERE NetworkID=[Forms]![FormName]![ComboboxName]
 

Now in the after update event of the combo box you will need the following code.

Me.listboxName.Requery
 

Mary

Thanks Mary and NeoPa for your inputs

I have done that part,
Sorry if I didn't ask my question properly.
I guess my question is about the listbox size, how can I make it dynamic, to change its size depending on the number of item it holds (small/ big).
Does this make sense??
Is it possible.
Thanks

[NeoPa]

The short answer to this is that you can but it's complicated.
The properties for .Height & .Width (you're interested in .Height I suspect.) are dynamically adjustable within your VBA code.
NB. The units to supply in the code are NOT the same as you use in design view necessarily. You will need to do some experimentation to determine what to ask it to do.
What you need to do is determine the number of items in the list first (.ListCount of your ListBox control) and, using that, determine the .Height value required.
Unfortunately, the size of the form itself, while it can be set in code, doesn't seem to change its visible size on screen ever :(.

[tara99]

The short answer to this is that you can but it's complicated.
The properties for .Height & .Width (you're interested in .Height I suspect.) are dynamically adjustable within your VBA code.
NB. The units to supply in the code are NOT the same as you use in design view necessarily. You will need to do some experimentation to determine what to ask it to do.
What you need to do is determine the number of items in the list first (.ListCount of your ListBox control) and, using that, determine the .Height value required.
Unfortunately, the size of the form itself, while it can be set in code, doesn't seem to change its visible size on screen ever :(.

Ok than, if that is the case I better saty with what I have now,
Thank you so much to both you Neopa and Mary for helping with my query.
Cheers

[MMcCarthy]

Ok than, if that is the case I better saty with what I have now,
Thank you so much to both you Neopa and Mary for helping with my query.
Cheers

You're welcome Tara

Mary