Solved .Filter Recordset "No Current Record" Error / UpSerting in VBA (1 Viewer)

[raziel3]

I'm now trying to learn Recordsets so bear with me.

Dim db As DAO.Database
Dim PrimaryTRS As DAO.Recordset
Dim ItemsRS As DAO.Recordset
Dim NewItemsRS As DAO.Recordset
Dim ItemsSql As String


Set db = CurrentDb
Set PrimaryTRS = db.OpenRecordset("tblItem", dbOpenDynaset)
ItemsSql = "SELECT Items.PIN, Items.AVGCOST, Items.LASTCOST, Items.ITEMWGHT, tblITEM.PRICEID " & _
            "FROM Items LEFT JOIN tblITEM ON Items.PIN = tblITEM.PRICEID"
Set ItemsRS = db.OpenRecordset(ItemsSql, dbOpenSnapshot)

        PrimaryTRS.MoveLast
        Debug.Print PrimaryTRS.RecordCount       '182 Records OK
        ItemsRS.MoveLast
        Debug.Print .RecordCount                         '181 Records OK
        ItemsRS.Filter = IsNull(ItemsRS.Fields("PRICEID").Value)
        Set NewItemsRS = ItemsRS.OpenRecordset
        NewItemsRS.MoveLast
        Debug.Print NewItemsRS.RecordCount     'ERROR "NO CURRENT RECORD"

ItemsRS.Close
PrimaryTRS.Close
NewItemsRS.Close

End Sub

The Filtered query has 3 records.


Can someone please show me where I'm going wrong?

 

[moke123]

I think you may want

ItemsRS.Filter = "PRICEID is null"

 

[raziel3]

Thanks, that did it.

Is this the correct way to filter a recordset in vba?

 

[June7]

Could include filter criteria in the SQL statement and only retrieve needed records. I have never used recordset Filter property.

Why open a recordset then don't do anything with it?

 

[raziel3]

This is part of the the problem I had in this post

How to make an UPSERT query using another Query as the source

I was reading this thread https://www.access-programmers.co.uk/forums/threads/append-if-rows-do-not-exists-else-update-if-exists.320254/page-2 and was wondering how to do an UPSERT using a query (not a table) as the source. I am trying the method described here and I am getting a "Operation...

www.access-programmers.co.uk

ItemsSql is read only but wanted to update and/or append tblITEMS. The filtered recordset will capture the missing items and append the table. The unfiltered recordset will update some of the fields in the tblITEMS.

I don't know if there's a simpler way?

 

[June7]

Set NewItemsRS = db.OpenRecordset(ItemsSql & " WHERE PRICEID is null", dbOpenSnapshot)

Exactly how do these recordsets edit table? Are you still developing that code?

 

[raziel3]

Are you still developing that code?

Yes

 

[June7]

Options for updating records with data from non-editable source:

1. save data to temp table and use that table in UPSERT sql

2. use domain aggregate functions to UPDATE with aggregate data, run a separate SQL to append new records

3. VBA loops source recordset and updates appropriate record in another recordset based on table or executes UPDATE action SQL, execute separate SQL to append new records

 

[raziel3]

I am trying to add the update portion to the code but it is updating the 1st record in tblITEM with the 1st record in ItemsRS. The both recordsets are not sorted so the wrong values are being populated in tblITEM.

Dim db As DAO.Database
Dim PrimaryTRS As DAO.Recordset
Dim ItemsRS As DAO.Recordset
Dim NewItemsRS As DAO.Recordset
Dim ItemsSql As String

Set db = CurrentDb
Set PrimaryTRS = db.OpenRecordset("tblItem", dbOpenDynaset)
ItemsSql = "SELECT Items.PIN, Items.AVGCOST, Items.LASTCOST, Items.ITEMWGHT, tblITEM.PRICEID " & _
            "FROM Items LEFT JOIN tblITEM ON Items.PIN = tblITEM.PRICEID"
Set ItemsRS = db.OpenRecordset(ItemsSql, dbOpenSnapshot)

