locked
Import records then set AutoNumber? RRS feed

  • Question

  • We have a new database. In the old db there was no primary ID. Everything was stored as in a spreadsheet in access. Each recordset was issued an ID number (PEBNumber) These records date back 30 years and are required to be maintained indefinitely. So I have uploaded the records and have set the PEBNumber as the primary ID. It is now requested that all future PEBNumbers be self generated.

    Is there a way to accomplish this.

    I thought about removing the PK from the PEBNumber however there are now 12 tables, 100+ queries, 50+ forms & many reports that depend on that PK.

    Is it possible to just change the setting to Auto Number? Will this cause problems?


    Chris Ward
    Wednesday, September 28, 2011 2:40 AM

Answers

  • Here's a Microsoft KB article that explains the custom counter
    approach.
     
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Proposed as answer by Bruce Song Friday, October 7, 2011 8:55 AM
    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:00 AM
    Friday, September 30, 2011 7:06 PM

All replies

  • As with all major changes, you will first make them on your development computer, then deploy a beta version to your testers, before taking it into production.

    My guess is that at the very least you will have to remove (comment out) code that currently generates this PebNumber. I'm not expecting major problems, just make sure you test it really well.


    -Tom. Microsoft Access MVP
    Wednesday, September 28, 2011 3:00 AM
  • Hello Tom,

    I don't know what your answer is here.

    I don't have any code that currently generates a PEB Number. The new db has been in use for several months now. They want to change so that the PEB Numbers self generate. I thought I should use the Auto Number format for the PEBNumber as that is already the PK. When I try to change it, Access reports: Cannot change due to relationships based on that field.

    I am hoping for a way to do it anyway or a good work around.

    Is a way to auto-generate the next PEBNumber without using AutoNumber

    Thank you for your time.


    Chris Ward
    Wednesday, September 28, 2011 1:16 PM
  • I think what Tom is saying that you can try (in a DEV copy of the
    database) just making the current PEBNumber an Autonumber and see how
    it works.  On any forms where the user was able to enter or change
    that field, it will switch to read-only.  Any code that attempts to
    set or change that field will fail with an error (but it sounds like
    you don't have any).
     
    The only way to set an existing Autonumber field with a specific value
    is using an Append query for new records - Access allows that.  It
    cannot be set on a form or in code.
     
    Remember that Autonumbers will have gaps in the sequence, and you
    shouldn't count on them having any special meaning except as a unique
    identifier.  If this won't work for you, then you can store the Next
    Available PEBNumber in a separate configuration table, then use a VBA
    function to update that record and increment the number.
     
    Hope this helps,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    Thursday, September 29, 2011 8:47 PM
  • Thanks Armen,

    The PEB Number is a must have. Each product is evaluated and all information is stored based on that number in several places. The Product Evaluation Board (PEB) Through the Product Evaluation Manager assigns the number. For us that means he would like the db, as he is entering data to assign the next PEB Number so he doesn't have to look it up. I thought using AutoNumber would fix that but since you mentioned the sequencing issue I need a better solution.

    Will you please elaborate on: ...store the Next Available PEBNumber in a separate configuration table, then use a VBA function to update that record and increment the number...

    If you know any good links for studying that would also be great

    Thank you!!!


    Chris Ward
    Thursday, September 29, 2011 9:14 PM
  • Here's a Microsoft KB article that explains the custom counter
    approach.
     
     
    Cheers,
     
    Armen Stein
    Microsoft Access MVP
    www.JStreetTech.com
     
     

    Armen Stein, Access MVP J Street Technology www.JStreetTech.com
    • Proposed as answer by Bruce Song Friday, October 7, 2011 8:55 AM
    • Marked as answer by Bruce Song Tuesday, October 11, 2011 3:00 AM
    Friday, September 30, 2011 7:06 PM