Sometimes, top-values query does not return top-most value ???

[MLH]

Have a data entry form bound to a table.
Has a save button on it that launches DoCmd.RunCommand acCmdSaveRecord
in Access 97. In the form after-update code, I have a line looking
something like this:

LastVehicleJobID = DLookup("[VehicleJobID]", "qryGetLastVehicleJobID")

qryGetLastVehicleJobID looks something like this:
SELECT TOP 1 tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs
ORDER BY tblVehicleJobs.VehicleJobID DESC;

Immediately after the RunCommand acCmdSaveRecord line,
sometimes the value returned by the top values query is the
ID of not the newly added record, but the one before it. Not all
the time does this happen. But sometimes it happens. Is that
normal? I do not have a DoEvents line after the acCmdSaveRecord
line. Should I have to? Previously, I've been under the impression
that Form AfterUpdate code did not run until the write to the table
had been made and was finished. Is that the way it's supposed to be?

[Allen Browne]

It should work if the data is in an Access table (not something linked to a
different database program.)

Instead of the RunCommand, try:
Me.Dirty = False
That should notify you if the record did not save.

Some other cases where it might not work include:
- Existing record (so the entry is not the last one.)
- New record not dirtied (so the last one doesn't match.)
- AutoNumber is Random instead of Incremental.
- Seed of AutoNumber has been reset (usually bug.)
- Another user saved another record since (rare.)

Is there any reason you are looking in the table instead of reading the
value from the form itself? (Access 97 can report the wrong number in
Form_AfterUpdate, and rarely in Form_ BeforeUpdate too, so perhaps you are
trying to avoid this.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:jj********************************@4ax.com...

Have a data entry form bound to a table.
Has a save button on it that launches DoCmd.RunCommand acCmdSaveRecord
in Access 97. In the form after-update code, I have a line looking
something like this:

LastVehicleJobID = DLookup("[VehicleJobID]", "qryGetLastVehicleJobID")

qryGetLastVehicleJobID looks something like this:
SELECT TOP 1 tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs
ORDER BY tblVehicleJobs.VehicleJobID DESC;

Immediately after the RunCommand acCmdSaveRecord line,
sometimes the value returned by the top values query is the
ID of not the newly added record, but the one before it. Not all
the time does this happen. But sometimes it happens. Is that
normal? I do not have a DoEvents line after the acCmdSaveRecord
line. Should I have to? Previously, I've been under the impression
that Form AfterUpdate code did not run until the write to the table
had been made and was finished. Is that the way it's supposed to be?

[MLH]

On Sun, 8 Apr 2007 17:35:40 +0800, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Is there any reason you are looking in the table instead of reading the
value from the form itself? (Access 97 can report the wrong number in
Form_AfterUpdate, and rarely in Form_ BeforeUpdate too, so perhaps you are
trying to avoid this.)

As a matter of fact, I haven't a good reason for reading the table
value instead of reading the AutoNumber field value right off the
form itself. I'm going to start doing just that. None of the other
conditions you pointed out are applicable. Its as if a race condition
exists in the process and the winner is the process trying to read
the new record and the loser is the process writing the new record.
At this point, I don't give a flying shit about the WHY? I'll settle
for the right value - and that will appear in the VehicleJobID field
on the form EVERY time. That much is for certain. Up until yesterday,
I did not have the VehicleJobID field on the data entry form - since
it was not a field that I thought necessary. Big wake up call - here's
a perfectly good reason for it.

Whenever this problem occurred, I've gone back to MAKE SURE THE
NEW RECORD WAS WRITTEN. It was - every single time. For two years,
the procedure in the AfterUpdate code has been reading the new record
ID field value from the table. 99% of the time, it snatches the value
of the NEW record. Every now and then, though, it gets the value of
the last record BEFORE the newly added record is written. I know it
immediately when it happens because letters that should-a-been printed
do not get printed and I find myself going Duhhhhh??????

At first, I was planning on reading the keyfield value of the last
rec in table BEFORE appending a new record, reading it again AFTER
appending the new record and comparing the values to LOOK for this
anomoly. But who the fuck cares about "catching it in the act" of
being wrong. Its too fuckin' late then. I think its a built-in
Micro$oft fuck-up - you know, shit happens. I'd rather have a work-
a-round tthat circumvents the problem than to have the problem and
go "Whoopee! I found it!" I appreciate the feedback.