On Mar 26, 1:52 pm, "deejayquai" <deejayq...@gmail.comwrote:
Quote:
Hi
>
I'm attempting to insert multiple records from one table into another
based on rows selected in a multiple list box. This all seems to be
working OK until I get to a field where the value is null. What seems
to happen then is the entire record isn't inserted into the new table
ie it fails. The field called [Initials] is 95% of the time empty but
occassionally has been filled in, so I think I need something in the
code that covers both eventualities. Grateful for any help as I've
trawled through the group (and others) and can't quite seem to crack
it. Below is the offending code!
>
thanks
>
David
>
Private Sub cmdAddMems_Click()
>
DoCmd.SetWarnings False
>
Dim lbl1ID As Variant
>
For Each lbl1ID In lstStudents.ItemsSelected
>
DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], [Group
Code ID], [Surname], [First Name], [Class Year],[Initials])" _
& "VALUES (""" & lstStudents.Column(0, lbl1ID) _
& """,""" & [Group Code ID] _
& """, """ & lstStudents.Column(1, lbl1ID) _
& """, """ & lstStudents.Column(2, lbl1ID) _
& """, """ & lstStudents.Column(6, lbl1ID) _
& """, """ & lstStudents.Column(3, lbl1ID) & """)"
>
Next
>
DoCmd.SetWarnings True
>
[lstMembers].Requery
>
End Sub
DJ:
Take a look at the Nz function in Help. It converts Nulls to
alternate values for you. So, if you just want it to put an empty
string instead of a Null, you'd change the line that inserts into the
[Initials] field to this:
Nz(lstStudents.Column(3, lbl1ID),"")
HTH,
Jana
