Last record in Query

[piersonb]

I am running this query to get the last record on a table:

SELECT Last([OFB numbers].BNumber) AS LastOfBNumber
FROM [OFB numbers];

[OFB numbers] is a table in my db that has the primary key of (BNumber)

When I add a new record everything works correctly (most of the time) and the last record is pulled. New records are added with a VBA statement DAO.AddNew

The problem is for some reason sometimes the last record does not get updated. The information will be added to the table but the last record query still shows the previous record. Once this happens the table locks-up and the only way I can get them to unlock is to do a compact and repair (which since I upgraded to 2007 doesn't work when the DB is on the server though that is a separate issue) or to copy the table and delete all the records from the original table.

I have been trying to trap the Err and correct it but without it updating the last record pointer.

Any thoughts on how I can keep this from happening or fix it automatically when it happens?

 

[SimonB1978]

Hi,

There is possibly other solutions, but one would be to add an autonumber field to your table.

You then change you query to order it by that field. That way it SHOULD always be in the correct order.

Another thing, make sure you do your DAO.Update before running the query.

Hope that helps,

Simon B.

 

[piersonb]

when I first created this DB the table started out using auto numbers. The problem is the first record has to start with either 100 or 0 and then incrinment up I prefere starting at 100.

I have been reading on setting an auto number and feel this might work.

DAO.Update is the last step of the add codes.


Thanks