Using multiple RunSQL statements

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
 
Hi

It would depend hugely upon what you mean by "move to the next line".
Are you referring to the [Per Data Birthday] field value increasing? Or something else entirely?

Cheers
 
I have a number of option boxes on a screen, if there are boxes that no option has been selected I want them to be auto populated to a No rather than having to manually tick all the No options. Currently the code works if all of the boxes that I'm referencing in my code are null but as soon as it comes across a box that is no null it seems to stop the code.

I wondered if there was a command that followed a "If then update, if not go to the next line - If then update, etc"

As I say, I'm a novice so not sure if this something very basic or simply isn't possible. In the old days of just using queries I'd have created one query per field and popped them into a macro but I'm trying to be "cleaner" with my databases and reduce the number of queries stored.

Thanks for your help
 
Should have made that clearer, not looking for an Else If as I believe that will again stop as soon as it finds a box that fits the first SQL update query. I need something that says:

If [field] is null then update to [No] then go to look at the next field and do the same BUT at the moment if the first field is not null then the code stops as the arguement was if it is null and it isn't!?!?

I need the process to step through each of the fields and update them where they are null irrespective of the ones that are not null.

Hope this makes sense
 
I'm still not clear on what you're doing.
Can you give a concise but full indication of your actual table structure?
And what the data is like - and should become?

Cheers.
 
I have a number of fields related to interview questions that are all populated through option boxes (1=Yes,2=No,3=Unknown). The boxes all start with a null value and as questions are answered through the option boxes they update accordingly. However, the questions are categorised so if there is a category where either all or the majority of the questions are not relevant to that particular interviewee, rather than asking them to manually tick all of the boxes to reflect a No I was looking for a way of adding a button on to the form that when pressed would automatically update all the null fields to No. What I have written works if all the fields are null BUT if there has been a box already populated as soon as the script gets to that field it stops and does not update the remaining fields.
 
You've nested your IF statements

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
Try this instead
Code:
If IsNull(Me.[Per Data Birthday]) Then
DoCmd.RunSQL SQL_Per_Birthday
End If
If IsNull(Me.[Per Data Birthplace]) Then
DoCmd.RunSQL SQL_Per_Birthplace
End If
If IsNull(Me.[Per Data Card Detail]) Then
DoCmd.RunSQL SQL_Per_Card
End If
If IsNull(Me.[Per Data Criminal Record]) Then
DoCmd.RunSQL SQL_Per_Crim_Rec
End If
 

Users who are viewing this thread

Back
Top Bottom