Multiselect Listbox to Table 2

[DBLoser]

I put a multiselect listbox on a form. It works great, I can select members of the list. I'm trying to learn how to code a button to append the selected items to a table.

For example:

I have a table called People with fields ID, FirstName, and LastName. This is my multiselect listbox.

I have another table called SendTo. I want to append the People.ID to the SendTo table for each selected item in the list.

Thanks in advance!

 

[Remou]

You can use an append query:

strSQL="INSERT INTO SendTo (ID) Values " & Me.lstPeople.Columns(itm,0)

CurrentDB.Execute strSQL

 

[SoCalAccessPro]

And, you need to loop throgh the selected items:

For Each varItem In .ItemsSelected
  If Not IsNull(varItem) Then
     'do something here
  End If
Next

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

SoCalAccessPro

If the item is selected, it cannot be null. You appear to be missing the control name and with.

 

[SoCalAccessPro]

My bad. I had some residual stuff in my clipboard.

Dim varItem As Variant 
With Me.myList
        For Each varItem In .ItemsSelected
           'do soemthing
       Next
End With

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

From the original post, it seems likely that it is "'do something" that the OP is having problems with.

 

[DBLoser]

Wow, I can't keep up with the changes. Anyway, I started with the first suggestion...
Private Sub Command2_Click()

strSQL = "INSERT INTO SendTo (ID) Values " & Me.lstPeople.Column(itm, 0)

CurrentDb.Execute strSQL
End Sub

I see that the listbox is called lstPeople so I changed the name on that. When I click the button I get an error: Run-time error '3134': Syntax error in INSERT INTO statement. Debug lands on:

CurrentDb.Execute strSQL

If I can get at least the first selection to append to the table then I will work on the looping method.

 

[Remou]

The line I posted assumed that you would be using the selected collection, hence, itm, however for test purposes, you should be able to us a number, say:

strSQL = "INSERT INTO SendTo (ID) Values " & Me.lstPeople.Column(1, 0)

ID = name of the field in ...
SendTo = the table.

 

[SoCalAccessPro]

This works on a sample database with your naming conventions:

Private Sub cmdSend_Click()


Dim varItem As Variant
Dim strSQL As String

With Me.lstPeople
    For Each varItem In .ItemsSelected
        strSQL = "INSERT INTO SendTo (ID) VALUES ('" & .ItemData(varItem) & "')"
        DoCmd.RunSQL (strSQL)
        strSQL = ""
    Next varItem
End With


End Sub

Forgive my earlier posts. I normally post "psuedo code" so the OP can understand the logic.

You can probably cut and paste the above and it will work. If your ID field is in fact, a number, then remove the commas (') in the SQL statement that are next to the parentheses.

Also, this was tested on a 1 column multi select list box. You'll have to modify it if your list box has more than 1 column.




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[DBLoser]

Thanks Remou and SoCalAccessPro! This works great. Now I have a great starting point.

 

[DBLoser]

Updated code...

With some help from a friend, we've created a really nice working form. I'll post the code to hopefully help others working on similar issues.

There's a form with two multiselect listboxes. One is a list of documents and the other is a list of people. You can click on any number of documents, any number of people, and then click the button to update the SendTo table with each person, each document, and the current date. For example, if you select 2 documents and 3 people it will create 6 records. Here's the code to make it happen...


Private Sub Command2_Click()

Dim varDOC As Variant
Dim varPeople As Variant
Dim strSQL As String
Dim x As Integer

For Each varDOC In Me.Combo6.ItemsSelected
For Each varPeople In Me.lstPeople.ItemsSelected
strSQL = "INSERT INTO SendTo (ID, Doc_ID,[Date]) VALUES ('" & Me.lstPeople.ItemData(varPeople) & "', '" & Me.Combo6.ItemData(varDOC) & "', '" & Date & "')"
DoCmd.RunSQL (strSQL)
Next varPeople
Next varDOC

End Sub