INSERT into + null

[deejayquai]

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 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

 

[rainman89]

try NZ([Initials] in there. i think that forces a value even if null

 

[deejayquai]

try NZ([Initials] in there. i think that forces a value even if null

Hi Ray

Where would this go please? I'm not too hot on the syntax!

cheers

David

 

[rainman89]

DoCmd.RunSQL "INSERT into tblGroupMembers ([Student ID], [Group
Code ID], [Surname], [First Name], [Class Year],[B]NZ([Initials])[/B])" _
& "VALUES (""" & lstStudents.Column(0, lbl1ID) _
& """,""" & [Group Code ID] _
& """, """ & lstStudents.Column(1, lbl1ID) _
& """, """ & lstStudents.Column(2, lbl1ID) _
& """, """ & lstStudents.Column(6, lbl1ID) _
& """, """ & lstStudents.Column(3, lbl1ID) & """)"

It may not work. i have not tried it

 

[pbaldy]

Err, I think it would have to be in the VALUES clause.

 

[rainman89]

haha dont hold me to it! it was a guess, ive never actualy tried it

 

[deejayquai]

Cracked it!

Thanks for everyone's help.


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) _
& """, """ & Nz(lstStudents.Column(3, lbl1ID), "") & """)"

Next

DoCmd.SetWarnings True

[lstMembers].Requery

End Sub