smudger70uk
New member
- Local time
- Today, 03:36
- Joined
- May 5, 2011
- Messages
- 7
Hi, I'm trying to run multiple sql statements behind a form button to update a number of fields at once as I didn't want to have to create individual queries and stick them in a macro. The code runs fine as long as the fileds currently contain a null value but the sequence stops as soon as it hits a field that in not null. I'm pretty new to VBA and cannot find a statement that allows me to say something along the lines of "If it's null then change it but if isn't null move to the next line" - does such a command exist?
The code I've written is below (once I get it to work I have numerous other strings and sql statements to add):
Private Sub btnNullToNo_Click()
Dim SQL_Per_Birthday As String
Dim SQL_Per_Birthplace As String
Dim SQL_Per_Card As String
Dim SQL_Per_Crim_Rec As String
SQL_Per_Birthday = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Birthday] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Birthday]) Is Null) " & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Birthplace = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Birthplace] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Birthplace]) Is Null)" & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Card = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Card Detail] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Card Detail]) Is Null)" & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Crim_Rec = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Criminal Record] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Criminal Record]) Is Null) " & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
If IsNull(Me.[Per Data Birthday]) Then
DoCmd.RunSQL SQL_Per_Birthday
If IsNull(Me.[Per Data Birthplace]) Then
DoCmd.RunSQL SQL_Per_Birthplace
If IsNull(Me.[Per Data Card Detail]) Then
DoCmd.RunSQL SQL_Per_Card
If IsNull(Me.[Per Data Criminal Record]) Then
DoCmd.RunSQL SQL_Per_Crim_Rec
End If
End If
End If
End If
Me.Refresh
End Sub
The code I've written is below (once I get it to work I have numerous other strings and sql statements to add):
Private Sub btnNullToNo_Click()
Dim SQL_Per_Birthday As String
Dim SQL_Per_Birthplace As String
Dim SQL_Per_Card As String
Dim SQL_Per_Crim_Rec As String
SQL_Per_Birthday = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Birthday] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Birthday]) Is Null) " & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Birthplace = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Birthplace] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Birthplace]) Is Null)" & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Card = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Card Detail] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Card Detail]) Is Null)" & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
SQL_Per_Crim_Rec = "UPDATE tbl_Questionnaire_Child SET tbl_Questionnaire_Child.[Per Data Criminal Record] = '2' " & _
"WHERE (((tbl_Questionnaire_Child.[Per Data Criminal Record]) Is Null) " & _
"AND ((tbl_Questionnaire_Child.SID)=[forms]![subfrm_Customer_Suppliers]![LstSupplier])); "
If IsNull(Me.[Per Data Birthday]) Then
DoCmd.RunSQL SQL_Per_Birthday
If IsNull(Me.[Per Data Birthplace]) Then
DoCmd.RunSQL SQL_Per_Birthplace
If IsNull(Me.[Per Data Card Detail]) Then
DoCmd.RunSQL SQL_Per_Card
If IsNull(Me.[Per Data Criminal Record]) Then
DoCmd.RunSQL SQL_Per_Crim_Rec
End If
End If
End If
End If
Me.Refresh
End Sub