Reinstate deleted record.

[suitejudyb]

Help!
I've inadvertently deleted a record and now the autonumber of each form (which I use for information) doesn't tally with the record number. This makes printing and saving tricky although not impossible!!
Is there any way of reinstating a record, even a blank one, to bring the numbers into line?
Judy

 

[DCrake]

If you delete a record in a table that has autonumbers then that number is lost it does not reinstate missing numbers. If you have used this in some way for your record number you need to look at revising how you generate your record number. How are you doing it now?

David

 

[missinglinq]

Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.

For the kind of field you're using, you need to use an auto-incrementing number hack such as one of these.

The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

If you insist on using a Numerical field the code would be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub

 

[gemma-the-husky]

you can however easily write an append query to specifically reinsert an item, and you can specify the value you want for the autonumber field

 

[missinglinq]

Afraid not Dave! You cannot assign a value to a field defined as an Autonumber datatype, only Access can do that!

You could change the datatype of the field from Autonumber to Number with Long Integer as the Field Size, then assign the missing number to the field. After that you'd have to use a hack as I outlined above to increment the number instead of relying on Access to do so as it would with the Autonumber.

 

[SOS]

Afraid not Dave! You cannot assign a value to a field defined as an Autonumber datatype, only Access can do that! .

Actually you CAN use an Append Query to insert a record using a specific autonumber (even though an autonumber is not normally writable).

Judy:

One more thing to remember is that your "record count" in the navigation of the form does NOT, I repeat, does NOT necessarily fall in the same order each time you open the form. You should NOT associate the record number shown there (Record 5 of 2230, for example) with an actual record. This is ONLY good for the current session for which your form is opened and will change based on a requery, if records are added, etc.

The records in an Access table are not stored in any particular order (although it does normally order them automatically for DISPLAY by a primary key, if one exists). Should you want to have a specific order, you should always use a query with an order by clause for your forms and reports.

 

[suitejudyb]

Thanks for your suggestions everyone - although I'm not sure I understand them all! I'm going to try changing the datatype from autonumber first or possibly apend query.
Judy

 

[gemma-the-husky]

i really wouldnt change the autonumber type.

you definitely can force a value into an autonumber field - and its not hard at all - simply fix and you are back on track.