With ItemsRS
    .Filter = "PRICEID is null"
    Set NewItemsRS = .OpenRecordset
    .MoveLast
        If .RecordCount <> 0 Then
            Debug.Print NewItemsRS.RecordCount
            With NewItemsRS
                Do Until .EOF = True
                    PrimaryTRS.AddNew
                    PrimaryTRS!PRICEID = !PIN
                    Debug.Print !PIN
                    PrimaryTRS.Update
                    .MoveNext
                Loop
            End With
        Else
            NewItemsRS.Close
        End If
    .MoveFirst
    Do Until .EOF = True
        PrimaryTRS.Edit
        PrimaryTRS!AVGCOST = !AVGCOST
        Debug.Print !PIN
        PrimaryTRS.Update
        .MoveNext
    Loop
End With

ItemsRS.Close
PrimaryTRS.Close
NewItemsRS.Close

I thought that the join in the sql would update the correct records no matter the sorting?

 

[June7]

Your code just sits on first record in PrimaryTRS so of course it will be the record edited in each iteration of ItemRS loop. The JOIN in ItemRS is not pertinent to which record in PrimaryTRS is updated. The recordsets are independent objects and have no knowledge of each other. You need to FIND appropriate record in PrimaryTRS to UPDATE. That's what DAO FindFirst method is for. Use ID from ItemRS record to search for same ID in PrimaryTRS.

Unless both recordsets are filtered to same records and sorted in same order so that looping each will keep them synchronized. This would involve nested loops.

 

[moke123]

I'm a little confused with your tables and recordsets.

Why the 2nd loop to update the primary table? Can't that be done in the .Addnew loop?

it also appears you are moving last for your recordcount and not returning to the first record before looping.

Post a stripped down copy with a couple records .

 

[raziel3]

I've attached the db.
I want to be able to take PIN, AVGCOST and ITEMWGHT from query Items and append to the table tblITEM if it does not exist

If PIN exist in tblITEM.PRICEID update tblITEM.AVGCOST and tblITEM.IWGTTNS with Items.AVGCOST and Items.ITEMWGHT

The query Items is an aggregate query that is also dynamic. Once a new variation of an item is created in the tables PROAZ or PROBRC it will show up in the query Items, hence the need for the append.

 

[MajP]

Untested but something like this. You are not doing yourself or anyone any favors with nested With End With. Makes the codes super hard to read.

Private Sub Command76_Click()

Dim db As DAO.Database
Dim PrimaryTRS As DAO.Recordset
Dim ItemsRS As DAO.Recordset
Dim NewItemsRS As DAO.Recordset
Dim strSql As String


Set db = CurrentDb
Set PrimaryTRS = db.OpenRecordset("tblItem", dbOpenDynaset)
strSql = "SELECT * from Items"
Set ItemsRS = db.OpenRecordset(strSql, dbOpenSnapshot)
strSql = "SELECT Items.PIN, Items.AVGCOST, Items.LASTCOST, Items.ITEMWGHT, tblITEM.PRICEID " & _
            "FROM Items LEFT JOIN tblITEM ON Items.PIN = tblITEM.PRICEID WHERE PriceID is NULL"
Set NewItemRs = db.OpenRecordset(strSql)

      
'AddNew
Do While Not NewItemsRS.EOF
   PrimaryTRS.AddNew
   PrimaryTRS!PRICEID = NewItemsRS!pin
   Debug.Print NewItemsRS!pin
   PrimaryTRS.Update
   NewItemsRS.MoveNext
Loop
NewItemsRS.Close
' Update

Do While Not ItemRS.EOF
     PrimaryTRS.FindFirst "PriceID = '" & ItemRS!pin & "'"
     If Not PrimaryTRS.NoMatch Then
       PrimaryTRS.Edit
       PrimaryTRS!AVGCOST = !AVGCOST
       Debug.Print !pin
       PrimaryTRS.Update
      End If
    itemRS .MoveNext
  Loop

ItemsRS.Close
PrimaryTRS.Close

 

[June7]

Please address this question: Why do you want to save aggregate data that can be calculated when needed?

 

