No current record

[HartJF]

My routine uses a stored append query to place records in a temporary,
persistent table. Immediately after the query is a FindFirst statement
against the temporary table. I noticed that sometimes, some records were
being ignored. I placed a breakpoint on the FindFirst; sometimes, it
continued properly, sometimes, it didn't. If I stopped the program and then
restarted, it always continued properly. I placed a MoveLast statement
against the temporary table between the query and the FindFirst and moved the
breakpoint there. Sometimes, I received a "No current record" error (3021).
In Immediate mode, I printed BOF and EOF statuses, which were both true.
Again, if I stopped and resumed execution, it always continued properly. I
added an error handler to Resume on a 3021; sometimes, it continued properly,
and sometimes, it didn't. During the break in each of the failing cases, I
opened the temporary table, and it did, indeed, have records.

The append query is selecting a few specific records (2 to 5) for further
group processing from a table with over 100,000 records. At the breakpoints,
the table underlying the append query has the appropriate grouped records
flagged for extraction.

What am I missing? I understand that the query does not complete before the
next command is executed, but I thought that at least one resulting record,
if one exists, is captured. I tried the MoveLast to force completion of the
query.

I initialized the recordset variable for the temporary table at the
beginning of the program. After the group processing is complete, a delete
query is executed to clear the table.

I can't manipulate the source of the append query as a recordset because it
is based on a union query which is, by definition, not updatable.

Help!!! I'm using Access 2003 SP3 in a Windows XP environment.

 

[Allen Browne]

How do you run the query? RunSQL?

Try the Execute method, e.g.:
Dim db As DAO.Database
Set db = CurrentDb()
db.execute "Query1", dbfailOnError
Debug.Print db.RecordsAffected & " record(s) inserted."

It should complete even without the Debug.Print. That's just to illustrate
that the results are available at that point.

More about Execute vs RunSQL here:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[HartJF]

Thank you for your reply, Pastor. I'm a part-time church staff member, so
the weekend got in my way.

I had been running the query using DoCmd.OpenQuery. I changed to
CurrentDb.Execute, but it still sporatically gave the "No current record"
error on .MoveLast.

....
'place records in tblSequence
CurrentDb.Execute "qappSequence", dbFailOnError
With rsdSequence
'dbOpenDynaset
..MoveLast
..FindFirst "bytStatus=1"
If Not .NoMatch Then
....

Because the small number of records that meet the criteria in qappSequence
and its precedents, I will try .GetRows against a reformed qrySequence. I
generally disdain the use of variants, but the benefits of reducing the
number of disk read/writes -- and, more importantly, the potential of
avoiding the "No current record" error -- may be more desirable.

Thanks again for

 

[Allen Browne]

Perhaps you are getting that error when there are no records (and hence
there cannot be a current record)?

As explained here:
http://allenbrowne.com/ser-29.html#Move_With_No_Records

 

[HartJF]

Thank you for your response. Your tips are invaluable!

When the code breaks at the .MoveLast method, I view the table against which
rsdSequence is opened. It has records. I postulate that when the .MoveLast
is executed, the append query has not finished and has not released its lock
on the destination table, so no records are exposed to the recordset.

I changed the append query qappSequence to a select query qrySequence and
changed the recordset rsdSequence, a dynaset against tblSequence which was
populated by qappSequence, to rssSequence, a snapshot against qrySequence,
and ran the following code

....
With rssSequence
..Requery
..MoveLast
..MoveFirst
avarCurrent = .GetRows(100)
....

I processed the data in the array, rather than in the recordset. I
eliminated the disk write (qappSequence), several (not-yet-coded) disk
reads in processing, and a disk deletion (qdelSequence: Delete * from
tblSequence). The .Requery and .Move's are slow, but I recognized an
increase in speed of more than five percent for the fetch process -- very
significant in considering about seven percent of the 130K records use this
routine. The gains will be much greater after adding processing to the
routine.

I appreciate your gracious patience with us apprentice developers.