Remember where I was in a table and return to same record

[David Moger]

Hi. I am very new to access and vba but I have learnt a lot and made good progress on a project I am creating.
I have found myself in a place where I am a bit lost.
I have a do loop stepping through a table.
I call a subroutine at the end of the loop before
“table.MoveNext”
“Loop”

The subroutine has a do loop that steps through the same table and when I return from the subroutine I get a
Runtime error 3021
No current record.

I think I need to record the record that I am at before I do the call and then set the recorded position when I return.
Any help would be gratefully received.

 

[CJ_London]

without knowing what your code actually does, the way to note which record you are at is in the form current event, populate a variable (probably a global one, or a tempvar) with the PK of the record you are at. Then use find to return to it. Another possible way is to use the recordset absolute position - but that assumes your code does not change the order or add/delete records.

a way to exit the loop is to use code something like

While not table.eof
    'do something
    table.movenext
Wend

 

[David Moger]

It’s a do until loop that works fine without the call to the subroutine.
Sorry for my ignorance but what is the PK?
The sub only reads the table to extract data from it.
Could you point me at the code that records the absolute position and can return me to it.

 

[CJ_London]

PK=Primary Key (usually an autonumber)
FK=Foreign Key - links a child table to the parent table

table.absolutePosition

If you are still stuck, provide the code you are actually using (copy the code, in the post header click the </> button and paste)

 

[GPGeorge]

It’s a do until loop that works fine without the call to the subroutine.
Sorry for my ignorance but what is the PK?
The sub only reads the table to extract data from it.
Could you point me at the code that records the absolute position and can return me to it.

Given the absence of understanding what PK is, it might also be helpful to review the table design overall.

 

[Gasman]

Are you checking for EOF ?

 

[The_Doc_Man]

If you call a subroutine that will be stepping through the same records that your calling routine was using, one useful and simple method is to make a clone of your outer recordset inside the sub and step through the records using the clone.

Recordset.Clone method (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Use the clone inside the subroutine and just close it when you are done. That way when the subroutine exits, everything is just like you had it when you called the sub because the Exit Sub de-instantiates (dissolves) any variables created locally to the sub.

 

[Pat Hartman]

Typically a batch process, which you are describing, is done using a recordset you open using DAO or ADO, NOT by looping through a form's RecordSource. So, that alone makes me question what you are doing. Code behind a form normally operates on the CURRENT Record of the bound form. Never on all the records of the Recordset.

Please describe the process using business terminology and we can suggest a proper solution.