[MajP]

Or simpler instead of multiple recordsets moving at one time

Do While Not ItemRS.EOF
     strSql = "Update tblItems set AVGCOST = " & ItemRS!avgCost & " WHERE PRICEID = '" & ITEMRS!PIN & "'"
     currentDb.execute strSql
     itemRS .MoveNext
Loop

 

[June7]

I have often read that using CurrentDb in a loop is bad practice - that should instead declare and set a db object variable outside loop and reference that variable inside loop.

 

[MajP]

That is actually a typo since it would replace the existing code and the OP already declared the "DB".
Something like

Private Sub Command76_Click()

Dim db As DAO.Database
Dim PrimaryTRS As DAO.Recordset
Dim ItemsRS As DAO.Recordset
Dim NewItemsRS As DAO.Recordset
Dim strSql As String


Set db = CurrentDb
Set PrimaryTRS = db.OpenRecordset("tblItem", dbOpenDynaset)
strSql = "SELECT * from Items"
Set ItemsRS = db.OpenRecordset(strSql, dbOpenSnapshot)
strSql = "SELECT Items.PIN, Items.AVGCOST, Items.LASTCOST, Items.ITEMWGHT, tblITEM.PRICEID " & _
            "FROM Items LEFT JOIN tblITEM ON Items.PIN = tblITEM.PRICEID WHERE PriceID is NULL"
Set NewItemRs = db.OpenRecordset(strSql)

     
'AddNew
Do While Not NewItemsRS.EOF
  strSql = "Insert into TblItems (PRICEID) values ('" & NewItemsRS!PIN & "')
  db.execute strSql 
  newItemsRS.movenext
Loop
NewItemsRS.Close

' Update
Do While Not ItemRS.EOF
     strSql = "Update tblItems set AVGCOST = " & ItemRS!avgCost & " WHERE PRICEID = '" & ITEMRS!PIN & "'"
    Db.execute strSql
     itemRS .MoveNext
Loop

ItemsRS.Close
PrimaryTRS.Close

 

[raziel3]

Please address this question: Why do you want to save aggregate data that can be calculated when needed?

A couple of reasons.

1. The database has 10,000+ records. I am aggregating for a 2-year comparison each year has about 6000 records and hundreds of Item variations. AVGITEMCOST takes really long time to load sometimes even crashing the db. Putting the results in a table makes it easier to access.

2. Because of the subquery in a couple of the source queries I cannot create a crosstab query for comparative reporting.

3. As of now, I'm dealing with 2022 and 2023. AVGITEMCOST tabulates the data from the current and last year meaning that in 2024 it will aggregate 2023 and 2024. Having the 2022 data in a table will give me the historical costing. Yeah, I know I can do a 2022/2023 query and a 2024/2025 query but then how do I crosstab those 4 years? It is already struggling to do 2 years at a time, I don't even want to try and do 3 years at a time.

4. It is much easier to set up the "UPSERT" to run as soon as the db opens or after some event and just use the tblITEMS for reporting because all the queries are geared toward creating the results in tblITEMS. Instead of relying on the queries which are so heavy/complex, I'll just reference the table which is more stable.

 

[June7]

Another approach to saving this aggregate data is to just save the aggregate calc and identifier to a different table that can join to primary table, yes that would be a 1-to-1 relationship. Table can be deleted and recreated or table can be permanent and all or some records would be deleted and replaced with complete set. In either case, code is much simpler. I have read that latter method can be a source of db bloat but I have used this 'temp table' technique and not been aware of bloat.

Is this a split multi-user db? Would multiple simultaneous users be performing this same data edit? Or trying to do normal data edit for existing records impacted by this UPDATE?

 

[moke123]

Table can be deleted and recreated or table can be permanent and all or some records would be deleted and replaced with complete set.

I have read that latter method can be a source of db bloat but I have used this 'temp table' technique and not been aware of bloat.

Another alternative is using a local Temp Database or Side database. I use them all the time and set a custom property "Expiration Date", and after a period of time the temp database is deleted and recreated.