List Box - Selection - Populates next List Box (1 Viewer)

[whhaatt]

Hi I am wanting to preempt data in list boxes

listbox1
Fruit
Vegetable

listbox2 (If Fruit Is Selected)
Apple
Banana
Orange

listbox2 (If Vegetable Is Selected)
Potato
Peas
Carrot

If Fruit is selected in Listbox1 - Then Listbox2 should have the options
Apple
Banana
Orange

If Vegetable is selected in Listbox1 - Then Listbox2 should have the options
Potato
Peas
Carrot

Please can anyone help :banghead:

 

[pr2-eugin]

Hello whhaatt, how is this information placed in the Listbox? If it gets it from a Table, this would be real simple.. So if you could let us know how the information gets there, we might be able to help you out..

 

[whhaatt]

Hi

I am exploring options at the moment

Using Multi List box or List box.

But the data at the moment is not in any table, so Listbox1 I am inputting the data manually in the list - But pointing the data to be saved in a field in a table.
Listbox2 will create a list dependent on what is selected in Listbox1, and again I will point the Selection that is made in Listbox2 to be saved to a Field in a table (via the properties box - for the list box)

Hope that helps

 

[pr2-eugin]

Hmmm, create a table that will have something like this..

tbl_FruitVeg
itemID - AutoNumber
itemName - Text
itemCategory - Text

Some sample data in the table like..

itemID    itemName    itemCategory
1        Apple         Fruit
2        Banana        Fruit
3        Orange        Fruit
4        Potato        Vegetable
5        Peas          Vegetable
6        Carrot        Vegetable

Then for the First listbox, you can have the RowSourceType as Table/Query and the Actual RowSource as

SELECT itemCategory 
FROM tbl_FruitVeg
GROUP BY itemCategory;

This will give you the list box value as Fruit, Vegetable.. Then in the after Update event or the On Click event of the list box you can set the second listbox RowSource, as..

Private Sub listBoxName_Click()
    Me.listBoxName.RowSourceType = "Table/Query"
    Me.listBoxName.RowSourceType = "SELECT itemName FROM tbl_FruitVeg WHERE itemCategory = '" & Me.yourFirstListBox & "'"
End Sub

The main reason you use tables is because it makes the job so much easier, if in case you add more category.. Meat, Poultry, Sea food etc.. you do not have to keep changing your Form design, just add them to the table.. You can then do what you wish to.. Hope this helps..

 

[whhaatt]

Ok I am trying you method, but i am confused

the table contains the following

Table Name Selections
Selection1 | Selection2 | Selection3 |Selection4
I.T Related | Software | Installation | Other
I.T Related | Software | Removal MS | Office 2007
Change Reuqest | Phone | Hunt Group | Add Ext
Change Reuqest | Phone | Hunt Group | Remove Ext
Change Reuqest | Phone | Hunt Group | Edit Extension

I am wanting the form to have 4 list boxes (as per table fields)
Selection1
Selection2
Selection3
Selection4

1) However when I click on List box selection 1 (I only want it to show Unique Values eg: - 1x I.T Related) and not 2 lots of I.T Related)

2) When I make a Selection in Selection list box 1, the Selction list box 2 should only show me unique values from field selection2 from the table eg: - 1x Phone and 1x Software)

3) and this should be the case for Selection box2 and 3 as in only show values dependent on previous list box selection

How can I do this, firstly I keep ketting multiple instances if records in the list Box, and second I cannot get the next list box to filter out the results based on the 1st selection

Please can anyony help!!!!!!!!:banghead:

 

[pr2-eugin]

Nope, that is not what I have proposed in Post#4.. Please read it again..

Group By eliminates the duplicate..

 

[whhaatt]

Ok - managed to get the 4 list boxes working.

Because the List data is in a separate table, the form will not allow me to update anymore.

Please could you let me know how I can add records now (I presume that the GROUP BY has something to do with this)

Private Sub Select1_AfterUpdate()
Me.Select2.RowSource = "Table/Query"
Me.Select2.RowSource = "SELECT Selection2 FROM Selections WHERE Selection1 = '" & Me.Select1 & "' GROUP BY Selection2;"
End Sub

Private Sub Select2_AfterUpdate()
Me.Select3.RowSource = "Table/Query"
Me.Select3.RowSource = "SELECT Selection3 FROM Selections WHERE Selection2 = '" & Me.Select2 & "' GROUP BY Selection3;"
End Sub

Private Sub Select3_AfterUpdate()
Me.Select4.RowSource = "Table/Query"
Me.Select4.RowSource = "SELECT Selection4 FROM Selections WHERE Selection3 = '" & Me.Select3 & "' GROUP BY Selection4;"
End Sub

 

[pr2-eugin]

I presume that the GROUP BY has something to do with this

Your assumption is absolutely right.. Totals query are always read only.. If you want to add, then create a Form to add the information to the ListData table.. Then have a button, which will open that form to add data to the table.. Then Requery the Listbox..

 

[whhaatt]

Your assumption is absolutely right.. Totals query are always read only.. If you want to add, then create a Form to add the information to the ListData table.. Then have a button, which will open that form to add data to the table.. Then Requery the Listbox..

Any ideas or suggestions on how, eg ; as per code

The list data is stored in A table called Selections
The information needs to be added to the Table Call Seection (Via a form)

Due to the GROUP BY options in the code for the list boxes (calling the selections table) the form is Read only in a sense as soon as you open it.

So could you point me in the direction - Create a button ??? or can coding be put in eg: on open form or on button click (save) etc

I am some what a Novice at this, as I have come back to VBA after a very long time, Any help would be appreciated.

Thank you in Advance

 

[pr2-eugin]

Okay here is a very basic quick example..

 

[gbil]

hello.

i also have two list boxes in my form and i follow the recommendation here. here is the final code:

Private Sub lstOrg_Click()
Me.lstMembers.RowSourceType = "Table/Query"
Me.lstMembers.RowSourceType = "SELECT tblMembers.tOrgNick, tblMembers.tFName, tblMembers.tMName, tblMembers.tLName FROM tblMembers Where tOrgNick = '" & Me.lstOrg & "'"
End Sub

i also put the same codes in (lstOrg.) afterupdate of the first list box.

the code did not show records in the second list box (lstMembers). it only shows 3 column dividers with no data. i have 10 records in the table to test things out.

how can i make the first list (name of organizations) show the members in the second list box?

 

[pr2-eugin]

Not entirely sure why you have the same code in two events, even then I don't think ti should cause trouble.. Can you post your DB here? Or maybe start a New thread?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[gbil]

thank you.

i follow your advice to post another thread with stripped db attached.