Datatype Mismatch Error

[crmulle]

I have created a database that monitors the QA vendor risk process.

In the main form of the database the user performs a lookup for a specific
vendor and the information displayed for that vendor is static.
Additionally, there are approximately 10 tabs on the form. Each tab is a
risk requirement and each requirement has a sub form with questions specific
to that requirement. All objects on this form are unbound. The sub form
contains text boxes, combo boxes and several option group boxes.

When the user completes a risk requirement, the information on the sub form
is inserted into a table specific for that requirement (example:
tblInsertBcpData).
The tblInsertBcpData fields are set up as a Text (with Allow Zero Length =
Yes) or Yes/No (text box). The primary key in the table is the
VendorID.Value & ReviewDate.Value.

Issue: A user might not need to complete every field in the subform. When
a user tries to save partial information to tblInsertBcpData a datatype
mismatch error appears. I believe this has something to do with null values
but I am unable to find a solution. I have added my code below for reference.

Private Sub cmdsubmit_click()

'Inserts values entered into assessment table

Dim cncurrent As ADODB.Connection
Set cncurrent = CurrentProject.Connection

cncurrent.Execute "Insert Into tblInsertBcpData VALUES ('" & _
txtVendorID.Value & txtReviewDate.Value & "', '" & _
txtVendorID.Value & "', '" & _
txtReviewDate.Value & "', '" & _
opgBcpPeNeeded.Value & "', '" & _
opgBcpPeObtained.Value & "', '" & _
comBcpPeStatus.Value & "', '" & _
txtBcpPeComments.Value & "', '" & _
opgBcpAcceptanceRiskNeeded.Value & "', '" & _
opgBcpAcceptanceRiskObtained.Value & "', '" & _
comBcpAcceptanceRiskStatus.Value & "', '" & _
txtBcpAcceptanceRiskComments.Value & "', '" & _
opgBcpVarianceNeeded.Value & "', '" & _
opgBcpVarianceObtained.Value & "', '" & _
comBcpVarianceStatus.Value & "', '" & _
txtBcpVarianceComments.Value & "', '" & _
opgBcpApNeeded.Value & "', '" & _
opgBcpApInPlace.Value & "', '" & _
opgBcpApCurrent.Value & "', '" & _
opgBcpApComplete.Value & "', '" & _
opgBcpApComprehensive.Value & "', '" & _
txtBcpApComments.Value & "')"

MsgBox "Successful!"

cncurrent.Close
Set cncurrent = Nothing

End Sub

 

[Maurice]

Datatype mismatch doesn't have to be a required field. My guess would be that
in your statement you've placed one of the fields between double quotes which
makes it a string and should be something different.

In that case what I would do is break up the insert statement into shorter
pieces and add one field at a time. You will get the datatype error at a
certain moment. That should be the field which is causing the trouble...

what could help is to create a query in and copy the SQL statement from that
query. Replace the fieldnames with the formfieldnames...