Insert multiple records into single table with list box

  • Thread starter Thread starter Michael Walsh
  • Start date Start date
M

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.
 
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
 
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
Click to expand...
http://www.mvps.org/access/forms/frm0007.htm, and reading
 
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
 
Back
Top