Access VBA Converting An Array to Individual Records

Question

I'm working on a database with 2 main tables and a linked table which contains the foreign keys from the 2 tables. I created a form in Access and the intent of the form is to use a drop-down to select 1 option from one table and use a multi-select list box to allow users to select multiple options from the other table. The user would then click a button and my code (see below) should create a record in the linked table for each selected option in the list box. For example:

User creates a new record on the form, selects "Quality Assurance" from the drop-down list, and chooses 2 options from the list box. When the user clicks the button "cmdAddCompToCurr", 2 records should be created in the linked table.

When I test the button it's not quite right. The code doesn't convert the array created from the list box selections into individual ID numbers. No matter what items I choose from the listbox, I always get the same ID number in the linked table.

Code:

Private Sub Command0_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Variant

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

If List89.ItemsSelected.Count = 0 Then
    MsgBox "No components were selected"
    GoTo Endme

End If

If List89.ItemsSelected.Count >= 1 Then
    i = List89.ListCount
    fld_ComponentID = List89.Column(0, 0)
    fld_ComponentCode = List89.Column(1, 0)
    fld_ComponentTitle = List89.Column(2, 0)
    fld_CompRevision = List89.Column(3, 0)

End If

For Each i In List89.ItemsSelected
    rs.AddNew
    rs!fld_CurriculumID = fld_CurriculumID
    rs!fld_ComponentID = fld_ComponentID
    rs.Update

Next Endme: End Sub

Jon Jon · Accepted Answer · 2016-08-18T14:05:53

The fundamental issue is you have assigned your fld_ComponentID variable before (and outside of) your for loop. This means it gets set only once, before the loop, and every iteration of the loop will use that same value. Also because it is hard-coded to .Column(0, 0), it gets set to whatever is in the first row regardless of what the user actually selects.

Instead, move the fld_ComponentID definition to inside the for loop, and also have it dynamically reference .Column(0, i), i.e.:

    fld_ComponentID = List89.Column(0, i)
    rs!fld_ComponentID = fld_ComponentID

However you will notice the variable itself becomes redundant, so you can just:

    rs!fld_ComponentID = List89.Column(0, i)

And note the preceding IF...END IF block is actually redundant, so can be removed entirely.

You have not defined fld_CurriculumID anywhere. Be wary that without Option Explicit this will automatically generate a variable with default value, which will not throw an error and is seldom what anyone intends. For the full answer below I've assumed the relevant control is named txtCurriculumID.

After a few control flow changes as well, an alternative to your sub can be:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Variant

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

    If List89.ItemsSelected.Count = 0 Then
        MsgBox "No components were selected"
    Else
        For Each i In List89.ItemsSelected
            rs.AddNew
            rs!fld_CurriculumID = txtCurriculumID.Value
            rs!fld_ComponentID = List89.Column(0, i)
            rs.Update
        Next i
    End If

Access VBA Converting An Array to Individual Records

1 Answers