Running MS Access SQL in VBA

[RossRC]

I'm fairly new to VBA and am having trouble running the below script. I keep getting a Syntax Error within the DoCmd, but I can't figure out what the problem is, and can't find any online references. I have a feeling it's having a problem with using the variables as part of the script.
Any help would be much appreciated.
Thank you.
-------------------------------------------
Public Function UpdtRADR_Region()

Dim REG1 As String
Dim REG2 As String
Dim REG3 As String
Dim REG4 As String
Dim REG5 As String
Dim REG6 As String
Dim REG7 As String
Dim Region As String
Dim StScript As String

REG1 = "MTN"
REG2 = "NCR"
REG3 = "SWR"
REG4 = "MTN"
REG5 = "NER"
REG6 = "SCR"
REG7 = "SER"

For Ct = 1 To 7
Region = "REG" & Ct
DoCmd.RunSQL "UPDATE [tbl_KW_Data-" & Region & _
"SET [tbl_KW_Data-" & Region & "].[Division Name] = Null," & _
"[tbl_KW_Data-" & Region & "].[Profit Center Number] = Null, " & _
"[tbl_KW_Data-" & Region & "].[Profit Center Name] = Null," & _
"[tbl_KW_Data-" & Region & "].[District Number] = Null," & _
"[tbl_KW_Data-" & Region & "].[District Name] = Null," & _
"[tbl_KW_Data-" & Region & "].[Cust Assigned Presale Route Number] = Null;"

Next Ct

End Function

 

[davper]

I think you want to use an array instead of the many REG variables

Dim Region(6) as String 'Arrays start with 0
Dim strSQL as String

Region(0) = "MTN"
Region(1) = "NCR"
Region(2) = "SWR"
Region(3) = "MTN"
Region(4) = "NER"
Region(5) = "SCR"
Region(6) = "SER"

For n = 0 to 6

strSQL = "UPDATE [tbl_KW_Data-" & Region(n) & _
"SET [tbl_KW_Data-" & Region(n) & "].[Division Name] = Null," & _
"[tbl_KW_Data-" & Region(n) & "].[Profit Center Number] = Null, " & _
"[tbl_KW_Data-" & Region(n) & "].[Profit Center Name] = Null," & _
"[tbl_KW_Data-" & Region(n) & "].[District Number] = Null," & _
"[tbl_KW_Data-" & Region(n) & "].[District Name] = Null," & _
"[tbl_KW_Data-" & Region(n) & "].[Cust Assigned Presale Route Number] = Null;"

Docmd.Runsql(strSQL)

next

*Note - I didn't validate your SQL string

 

[RossRC]

That worked perfectly davper.

Thank you so much.