Results 1 to 14 of 14

Thread: Auto Numbering

  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Chantilly, VA USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Numbering

    My business unit maintains an Access 97 database of our documentation. Each document is assigned a number via Access' autonumbering feature.

    Someone accidentally deleted a record. We have the data in hard copy and can recreate it, but can't figure out how to restore the old auto-generated document number. New records get a new number.

    Can anyone help?

    Thanks

    Don

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    I don't think you can replace the auto number once its been deleted. However you have two work a-rounds. BACK UP FIRST!!!

    Make a copy of your auto number in new text field on the damaged table. Add the missing record, put the auto number value thats missing in this text field. delete the auto number field. sort the table on the text field. Make a copy of this table (structure only) append the damaged table to the new table.

    The other way is to add the missing record in a new position and then find all the references to the changed auto number and change them.
    Rupert

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Correction::
    "Make a copy of this table (structure only) append the damaged table to the new table"

    This should read "make a copy of the original table (structure only) append the damaged table to the new table"

    Rupert

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Definitely Back Up the table first. If memory serves me correctly, Access (at least 97) will not allow you to change a field data type to Autonumber if there are records in the table. I've learned from experience, Autonumber fields should not be used as permanent reference numbers. Been there, done that. It's very painful to send a customer a contract with a contract number based on the Autonumber and then find out the contract number has changed because someone accidently deleted the record [img]/w3timages/icons/shocked.gif[/img]
    Anyway, good luck.

  5. #5
    Scott A
    Guest

    Re: Auto Numbering

    You can actually use an append query to insert the missing number. You can just specify the missing number to be inserted into the AutoNumber field. The SQL would look something like:
    INSERT INTO <table name>
    VALUES (<missing number>, <field 1>, <field 2> ...)

    One thing you'll have to do is Compact and Repair the database afterwards. You need to do this because the query, for some reason, resets the AutoNumber back to the number just inserted (so if you used the query to insert the number 5, the next time a new record is added to the table, the AutoNumber would attempt to use 6). By Compacting and Repairing the db, this updates all AutoNumbers.

    I think you might be able to do it use SQL and the ALTER TABLE statment, but Compact and Repair is the quick and easy way.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Numbering

    Sounds like you're using Access 2000, Scott. The Jet 4 SP 5 corrects that little problem of the autonumber seed. Look for jet40sp5_9xnt.exe on the Microsoft Download Center page.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Dear Charlotte,

    Not wishing to call into question the advice of THE guru of this forum, but I went to the download site and found it very confusing - the only reference to SP5 was for Win 2000, whereas I for one am using Win 98 at the moment! Being curious by nature, I clicked on the link anyway, and got Q239114, which lists all versions of the service pack. People should read this whole document before downloading, however.

    Microsoft sending a clear and simple message again![img]/w3timages/icons/laugh.gif[/img]

    kiwi44

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Numbering

    The MS site can be very confusing, but what you need to do if you're looking for a particular download file is to change the search to keyword and put the file name in the input box, then choose your operating system.

    I'm not sure why you were confused by the article. All you have to do is go to the paragraph for Windows 95, 98 and NT and click on the link to jet40sp5_9xnt.exe, which will download the version for 95/98/NT. There's a separate link for 2000 and another for ME. What part were you confused by?

    Call tech support some time and about the first thing they'll ask about if Access is misbehaving is what version of Jet SP you have installed. Tthen they'll recommend you download and install the latest SP. Over the years, I've found that to be very good advice.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    I am going to assume that you are using the auto-number so that all your records are consecutively numbered and the record number matches the record count on the navigation bar (most users like to have it this way). My solution is to set the form properties "Allow Deletions" NO. This will prevent a saved record from being deleted. You can still allow editing, that property is separate. Unfortunately, if you open a "New Record" Access will increment the auto-number. If that record is not saved, then you will loose that number. So rather than use the default numbering system, change the field on the table to a regular number field. Set the default value for that field on the form to count the number of records and increment by one. You can use the DCount function to do this. Now, even after the number is assigned to a "New Record" and you don't save it the number will be used on the next "New Record". If you are in a multi-user environment thre is the possibilty that two users will have two unsaved records open at the same time. To solve this probelem, add the DCount to the before update event of the form so that it recounts before saving and resets the number field to the next highest number. If you want, also add a MsgBox "Due to changes before saving this record has been assigned a new number." I think this is probably the easiest way for a beginner, other solutions require a more indepth knowledge of VBA and the Jet database.

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Sorry, I told you how to keep the problem from happening again not how to solve it now. So, If you go into table design and set the auto-number field to a regular number then insert a new record with the correct number, you should be able to re-assign the field to auto-number as long as all your missing number have been replaced (Access requires a complete and continuous number sequence).

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Numbering

    Changing an autonumber field to a long is a one-way street. You cannot change it back if there is data in the field. What you can do, however, is change it to a long integer and save the table. Make a copy of the table structure only and in that copy, change the field back to an autonumber. Then you can use an append query to append the records from the old table to the new copy. The long integer will append to the autonumber field, and you'll be back in business.

    You should be aware, however, that this is one of the reasons for not trying to enforce consecutive autonumbers. It isn't really practical and it isn't really necessary. You could add a field to your table that would hold the next consecutive number based on a DMax + 1 formula if you must have consecutive numbers, but leave the autonumber as the primary key and don't worry about missing numbers. In fact, if you ever decided to use replication, your autonumber scheme would go straight out the window, since autonumbers are converted to random rather than incremental in replicated tables.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Bingo. I forgot about the replication monster. Losing Autonumbers is one thing but you're right, when I attempted replication !Boom! [img]/w3timages/icons/shocked.gif[/img] Oh well, live and learn.

  13. #13
    Star Lounger
    Join Date
    Jan 2001
    Location
    Bethel, CT, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Numbering

    Sorry again, being new to online forums [img]/w3timages/icons/baby.gif[/img], I hadn't realised just how inconvenient it is to have a mouth that speaks louder than my experience. But I'm wondering, if the DMax + 1 is used, then the auto-number would not, is there any problem with this in replication?

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Numbering

    The DMax + 1 approach would be to generate a sequential number for tracking purposes and to keep the boss happy. You have to do a bit more than that in a multiuser environment, but I simplified for these purposes. The autonumber should still be kept as the primary key. If it makes people happy to search on the sequential number, fine; but that has now become data, and I personally object to using data as a primary key. A PK should be nothing but a PK rather than meaninful data.

    And before this starts a war, I'll state publicly that this opinion is not shared by all the experts, just most of them. I've had several discussions on this with Peter Vogel (Smart Access editor), who prefers to use existing data as keys as long as it's unique. We argued over Social Security Number, which he feels is a valid key. I don't, since SSNs may be entered incorrectly or even issued incorrectly. I try to design database that will handle whatever gets thrown at it, so I try not to use keys that will have to be corrected along the way.
    Charlotte

Posting Permissions

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