Creating records covering all options selected in several list boxes

[EonsTimE]

I, I'm learning how to add records to a table from different types of controls.



I'm having the following issue with ListBox. For one list box, let's say it contains 5 choices. then there are two text boxes on the same form.

The following code adds table entries for every selection made in the ListBox1 (Multi Select: Simple) and attaches entries from other cotrols for each ListBox1 selection.

Set rs = db.OpenRecordset("tblOut", dbOpenDynaset, dbAppendOnly)
For Each varItem1 In Me.Frm1LstBox1.ItemsSelected
rs.AddNew
rs!something1 = Me.Frm1TxtBox1
rs!something2 = Me.Frm1TxtBox2
rs!country = Me.Frm1LstBox1.Column(0, varItem1)
rs!color = Me.Frm1LstBox2.Column(0, varItem2) 'This code will add only first selection from list to the table
rs!plant = Me.Frm1LstBox3.Column(0, varItem3) 'This code will add only first selection from list to the table
rs.Update
Next varItem1

How do I efficiently nest let's say 3 ListBoxes so that entries in the table are created for every possible combination of choices from each ListBox?

Set rs = db.OpenRecordset("tblOut", dbOpenDynaset, dbAppendOnly)
For Each varItem1 In Me.Frm1LstBox1.ItemsSelected
For Each varItem2 In Me.Frm1LstBox1.ItemsSelected 'I know this won't work, it's just for ilustration
For Each varItem3 In Me.Frm1LstBox1.ItemsSelected 'I know this won't work, it's just for ilustration
rs.AddNew
rs!something1 = Me.Frm1TxtBox1
rs!something2 = Me.Frm1TxtBox2
rs!country = Me.Frm1LstBox1.Column(0, varItem1)
rs!color = Me.Frm1LstBox2.Column(0, varItem2)
rs!plant = Me.Frm1LstBox3.Column(0, varItem3)

rs.Update
Next varItem1
Next varItem2 'I know this won't work, it's just for ilustration
Next varItem3 'I know this won't work, it's just for ilustration

Thank you.

[ranman256]

you don't need all this code to update records, you use queries.
to cascade lists, each list box AFTERUPDATE event will filter the next list box...

sub list1_afterupdate()
   list2.requery
end sub

[ssanfu]

How do I efficiently nest let's say 3 ListBoxes so that entries in the table are created for every possible combination of choices from each ListBox?

Maybe this???

Option Compare Database
Option Explicit


Private Sub btnAddAll_Click()  ' my test button name
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim varItem1, varItem2, varItem3
    Dim LBC1 As Integer  'list box 1 count
    Dim LBC2 As Integer  'list box 2 count
    Dim LBC3 As Integer  'list box 3 count
    Dim x As Integer  'count of records added

    'list box 1 selected count
    LBC1 = 0
    For Each varItem1 In Me.Frm1LstBox1.ItemsSelected
        LBC1 = LBC1 + 1
    Next varItem1

    'list box 2 selected count
    LBC2 = 0
    For Each varItem1 In Me.Frm1LstBox2.ItemsSelected
        LBC2 = LBC2 + 1
    Next varItem1

    'list box 3 selected count
    LBC3 = 0
    For Each varItem1 In Me.Frm1LstBox3.ItemsSelected
        LBC3 = LBC3 + 1
    Next varItem1
    '    MsgBox LBC1 & ", " & LBC2 & ", " & LBC3
    '
    'ensure that there is at least 1 selection in each list box
    If LBC1 = 0 Or LBC2 = 0 Or LBC3 = 0 Then
        MsgBox "1 or more list boxes have no selections"
        Exit Sub
    End If

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblOut", dbOpenDynaset, dbAppendOnly)

    x = 0
    For Each varItem1 In Me.Frm1LstBox1.ItemsSelected
        For Each varItem2 In Me.Frm1LstBox2.ItemsSelected
            For Each varItem3 In Me.Frm1LstBox3.ItemsSelected
                x = x + 1
                'Debug.Print Me.Frm1LstBox1.Column(0, varItem1) & ", " & Me.Frm1LstBox2.Column(0, varItem2) & ", " & Me.Frm1LstBox3.Column(0, varItem3)
                rs.AddNew
                rs!something1 = Me.Frm1TxtBox1
                rs!something2 = Me.Frm1TxtBox2
                rs!country = Me.Frm1LstBox1.Column(0, varItem1)
                rs!color = Me.Frm1LstBox2.Column(0, varItem2)
                rs!plant = Me.Frm1LstBox3.Column(0, varItem3)

                rs.Update
            Next varItem3
        Next varItem2
    Next varItem1

    rs.Close
    Set rs = Nothing
    Set db = Nothing

    MsgBox x & " " & "Records added"

End Sub

================================================== ===========
An alternative to using the rs.AddNew...rs.Update syntax, is to use an append query:
sSQL = "INSERT INTO tblOut (something1, something2, country, color, plant ) VALUES (" & Me.Frm1TxtBox1 & ", " & Me.Frm1TxtBox2 & ", " & Me.Frm1LstBox1.Column(0, varItem1) & ", " & Me.Frm1LstBox2.Column(0, varItem2) & ", " & Me.Frm1LstBox3.Column(0, varItem3))
CurrentdB.Execute sSQL, dbfailonerror

The above probably won't work because I don't know the field types to add the delimiters.
================================================== ===========