By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

How to Create Dynamic List Boxes.

100+
P: 106
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
Dec 12 '06 #1
Share this Question
Share on Google+
11 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT CASE comboboxName
  2.  
  3. Case "item1"
  4.    Me.listboxName.ColumnCount = 5
  5.  
  6. Case "item2"
  7.    Me.listboxName.ColumnCount = 10
  8.  
  9. Case "item3"
  10.    Me.listboxName.ColumnCount = 2
  11.  
  12. End SELECT
  13.  
  14. Me.listboxName.Requery
  15.  
  16.  
You can also change column widths in this manner if required.

Mary
Dec 12 '06 #2

100+
P: 106
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT CASE comboboxName
  2.  
  3. Case "item1"
  4.    Me.listboxName.ColumnCount = 5
  5.  
  6. Case "item2"
  7.    Me.listboxName.ColumnCount = 10
  8.  
  9. Case "item3"
  10.    Me.listboxName.ColumnCount = 2
  11.  
  12. End SELECT
  13.  
  14. Me.listboxName.Requery
  15.  
  16.  
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
Dec 12 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 12 '06 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #5

100+
P: 106
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.
Dec 13 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName 
  2. WHERE NetworkID=[Forms]![FormName]![ComboboxName]
  3.  
Now in the after update event of the combo box you will need the following code.

Expand|Select|Wrap|Line Numbers
  1. Me.listboxName.Requery
  2.  
Mary
Dec 13 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 14 '06 #8

100+
P: 106
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM TableName 
  2. WHERE NetworkID=[Forms]![FormName]![ComboboxName]
  3.  
Now in the after update event of the combo box you will need the following code.

Expand|Select|Wrap|Line Numbers
  1. Me.listboxName.Requery
  2.  
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
Dec 18 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
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 :(.
Dec 18 '06 #10

100+
P: 106
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
Dec 19 '06 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 20 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.