Cascading combo/list boxes

[MarkP]

Hello, I have a combo box that feeds a list box1 which in turn feed another list box2. List box2 values need to remain in list box2 when list box1 has been change by the combo. The content of list box2 will be stored in a table. I guess this is 2 part question: Are there any examples out there on list box2 used as a collection point, and how hard would it be to store the contents of list box2 in a table? Thank you for your help.

[RuralGuy]

It is pretty easy to have a ListBox display the contents of a table.

[MarkP]

Hello RuralGuy, What I'm trying to resolve is an Access form that the user would use to record events. This requires information to be collected and stored. On the form I have 1 combo box that feeds off of a table and that updates a list box(1). The information from this box(1) is selected and goes to list box(2). This in turn, gets stored in a separate table as a record for that event. My problem is that each item in the combo box that brings up a separate list in box(1), that I select from, needs to be collected in box(2). I don't want to loose the items previous selected from box(1), as I'm adding to this in box(2). How is this accomplish?

[RuralGuy]

I'm not real clear what you feel you need. How are you adding the items to Box(2)?

[MarkP]

Merry Xmas and a Happy New Year. Thanks for responding back, I have enclosed a copy of the procedure below for List Box(1). Note: the combo box to list box(1) works okay and I can do the same steps for list box(2), however, I would like to show all of the selections that the user pick from list box(1). Also the combo box has 15 items that the user could pick and list box(1) has anywhere from 5 to 15 items per combo box selection. (Ex. If I was to select the 1st combo box item list box(1) would show 7 items; the next item in the combo box if selected would show 12 items in list box(1) and so on... But I would like to keep my selection shown in list box(2) when selected from list box(1) and to save the whole event to a record in a table.

List box(1) procedure:
Private Sub ListBox1_DblCli ck(Cancel As Integer)
Dim strItems As String
Dim intItem As Integer
For intItem = 0 To ListBox1.ListCo unt - 1
If ListBox1.Select ed(intItem) Then
strItems = strItems & ListBox1.Column (0, intItem) & ";"

End If
Next intItem
ListBox1Selecte d.RowSource = ""
ListBox1Selecte d.RowSourceType = "Value List"
ListBox1Selecte d.RowSource = strItems
End Sub

Thanks for your help with this problem.

[RuralGuy]

Rather than create a ValueList for List2, why not just save the values in a table and let List2 display the table?

[MarkP]

Great idea. So, if I understand what you are saying is to have list1 populate a table on selection and have list2 display the table as it is being populate by list1. Sounds great, do you have the VBA code for this?

[RuralGuy]

It looks to me like List2 os populated with the selections in List1 when you double click on List1. Is that correct?

[MarkP]

Yes you are correct. I can also deselect from list2 by holding down the ctl key and double clicking on list1 item that's highlighted.

[MarkP]

The problem I was having was that I could select the items from list1 but when I changed the list of items in list1 by selecting a new item from the combo box my items selected in list2 would remain. List2 items would clear.

[MarkP]

Correction: items selected in list2 would erase.

[RuralGuy]

Well the add would be a simple APPEND query in your loop that builds the ListValue string and the delete is simple a DELETE query with a Where clause.

[RuralGuy]

So a selection from the ComboBox gives List1 a new RowSource and clears List2, correct?

[RuralGuy]

You are still going to have a problem when you make a change in the ComboBox. The table does not really help you remember the selections in List1 unless you make the records unique to your cbo selection and add time. What would you expect to happen if the user make the same cbo selection again? Would you expect your previous selections to appear in List2?

[MarkP]

Yes you are correct about the combo box. If the user make the same cbo selection again then I would need a msg to advise of duplicate entries.