Compact and repair database

[naym]

Hi all,

I ahve come across a small problem and i am wandering if someone can helo me out on this.
I have a programme which is for the fast food and uses a order no for each receipt, now the problem is each time the form is closed and opened the number goes to the next avaialable number so last nunber was 32 the next will be 33 etc this number adding is done by code but i am having a problem somewhere along the lines and it goes back to 1 every so often, so the you have to log off and log in to to to the next available record,

So i was thinking rather having to do the number increment by code why not have a auto number field in there instead, the only problem is at the end of the day the number should go back to 1 for the next day so is there a way to compact and repair the database or better still to zero the tables i wish??

Thank you

Regards
Naym

 

[neileg]

Not a good idea to use an autonumber. This won't guarantee the behaviour you want. It sounds like the code you've got is the right approach but something is corrupting it. Can you post the code that does the incrementing.

 

[naym]

Hi Neileg

Thanks for the reply, here is the code below

I havea orders form witha order number and also a order details subform with a order details id

DoCmd.GoToRecord acDataTable, "Orders", acLast
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Orders")
If rs.EOF = False Then
rs.MoveLast
G_orderId = rs(0)
End If
DoCmd.RunCommand acCmdRecordsGoToLast

the code above is for the subform so when the dtabase is closed or openend it goes to the last record. it is triggered on the load event of the subform.