469,288 Members | 2,353 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,288 developers. It's quick & easy.

How to Create Dynamic List Boxes.

106 100+
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
11 10948
MMcCarthy
14,534 Expert Mod 8TB
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
tara99
106 100+
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
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
tara99
106 100+
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
14,534 Expert Mod 8TB
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
32,173 Expert Mod 16PB
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
tara99
106 100+
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
32,173 Expert Mod 16PB
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
tara99
106 100+
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
14,534 Expert Mod 8TB
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.

Similar topics

13 posts views Thread by mr_burns | last post: by
3 posts views Thread by Don Lee | last post: by
4 posts views Thread by Brian Shannon | last post: by
reply views Thread by mtsylvester | last post: by
15 posts views Thread by lxyone | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.