0

I need to update a table that is based on a query. The query (tmp_Assign) is a straight-forward, single table query to pull location and date specific data. The code that is causing the error is below.

Set Assgnrst = CurrentDb.OpenRecordset("tmp_Assign", dbOpenDynaset)
Set SlpOccrst = CurrentDb.OpenRecordset("tmp_Occ", dbOpenDynaset)
Set OpenRms = CurrentDb.OpenRecordset("tmp_Open", dbOpenDynaset)
   
Do While Not Assgnrst.EOF
    SleepChoice = Assgnrst.SleepingChoice
    Arrive = Assgnrst.Arrival
    .
    .
    .
    MemID = Assgnrst.MemberID
    RmID = OpenRms.RmNum
    'End With
    'Add room # to member's gathering record
    strSQL = "Update [Assgnrst] " _
           & "Set [RmNum] = '" & RmID & "' WHERE [GatheringID] = " & GathID & " And " _
           & "[EventYr] = " & EvntYr & " And [MemberID] = " & MemID & ";"
    DoCmd.RunSQL (strSQL)

Prior to running DoCmd.RunSQL, strSQL contains:

Update [Assgnrst] Set [RmNum] = '22A1' WHERE GatheringID = 5 And EventYr = 2021 And [MemberID] = 4290;

I have copied the strSQL into an Access Create Query SQL and it works as expected (using the query name the table is based on).

The reason I've opened the queries as tables is because I tried using update code on the queries (all three need to be updated) but was getting a similar error on all three. I opened the queries as tables and the other two work fine. This one does not.

The elipsis represents code that performs a FindFirst that selects an open room. It works fine. I'm not understanding the error since this whole section is a DO loop based on the Assgnrst table and the SleepChoice variable is set from the Assgnrst table. The variables in the statement have been properly declared and set. The variables are numeric with the exception of RmID and all of the fields exist in the table. Why isn't the table recognized?

Bill Bennett
  • 1
  • 2
  • 1
    Does simply `DoCmd.RunSQL("Update [Assgnrst] Set [RmNum] = '22A1' WHERE GatheringID = 5 And EventYr = 2021 And [MemberID] = 4290;")` cause the same error? – mxmissile May 21 '21 at 13:46
  • @mxmissile - Yes, it does. – Bill Bennett May 21 '21 at 22:07
  • Then you don't have a table/query named `Assgnrst`, only a recordset. – Gustav May 22 '21 at 12:48
  • @Gustav - So what am I missing? The other recordsets that are open aren't giving this error. – Bill Bennett May 22 '21 at 17:00
  • You are apparently missing a table or a query named `Assgnrst`. – Gustav May 22 '21 at 20:22
  • First line of my sample code: Set Assgnrst = CurrentDb.OpenRecordset("tmp_Assign", dbOpenDynaset) Definition of FindNext: Recordset.Update method (DAO) 09/18/2015 Applies to: Access 2013, Office 2013 Syntax expression .Update(UpdateType, Force) expression A variable that represents a Recordset object. – Bill Bennett May 23 '21 at 13:40
  • I did some more research and have changed to the rs.edit method. The rs.update is having issues, but if I cannot figure that out. I will open another issue. – Bill Bennett May 23 '21 at 15:51
  • `Assgnrst` is an object in your VB code, outside of the context of Access's SQL. – mxmissile May 24 '21 at 13:44

0 Answers0