Expected End of Statement thwarting attempt to update multiple tables

[dstorms]

I'm trying to run an update query on multiple tables, and since Access doesn't allow me to update tables from a union query, I'm writing a module as a workaround. So I've set up a temporary recordest (rstName) from the union query (qryEqiupEmplOn ly), and construced a Do-Loop that updates the corresponding table.

Unfortunately the Update SQL code prompts a compile error "Expected End of statement" and highlights "rstName" (line 28) as the culprit. Does anyone know how to fix it?

Option Compare Database

Private Sub AssetUpdate()
  Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
  Dim n As Integer, Table As String
  
   Set db = CurrentDb
   Set n = 1
   
   'Setup Recordset
   SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
         "FROM qryEquipEmplOnly " & _
         "ORDER BY [Employee Name];"
   Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
   
   'Parse thru tables & update asset numbers
   Do Until rstName.EOF
      Table = Switch(rstName!Category = "Computers", "tblComputers", _
            rstName!Category = "Cameras", "tblDECameras", _
            rstName!Category = "Docks", "tblDEDocks", _
            rstName!Category = "MFCs", "tblDEMFCs", _
            rstName!Category = "Monitors", "tblDEMonitors", _
            rstName!Category = "Printers", "tblDEPrinters", _
            rstName!Category = "Other DE", "tblDEOthers")

      SQL = "UPDATE " & Table & " " & _
            "SET AssetNo = n " & _
            "WHERE [Employee Name] = '" rstName![Employee Name] "' "& _
            "AND SerialNo = '" & rstName!SerialNo & "';"
      db.Execute SQL, dbFailOnError
      Set n = n + 1
      rstName.MoveNext
   Loop
   
   Set rstName = Nothing
   Set db = Nothing
End Sub

[Stewart Ross]

Hi. You are missing two '&' operators in line 28. You also have an error in line 27, where you have accidentally put the reference to variable 'n' inside your SQL string. Revised lines shown below.

-Stewart

"SET AssetNo = " & n & _
" WHERE [Employee Name] = '" & rstName![Employee Name] & "' "& _

[dstorms]

Thank you Stewart. That did fix the problem I described above. However, I've run into a different problem. When I try running the code I an error "3061", too few parameters: expected 1. I've determined that it has to do with the Table parameter. Either I'm not getting the value I want or the query the recordset calls is too complex to run. (It's a qruery filtering out one employee name from a previous union query.)

Here's the modified code:

Private Sub AssetUpdate()
  Dim db As DAO.Database, rstName As DAO.Recordset, SQL As String
  Dim n As Integer, Table As String
  
   Set db = CurrentDb
   n = 1
   
   'Setup Recordset
   SQL = "SELECT [Employee Name], SerialNo, AssetNo, Category " & _
         "FROM qryEquipEmplOnly " & _
         "ORDER BY [Employee Name];"
   Set rstName = db.OpenRecordset(SQL, dbOpenDynaset)
   
   'Parse thru tables & update asset numbers
   Do Until rstName.EOF
      Table = Switch(rstName!Category = "Computers", "tblComputers", _
            rstName!Category = "Cameras", "tblDECameras", _
            rstName!Category = "Docks", "tblDEDocks", _
            rstName!Category = "MFCs", "tblDEMFCs", _
            rstName!Category = "Monitors", "tblDEMonitors", _
            rstName!Category = "Printers", "tblDEPrinters", _
            rstName!Category = "Other DE", "tblDEOthers")

      SQL = "UPDATE " & Table & _
            " SET [AssetNo] = " & n & _
            " WHERE [Employee Name] = '" & rstName![Employee Name] & "' " & _
            "AND [SerialNo] = '" & rstName!SerialNo & "';"
      db.Execute SQL, dbFailOnError
      n = n + 1
      rstName.MoveNext
   Loop
   
   Set rstName = Nothing
   Set db = Nothing
End Sub

[Stewart Ross]

Hi. I am assuming that the failure point is at the OpenRecordset line...

Could you check what the Table and SQL variables are set to and post these back so that the syntax of what is actually being passed to OpenRecordset can be seen? Set a breakpoint within your code, and step through each line one by one checking the values of the SQL string and the Table variable as you go.

Just as an aside, is your SerialNo field text? You are enclosing the reference to the field in single quotes, which is fine as long as it is a text field. If it is numeric then the single quotes are incorrect and should be removed.

-Stewart

ps Looking again at the Where clause in your update, does it really belong to the tables that are listed in your Switch statement? It would be unusual to have employee names in such tables, so I wonder if your Where clause actually relates to query qryEquipEmplOnl y instead??? If this is the case you need to completely rethink your Update statement as it cannot work if the Where clause has no relation to the table being updated.

[dstorms]

Stewart,

It turned out that I needed to write a more complex SQL statement, which I did get to work:

      SQL = "UPDATE tblEmployees INNER JOIN " & Table & _
            " ON tblEmployees.EmployeeID = " & Table & ".EmployeeID" & _
            " SET " & Table & ".AssetNo = " & n & _
            " WHERE (((tblEmployees.[Employee Name]) = '" & rstName![Employee Name] & "') " & _
            "AND ((" & Table & ".SerialNo) = '" & rstName!SerialNo & "'));"
      db.Execute SQL, dbFailOnError

The old SQL statemnet was looking for [Employee Name] when the Table had a field [EmployeeID] instead. The [Employee Name] field was actually coming from the tblEmpoyees table, so I had to make the necessary adjustment.

With the new SQL statment it worked beautifullly.

Thanks for your help!