Insert Command rejects null values 1

[Weebairnes]

I'd like to use an insert command to insert whatever data that is on the unbound form. The first three fields are required and I check those values prior to executing the code below. But some fields may be null.

Dim strSQL as string
.
.
code
.
.
strSQL = "INSERT INTO tblCase " & _
"(Case_SiteName, " & _
"Case_PatientSequenceNumber, " & _
"Case_PatientInitials, " & _
"Case_DOB, " & _
"Case_ProcedureDate, " & _
"Case_Investigator, " & _
"Case_Institution, " & _
"Case_Value) " & _
"VALUES ('" & Me.cboSiteName & "', " & _
Me.txtPatientNumber & ", '" & _
Me.tbxPatientInitials & "', '" & _
Me.tbxPatientDOB & "', '" & _
Me.tbxProcDate & "', '" & _
Me.tbxInvestigator & "', '" & _
Me.tbxInstitution & "', " & _
Me.grpValue & ""

CurrentProject.Connection.Execute strSQL

___________________________
Works fine when user enters all data. However, when any of the last four controls are unanswered, I get an error. Yet, any or all of the last four can be blank. Any suggestions?

 

[zemp]

What is the error?

What I like to do is to add the following line,

Debug.Print strSQL

after the SQL is built. Then copy and run the SQL directly on the database to see if a more detailed error is produced. It also lets you see exactly what you are trying to insert.

Take Care,

zemp

 

[vb5prgrmr]

Weebairnes, have you read FAQ222-2244 yet?

[tt]
Dim strSQL As String, strSQLValues As String
strSQL = "INSERT INTO tblCase " & _
"(Case_SiteName, " & _
"Case_PatientSequenceNumber, " & _
"Case_PatientInitials "

strSQLValues = "VALUES ('" & Me.cboSiteName & "', " & _
Me.txtPatientNumber & ", '" & _
Me.tbxPatientInitials & "'"

If Trim(Me.tbxPatientDOB) <> vbNullString Then
strSQL = strSQL & &quot;,Case_DOB &quot;
strSQLValues = strSQLValues & &quot;,'&quot; & Me.tbxPatientDOB & &quot;'&quot;
End If
'...
strSQL = strSQL & &quot&quot; & strSQLValues & &quot&quot;
'...
[/tt]

Or something like that could be one way to check for the existance of data and the building of your insert string.

Good Luck

 

[Weebairnes]

Thanks vb5prgrmr,

I was hoping for some elegant little syntactical shortcut.

Thanks for your suggestion.

PS Did you write &quot;Weebairnes, have you read FAQ222-2244 yet?
&quot;? If so, did I break protocol somehow? Please inform.

Thanks

 

[vb5prgrmr]

Oh, no, I just noticed that you are a recent member and many people have found that particular FAQ222-2244 helpful in forming their questions and replies.

Also I forgot to mention ... look close at where I included the commas.

Good Luck