append query with listbox criteria

[miss031]

I am not sure if this has been covered here exactly.

I need to write be able to insert a record into my table for each item
selected in a multi-select listbox. I have read about how to use a
multi-select to concatenate criteria, but I don't think that's what I want to
do.

For each item selected in the "licence_type" listbox, I would like to insert
the value (1,2,3, etc.) into the "tbl_cont_licence" along with the
"contact_ID" (also numeric) which is on the form "frm_cont_licence". So i
believe that each item selected in the list box would be inserted into the
table as a separate record with the given contact ID.

 

[Noëlla Gabriël]

Hi,

in this case you can open a recordset and insert a record for each item
selected, something in the style of:

dim rst as new ADODB.recordset
dim varItem as variant

rst.open "tbl_cont_license", currentproject.connection, adOpenKeyset,
adLockPessimistic
with rst
for each varItem in me.cboMyList.ItemsSelected
.addnew
!field1 = val1
'fill in here all the necessary field values
.update
next varItem
.close
end with

 

[miss031]

okay, thank you very much. I don't know ADO, so I switched to DAO. My code is
below . It inserts records into the table and records the contract_ID to each
of them, but the licence_type field is left blank in each record. Do I need
to specify the column of the listbox, and if so, how?


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant

Set db = CurrentDb

Set rst = db.OpenRecordset("tbl_firearms_licence")

With rst
For Each varItem In Me.txt_gun_lic_no.ItemsSelected
.AddNew
rst![contact_ID] = [Forms]![add_new_all]![contact_ID]
rst![licence_type] =
[Forms]![add_new_all]![subf_main_blank].[Form]![tbl_firearms_cont_licence
subform].[Form]![txt_gun_lic_no]
.Update
Next varItem
.Close
End With