Run multiple SQL queries

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I need to run several update queries on one table. I
created a command button with a series of StrSql
statements behind it. When I click the button, it says
it's about to update the records, and when I click OK,
nothing happens. I check the table and nothing was
updated.

I read this article about running multiple Sql queries
(http://www.mvps.org/access/queries/qry0014.htm), but I'm
hoping there's a simpler solution using the command
button and underlying code.

Any ideas? Thanks. - Kurt (code below)

Private Sub cmdUpdate_Click()
On Error GoTo Err_Cancel

Dim StrSql As String

''''''''''''''''''''''''''''''''''''''''''''''

' Enter numidnew (numid in 000 format) value

StrSql = "UPDATE tblTeachersNew SET
tblTeachersNew.numidnew = " & _
"Format([numid],'000');"

''''''''''''''''''''''''''''''''''''''''''''''

' Enter RoomSort (RoomNumber in 000 format) value

StrSql = "UPDATE tblTeachersNew SET
tblTeachersNew.RoomSort = " & _
"IIf(IsNumeric([RoomNumber]),Format
([RoomNumber],'000'),[RoomNumber]) " & _
"WHERE (((tblTeachersNew.RoomNumber) Is Not Null));"

'''''''''''''''''''''''''''''''''''''''''''''''

' Enter TeachID (SchoolPrefix + numidnew) value

StrSql = "UPDATE tblTeachersNew SET
tblTeachersNew.TeachID = [SchoolPrefix] & [numidnew];"

'''''''''''''''''''''''''''''''''''''''''''''''

DoCmd.RunSQL StrSql

Exit_Cancel:
Exit Sub
Err_Cancel:
MsgBox Err.Number & Err.Description
Resume Exit_Cancel

Exit_cmdUpdate_Click:
Exit Sub

End Sub
 
Back
Top