Lost records due to power cut

[ronniepurpleuk]

Hi All,

We've just had a power cut and 2 of us were in the process of adding a record to the database. The primary field is an Autonumber and, on re-entering the information for the record, we have found that 2 numbers are missing.

Ie. instead of record numbers 1,2,3,4, we now only have 1 & 4. Is there any way of retrieving 2 & 3?

And is there something I can/should do in order to prevent this problem from reoccuring?

We have the Autonumber facility so that, when we add a new record, it automatically enters the next number. Is there a formula I can use so that the default value when adding the record is the number of the previous record + 1?

With thanks for any assistance.

Ronnie

 

[boblarson]

You do NOT, I repeat, YOU DO NOT want to give meaning to an autonumber UNLESS you do NOT need them in sequence because they are not guaranteed to be in sequential order. An autonumber is ONLY good for ensuring a UNIQUE number. If a record is started, but not completed, the autonumber is gone! If you delete a record that number is gone!

So, if you need the number to be in sequence and/or you need no gaps, you should look into changing to a Long Integer and using DMax to determine the last number added and then adding one to it. You should do this step the latest into the record creation process as possible so that you do not wind up with gaps from someone starting a record and not completing it and someone else starting a record while the other person has the number locked.

 

[ronniepurpleuk]

I guess it's not absolutely essential that we have all the numbers there (they are just job numbers) but we do want them in sequence and they have always come up in sequence (with the odd "missing" one).

How do I use the DMax thing? Ie. where do I enter the code required? I'm pretty amateur at this. The records are entered in a form, so do I enter the code in the Job Number field property in the form?

I imagine with a bit of searching the forum, I can find the code to use, or at least an example of it, so I won't ask about that unless I get stuck!!

Thanks again

Ronnie

 

[boblarson]

There are definitely plenty of posts about this. Checkout autonumber and Dmax (in the same search).

 

[EdFred]

Ohhh how I hate autonumbers with their wee beady eyes...

I had this issue with invoice numbers and so I incorporated it in the BeforeUpdate section of the code.

Private Sub Form_BeforeUpdate()
If IsNull(me.Invoice_Number) Then
        Me.Invoice_Number = DMax("[Invoice Number]", "[Invoices]") + 1
End If
End Sub

I could have did it with an nz() but didn't.