Multiselect List Box - Need Help

[cstanley]

Hello all,

I have a multiselect list box where I want to select multiple records, select a single value from a combo box in another place on the form, and then create multiple new records in a join table, like so:

Table 1 Table 2
(source for List Box) (source for combo box)

A 1
B 2
C
D

So therefore, if I selected A,B, and C from the list box, and then 1 from the combo box, my join table would look like:

Table 3 (Join Table)
Field 1 Field 2
A 1
B 1
C 1

How can I code for this? I found some code in a book that I think is applicable, but I'm fairly new at VBA so I can't be sure. It's currently stopping on the rst.Open line, saying that it can't find the field that I want it to. Can anyone help me? Thanks!!!!!

Private Sub Specification_Req_Click()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim msg As String, varNumber As Variant
Set cnn = CurrentProject.Connection
rst.Index = "Primary Key"

rst.Open [Specification Source], adOpenStatic, adLockOptimistic, adCmdTableDirect

For Each varNumber In [Specification Source].ItemsSelected
rst.Seek [Specification Source].ItemData(varNumber), adSeekFirstEQ
rst!Specification_Req = cboNew
rst.Update
Next
cboCurrent = cboNew
cboNew = Null
[SpecificationSource].Requery
End Sub

 

[scottfarcus]

Dim varItem as Variant

For Each varitem In lstList.ItemsSelected 'Loop through all selected items in the list box
strSQL = "INSERT INTO tblAlphaNumber (AlphabetID, NumberID) VALUES (" & lstList.ItemData(varitem) & ", " & cboNumberID & ")"
DoCmd.RunSQL (strSQL) 'Append a record
Next 'Move to the next item selected in the list box

-----------------------------------------------------------------------------------
If I had:

a multiselect listbox named lstList (holding alphabet characters),

a combobox named cboNumberID (holding a single number)

and a table called tblAlphaNumber (with fields named AlphabetID and NumberID) where these to entities would be joined (one-to-many - ONE NumberID can have MANY Alphabet characters)

this code would do exactly what you want to do.

Change the field names, table name, and control names to reflect you application.

Good luck!

 

[cstanley]

OK - this looks good and I sort of understand what is going on...

Where would I put this snippet of code though? In the list box? In a button that advances to a new record?

Thanks!

Chris

 

[cstanley]

It's also giving me a syntax error in the INSERT INTO statement

 

[scottfarcus]

If the bound column of either the list box or the combobox is not of type number, you will need to put single qoutes around the values like this...

For Each varitem In lstList.ItemsSelected 'Loop through all selected items in the list box
strSQL = "INSERT INTO tblAlphaNumber (AlphabetID, NumberID) VALUES ('" & lstList.ItemData(varitem) & "', '" & cboNumberID & "')"
DoCmd.RunSQL (strSQL) 'Append a record
Next 'Move to the next item selected in the list box

Hope this helps!

Good luck!