Append new records using SQL code

[Guest]

I'm trying to adde new records with SQL , but it will only work if none of
the values to add is not null or empty. Because the values are saved from
the form's entry, how can I correct it so that it saves all the values? It
doesn't give me an error message, - the record just will not save. Please
help! The following is the code:

Dim db As Database, rsLog As Recordset, strSQL As String
Set db = CurrentDb

strSQL = "SELECT * from NCLog Where"
strSQL = strSQL & "[CARNum] = '" & Me!txtCAR & "'"
Set rsLog = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)
If rsLog.EOF Then
strSQL = "INSERT INTO NCLog"
strSQL = strSQL & "(PONum, POLine, CARNum, DateSaved,"
strSQL = strSQL & "ChassisSN, SupplierID)"
strSQL = strSQL & "VALUES('"
strSQL = strSQL & Me!cboPOKey.Column(1) & "','"
strSQL = strSQL & Me!cboPOKey.Column(2) & "','"
strSQL = strSQL & Me!txtCAR & "','"
strSQL = strSQL & Me!txtDate & "','"
strSQL = strSQL & Me!txtChassisSN & "','"
strSQL = strSQL & Me!txtSupplierID & "');"

MsgBox strSQL
db.Execute strSQL
MsgBox Me!txtCAR & "has been added to the table."

Else
MsgBox "CAR number: " & Me!txtCAR & "already exists.", 48,
"ERROR!"
Me!cboPOKey.SetFocus
End If

rsLog.Close
db.Close

Any help is very much appreciated. Thank you.
Samantha

 

[Graham R Seach]

Samantha,

The first thing I noticed is that your SQL string is one continuous string,
without spaces between statements. That's probably why it failed. You need
to add a space at the end of every line. When creating such strings, it's a
good idea to copy the whole strSQL assignment to the Immediate window,
precede it with a questionmark (?), hit <Enter> and see what comes out of
it.

I'd suggest appending dbFailOnError to the db.Execute method, like so:
db.Execute strSQL, dbFailOnError

....you can trap any errors this way.

If you want to ensure than no Nulls go into certain table fields, you need
to use the Nz() function. Nz() replaces Null values with a value you supply.
For example, Nz(Null, "") replaces Null with an empty string (""); Nz(Null,
123) replaces Null with 123.

You need to determine which tables fields can tolerate Nulls, and which
can't. The ones that can't are the ones you use Nz() on.

Example:
strSQL = strSQL & Nz(Me!cboPOKey.Column(1), "") & "','"

....replaces a Null value found in cboPOKey.Column(1) with an empty string
("").

From the looks of your code, however, all your fields are strings. I'm not
convinced they are. For example, I would think that PONum is numeric,
therefore cboPOKey.Column(1) would need to be stored as a number. Certainly
DateSaved is a date, not a string, so txtDate must also be saved as a
DateTime datatype. I don't know about the other fields, but the following is
my suggestion:

strSQL = "INSERT INTO NCLog "
strSQL = strSQL & "(PONum, POLine, CARNum, DateSaved, "
strSQL = strSQL & "ChassisSN, SupplierID) "
strSQL = strSQL & "VALUES( "
strSQL = strSQL & CLng(Me!cboPOKey.Column(1)) & ",' "
strSQL = strSQL & Me!cboPOKey.Column(2) & "',' "
strSQL = strSQL & Me!txtCAR & "', "
strSQL = strSQL & Me!txtDate & ",' "
strSQL = strSQL & Me!txtChassisSN & "', "
strSQL = strSQL & CLng(Me!txtSupplierID) & ");"

Also, you may not have known about the line continuation character (the
underscore). It must be preceded by a space then an ampersand. Here is how
you could change the way you write such strings:
strSQL = "INSERT INTO NCLog " & _
"(PONum, POLine, CARNum, DateSaved, " & _
"ChassisSN, SupplierID) " & _
"VALUES( " & _
CLng(Me!cboPOKey.Column(1)) & ",' " & _
Me!cboPOKey.Column(2) & "',' " & _
Me!txtCAR & "', " & _
Me!txtDate & ",' " & _
Me!txtChassisSN & "', " & _
CLng(Me!txtSupplierID) & ");"

You might not agree, but I think it's a lot easier to read that way.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I'm trying to adde new records with SQL , but it will only work if none
of
the values to add is not null or empty. Because the values are saved
from
the form's entry, how can I correct it so that it saves all the values?
It
doesn't give me an error message, - the record just will not save. Please
help! The following is the code:

Dim db As Database, rsLog As Recordset, strSQL As String
Set db = CurrentDb

strSQL = "SELECT * from NCLog Where"
strSQL = strSQL & "[CARNum] = '" & Me!txtCAR & "'"
Set rsLog = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)
If rsLog.EOF Then
strSQL = "INSERT INTO NCLog"
strSQL = strSQL & "(PONum, POLine, CARNum, DateSaved,"
strSQL = strSQL & "ChassisSN, SupplierID)"
strSQL = strSQL & "VALUES('"
strSQL = strSQL & Me!cboPOKey.Column(1) & "','"
strSQL = strSQL & Me!cboPOKey.Column(2) & "','"
strSQL = strSQL & Me!txtCAR & "','"
strSQL = strSQL & Me!txtDate & "','"
strSQL = strSQL & Me!txtChassisSN & "','"
strSQL = strSQL & Me!txtSupplierID & "');"

MsgBox strSQL
db.Execute strSQL
MsgBox Me!txtCAR & "has been added to the table."

Else
MsgBox "CAR number: " & Me!txtCAR & "already exists.", 48,
"ERROR!"
Me!cboPOKey.SetFocus
End If

rsLog.Close
db.Close

Any help is very much appreciated. Thank you.
Samantha

 

[Guest]

Hi Samantha,

You could use the Nz() function around each control reference to specify a
substitute value for fields that were left blank. But, if there are some
fields that are required and you don't want to just substitute 0's or empty
strings, you may want to first use If statements to check for nulls in those
fields and message the user to fill in the field and then exit the sub.

HTH, Ted Allen