Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: Microsoft Acess Auto Numbering

    Hi,

    I am new at this forum and working with Access.
    At work I use Access database to log my incoming inspection. One of this day the auto numbering for some reason skipped a number. So I went to the design mode and added the missing number. By my surprise, when I when to put back in auto numbering mode I received a message saying that I cannot put back the auto numbering.

    Is there a way that I can put back the auto numbering?

    Thank you.

    Rubem

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    add a new column
    make it an autonumber column
    then drop the original column

    your basic problem is that you have tried to coerce the autinumber system to have a meaning outside the system. something that it shouldn't be used for.

    if you want an auto number system that has a meaning out side the systemk (ie you need to develop your own mechanism to maintian a number system
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As healdem indicated, you're trying to use an Autonumber field for something that it isn't intended to be used for! 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 thing you're trying to do, 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.

    Code:
    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:

    Code:
    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
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •