Append to Table Multiple Selection

[burrina]

I have a combo box and a listbox on my form. Combo is for userID and listbox is for from names.
I want to select a user then select as many form names as I want and then append them to my table.
Where am I going wrong here?

Code: Select all

Dim strSQL As String
                                                  
      strSQL = "INSERT INTO tblformAccess (frmName,userID ) "
      strSQL = strSQL & "VALUES (#" & Me.frmName & "#,'" & Me.userID & "','""')"
           
                CurrentDb.Execute strSQL, dbFailOnError 

[HansV]

If you have set the Multi Select property of the frmName list box to Simple or Extended, so that the user can select multiple items, the value of the list box will always be Null. You have to loop through the selected items.

Also # is for dates, and you are specifying 2 field names but it looks like you are trying to specify 3 values.

Assuming that frmName is a text field and UserID a number field:

Code: Select all

    Dim strSQL As String
    Dim itm As Variant

    For Each itm In Me.frmName.ItemsSelected
        strSQL = "INSERT INTO tblformAccess (frmName,userID) " & _
            "VALUES ('" & Me.frmName.ItemData(itm) & "'," & Me.UserID & ")"
        CurrentDb.Execute strSQL, dbFailOnError
    Next itm

If UserID is a text field like frmName:

Code: Select all

    Dim strSQL As String
    Dim itm As Variant

    For Each itm In Me.frmName.ItemsSelected
        strSQL = "INSERT INTO tblformAccess (frmName,userID) " & _
            "VALUES ('" & Me.frmName.ItemData(itm) & "','" & Me.UserID & "')"
        CurrentDb.Execute strSQL, dbFailOnError
    Next itm

[burrina]

This almost works! It adds an extra record with just the userID and no form name ?
It does however add the correct number of forms with the matching userID
Yes, userID is text

Dim strSQL As String
Dim itm As Variant

For Each itm In Me.frmName.ItemsSelected
strSQL = "INSERT INTO tblformAccess (frmName,userID) " & _
"VALUES ('" & Me.frmName.ItemData(itm) & "','" & Me.userID & "')"
CurrentDb.Execute strSQL, dbFailOnError
Next itm

[HansV]

Did you select an item without a name (i.e. an empty name) in the list box?

[burrina]

No, I selected 1 userID and then 2 form names
Hmm, just checked again, seems to be working. Must now check for duplicates

Okay, one more adjustment Please.
I also need to set this value.
fAccess which Data Type is a number and format is integer and needs to be set to 1


My Oops was, I had a default value set in fAccess in the table.

[HansV]

That would be

Code: Select all

        strSQL = "INSERT INTO tblformAccess (frmName,userID,fAccess) " & _
            "VALUES ('" & Me.frmName.ItemData(itm) & "','" & Me.UserID & "',1)"

[HansV]

By the way, it's better to post an additional request in a new reply instead of editing an existing reply. I read all new replies but there is no guarantee that I will notice that you edited a reply.

[burrina]

True, my bad. Thanks so much for the help.

[burrina]

Kinda Strange? Just noticed. If I use the code to append and look at the table while the form is open it shows the correct number of records
If however I close the form and then open the table it again shows a userID with no frmName ? But still with other records that were supposed to be added
The tblformAccess does NOT have any default values set.

[burrina]

Oops again. I am testing on a mini version of db. I hard coded the userID so it shows up as a record in the table and it is set in the OnCurrentEvent
Time to modify that code and test in the real db.