Insert multiple records into single table with list box

[Michael Walsh]

I have a child table that contains a link to the parent, a
couple of number fields, a drop down and a listbox.

From the corresponsing form, I would like to get 1 record
for each item selected. So, if I enter 20, 30, 2-hour,
and then select Smith and Jones and White on the listbox,
I would like to get 3 records in my table like this

Record 1 = 20 - 30 - 2-hour - Smith
Record 2 = 20 - 30 - 2-hour - Jones
Record 3 = 20 - 30 - 2-hour - White

What is the best way to go about this?

I'm fairly new to programming access but I was thinking
that perhaps it would be possible to do an insert into
loop of some sort using itemselected if there's not an
easier way.

 

[Michel Walsh]

Hi,


That is exactly that, you have to loop through (all) the ItemsSelected
collection of the list box controls ( with multiple selection allowed), a
little bit like at http://www.mvps.org/access/forms/frm0007.htm, and reading
the data from ItemData, you append the record with something like:

strSQL= "INSERT INTO tableName( ListOfFields) VALUES( " & value1 & ",
" ... & ") ; "
Debug.Print strSQL
'CurrentDb.Execute strSQL, dbFailOnError
DoCmd.RunSQL strSQL

inside your loop.

Note that DoCmd.RunSQL resolves the references like
FORMS!FormName!ControlName, but not CurrentDb. In other words,

strSQL = ".... VALUES( ..., FORMS!MyForm!MyControl, .... "
is acceptable, only if you use DoCmd. If you use CurrentDb, you have to use
something like:
strSQL = ".... VALUES( ..., " & FORMS!MyForm!MyControl & ", .... "

and, then, also check for:
- dot, not coma, used as decimal separator with floating point numbers,
- delimit the constant strings with " or with '
- delimit the constant dates with #, and use the US format for date



Hoping it may help,
Vanderghast, Access MVP

 

[Michael Walsh]

I've gotten it to work, but there's one small problem. If
I select 2 items it submits 3 with one being a null item.
The EventProcedure is linked to a submit button on the
form that submits the dat and then closes the form. I'd
rather not have all the nulls in the table.

Here's the code...
Private Sub TrainingEventSubmit_Click()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me.Form
Set ctl = frm!TrainerKey
DoCmd.SetWarnings False
For Each varItem In ctl.ItemsSelected
strSQL = "INSERT INTO tblTrainingEvents
(TrainingRecordID, TrainerKey, TypeKey, PrepCount,
PartCount) VALUES(TrainingRecordID ," & ctl.ItemData
(varItem) & ", TypeKey, PrepCount, PartCount) ; "
Debug.Print strSQL
DoCmd.RunSQL strSQL
Next varItem

DoCmd.SetWarnings True
DoCmd.Close
End Sub

-----Original Message-----
Hi,


That is exactly that, you have to loop through (all) the ItemsSelected
collection of the list box controls ( with multiple selection allowed), a
little bit like at

http://www.mvps.org/access/forms/frm0007.htm, and reading

 

[Michel Walsh]

Hi,

You can check with IsNull( valueToTest ) before insertion...


For Each varItem In ctl.ItemsSelected
If Not IsNull( ctl.ItemData(varItem) Then

strSQL = "INSERT INTO tblTrainingEvents
(TrainingRecordID, TrainerKey, TypeKey, PrepCount,
PartCount) VALUES(TrainingRecordID ," & ctl.ItemData
(varItem) & ", TypeKey, PrepCount, PartCount) ; "

' Debug.Print strSQL

DoCmd.RunSQL strSQL
End if
Next varItem



You can also comment the Debug.Print, since the SQL statement turns out to
be OK.




Vanderghast, Access MVP