Autonumber error (1 Viewer)

Gkirkup · Feb 18, 2013

I have a table with an autonumber field, which of course is indexed with no duplicates. Twice in recent days it has attempted to add a record with an autonumber that is not the high number - it is about 20 numbers below the high number. So we get a 'can't add this record' error.
I can fix this by copying the table to a temporary table and then copying it it back. Then the autonumber works correctly.
What can I do to prevent this? By the way we updated to Access 2010 a couple of weeks ago, but the data is still Access 2003. We are reluctant to update the data yet in case it causes more problems.

Robert

Beetle · Feb 18, 2013

If you haven't already, run a Compact & Repair, then see if the problem persists.

John Big Booty · Feb 18, 2013

This article may also be enlightening.

gemma-the-husky · Feb 18, 2013

if not fixed, write an append query to insert a record with the correct autonumber "seed".

that will fix it.

occasionally the "seed " for an autonumber goes wrong, and you have ot reset it.

Gkirkup · Feb 19, 2013

Yes, Compact and Repair was the first thing I tried. Didn't work. The only fix I could find was to copy the table to a temp table, and back to the correct table. This works but disrupts all users.
What is the correct autonumber 'seed'?
Robert

Beetle · Feb 19, 2013

The seed is the next number to be used, so would be one higher than the highest value that currently exists in the table.

Solo712 · Feb 19, 2013

Gkirkup said:
Yes, Compact and Repair was the first thing I tried. Didn't work. The only fix I could find was to copy the table to a temp table, and back to the correct table. This works but disrupts all users.
What is the correct autonumber 'seed'?
Robert

Robert,
just in case you want to try a VBA-generated sequence number, here is a generic routine which you might find handy. In the form's Before_Update execute the function to get the next number in the field. You can use this with any table and any field defined as long integer.

Code:

If Me.NewRecord Then 
     Me!MyField = NextNumber("MyTable", "MyField") 
End If

Place the following in your global utilities module for the db.

Code:

Public Function NextNumber(tTable As String, fField As String) As Long
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset, SQLstr As String
  Dim i As Integer
  Set dbs = CurrentDb
  SQLstr = "SELECT * FROM " & tTable & " ORDER BY " & fField
  On Error GoTo NextNumber_Error
  Set rst = dbs.OpenRecordset(SQLstr, dbOpenDynaset)
 
  If Not rst.EOF Then
    rst.MoveLast
    For i = 0 To rst.Fields.Count - 1
      If rst.Fields(i).Name = fField Then
         NextNumber = rst.Fields(i).Value
         Exit For
      End If
    Next i
  End If
 
  NextNumber = NextNumber + 1
 
ExitNextNumber:
  rst.Close
  dbs.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Function
 
NextNumber_Error:
  MsgBox "NextNumber Error: " & Err.Number & " " & Err.Description
  NextNumber = 0
  Resume ExitNextNumber
End Function

If you are interested I can show you how to compact and re-seed the VBA-generated sequence numbers.

Best,
Jiri

Galaxiom · Feb 19, 2013

Solo712 said:
Robert,
just in case you want to try a VBA-generated sequence number, here is a generic routine which you might find handy.

I don't see any advantage with that routine over simply using a DLookup.

BTW The loop to find the named field is clumsy and quite unnecessary as it could be achieved with:

rst.Fields(fField)

Solo712 · Feb 19, 2013

Galaxiom said:
I don't see any advantage with that routine over simply using a DLookup.

That would be DMax I guess. I have actually never tried the domain functions with variables. I trust they work.

This is actually just part of the routine that I use. There is an optional switch that compacts the number set before issuing the next number.

BTW The loop to find the named field is clumsy and quite unnecessary as it could be achieved with:

rst.Fields(fField)

Great !

I didn't realize I can use the variable name as an argument to Fields. That certainly simplifies things. Thanks!

Best,
Jiri

Galaxiom · Feb 19, 2013

Solo712 said:
I have actually never tried the domain functions with variables. I trust they work.

Yes. Just remember that the arguments are all strings.
eg

Code:

x = DMax(fField,tTable,"sometextfield='" & strVariable & "'")

Code:

x = DMax(fField,tTable,"sometextfield=" & intVariable)

Solo712 · Feb 20, 2013

Galaxiom said:
Yes. Just remember that the arguments are all strings.
eg

Code:

x = DMax(fField,tTable,"sometextfield='" & strVariable & "'")

Code:

x = DMax(fField,tTable,"sometextfield=" & intVariable)

Thanks, but I am sticking with my function to get the next number for the field. As I said the full function optionally compacts the number set by reassigning values from deleted records before showing the next available number. This comes in handy eg. after archiving a portion of the table.

Best,
Jiri

Autonumber error (1 Viewer)

Gkirkup

Registered User.

Beetle

Duly Registered Boozer

John Big Booty

AWF VIP

gemma-the-husky

Super Moderator

Gkirkup

Registered User.

Beetle

Duly Registered Boozer

Solo712

Registered User.

Galaxiom

Super Moderator

Solo712

Registered User.

Galaxiom

Super Moderator

Solo712

Registered User.

Similar threads

Users who are viewing this thread