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_DblClick(Cancel As Integer)
Dim strItems As String
Dim intItem As Integer
For intItem = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(intItem) Then
strItems = strItems & ListBox1.Column(0, intItem) & ";"

End If
Next intItem
ListBox1Selected.RowSource = ""
ListBox1Selected.RowSourceType = "Value List"
ListBox1Selected.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.

[RuralGuy]

Do you plan to just keep adding on to one table for all of the different selections?

[MarkP]

I was hoping to add the contents of the form to a table as a record for each event (daily there could be 10 to 20 records) entered and to use the selections in listbox2 as a string in a column in the table that would be used for reports.

[RuralGuy]

Would this table just keep getting bigger every day that goes by?

[MarkP]

Yes the table would increase daily and help in trending reports.

[RuralGuy]

Almost there. We need to know what the fields of this table will be and where the values will come from and probably a value from the ComboBox so we can set that value for the filter criteria on the List2 RowSource.

[MarkP]

Thank you for your time. Let me briefly explain what I'm doing. I need to get an assessment of daily events that are analyzed by inspectors. These events would assist the inspectors in trending and focus their attention on problemt areas. I'm trying to capture their assessment in a database that would allow them to run reports. That said, presently, in the database I have two tables and one form. The form name is EventForm. The table that the combo/list boxes feed off of is tbl_CausalFactors. The table that the events are collected is tbl_Events. The causal factor table has 3 columns: CausalID (autonumber), Causals (text), and Factors (text). The event table has several columns but the main two are Causals and Factors and they are text fields. The combo box values would be things like: Alerts, Communications, Corrdination, and Automation for examples. I hope this helps.