Recordset Bookmark return no current record (1 Viewer)

VSolano

Registered User.
Local time
Today, 12:00
Joined
Feb 21, 2017
Messages
85
Hi

I am trying to bookmark the position on a recordset to later return to the same position and the bookmark codes is returning no record.
I have not used this before but any help will be greatly appreciated. The problem is when stepping to RSBookmark = rs4.Bookmark
the rsbookmark has been set as variant

Code:
Do While rs3.EOF = False
                    CrRecord = rs3!benefitsid & rs3!companyid & rs3!entityid & rs3!plandatailid & rs3!PlanCarrier & rs3!PlanType
                    If PrvRecord = CrRecord Then
                        If PrvMonth <> CrMonth Then
                            PrvMonth = Month(rs3!Invoicedate)
                            rs4.Bookmark = RSBookmark
                            TxtMonth = MonthName(Month(rs3!Invoicedate))
                            rs4.AddNew
                            SumInvoiceAmt = DSum("invoiceamount", "tbcolumnreporttemp", StrCriteria)
                            rs4.Fields(TxtMonth) = SumInvoiceAmt
                            rs4.Update
                            
                        End If
                    
                    
                    
                    Else
                    
                        TxtMonth = MonthName(Month(rs3!Invoicedate))
                        rs4.AddNew
                        rs4!benefitsid = rs3!benefitsid
                        rs4!PlanCarrier = rs3!PlanCarrier
                        rs4!entityid = rs3!entityid
                        rs4!companyid = rs3!companyid
                        rs4!PlanType = rs3!PlanType
                        rs4!plandatailid = rs3!plandatailid
                        SumInvoiceAmt = DSum("invoiceamount", "tbcolumnreporttemp", StrCriteria)
                        rs4.Fields(TxtMonth) = SumInvoiceAmt
                        rs4.Update
                        PrvRecord = rs3!benefitsid & rs3!companyid & rs3!entityid & rs3!plandatailid & rs3!PlanCarrier & rs3!PlanType
                        PrvMonth = Month(rs3!Invoicedate)
                        db.TableDefs.Refresh
                        RSBookmark = rs4.Bookmark
                        
                    End If
                    
                    
                    rs3.MoveNext
                    CrRecord = rs3!benefitsid & rs3!companyid & rs3!entityid & rs3!plandatailid & rs3!PlanCarrier & rs3!PlanType
                    CrMonth = Month(rs3!Invoicedate)
                    Debug.Print rs3!Invoicedate
                    
                 Loop
                
                
                
            Stop
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:00
Joined
Oct 29, 2018
Messages
21,360
Hi. I don't see in your code how you declared and set RSBookmark.
 

VSolano

Registered User.
Local time
Today, 12:00
Joined
Feb 21, 2017
Messages
85
Hi. I don't see in your code how you declared and set RSBookmark.

I declared the RSbookmark mark as variant. I did not copy this part of the code but I state it on the information.

the bookmark was set on the else statement
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:00
Joined
Oct 29, 2018
Messages
21,360
I declared the RSbookmark mark as variant. I did not copy this part of the code but I state it on the information.

the bookmark was set on the else statement
Sorry, I took the description in your post as an unexpected outcome when you were debugging. Now, before the Else block, you have the following code:

Code:
rs4.Bookmark = RSBookmark

So, what would it have been set too on the first run if the code doesn't reach the Else statement?
 

VSolano

Registered User.
Local time
Today, 12:00
Joined
Feb 21, 2017
Messages
85
Sorry, I took the description in your post as an unexpected outcome when you were debugging. Now, before the Else block, you have the following code:

Code:
rs4.Bookmark = RSBookmark

So, what would it have been set too on the first run if the code doesn't reach the Else statement?
I tried placing the bookmark at the beginning and I got the same result: No current record
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:00
Joined
Oct 29, 2018
Messages
21,360
I tried placing the bookmark at the beginning and I got the same result: No current record
Right. The bookmark needs to have a current record to accept a value. Without seeing the entire code, or even not being able to step through it, I'm not sure how we will be able to help you. Are you able to post a demo version of your db instead? Also, can you please describe what the code is supposed to be doing? What does each recordset represent?
 

VSolano

Registered User.
Local time
Today, 12:00
Joined
Feb 21, 2017
Messages
85
I am just trying to filter record and copy value base on a criteria. Once the first value is copy I want o bookmark the record on the destination table so I can copy the second value to the same record
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:00
Joined
Oct 29, 2018
Messages
21,360
I am just trying to filter record and copy value base on a criteria. Once the first value is copy I want o bookmark the record on the destination table so I can copy the second value to the same record
Hmm, so why not copy all the values at first pass instead of going back and forth?

PS. Besides, why are you copying records (if that's what you're doing)? Is this for an audit trail?
 

Cronk

Registered User.
Local time
Tomorrow, 03:00
Joined
Jul 4, 2013
Messages
2,770
I can't see any purpose for moving rs4 to the bookmark position. Next line uses rs3 and then a new record is added to rs4.

Also cannot see any point in db.TableDefs.Refresh, especially in a loop.
 

Users who are viewing this thread

Top Bottom