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