Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Numbering Schemes

    I have a client who is using Access 2000. They manufacture widgets and need to have a serial number assigned to each widget. Sounds easy so far, huh?

    Here's the catch. Sometimes their customers tell them what serial numbers they want (so they match the customers product serial numbers). Also, serial numbers are not always unique because sometimes different types of widgets can have the same serial number.

    If the customer is not providing the serial numbers my client wants to automatically assign a serial number. So, in the same field they need the ability to enter a manual serial number and be able to autonumber (or at least know quickly what the next available number in a sequence is).

    I know you can't have a field that autonumbers and lets you assign a number. I haven't been able to figure this one out. Any help would be appreciated.

    Thanks!!!

  2. #2
    DavidMadge
    Guest

    Re: Numbering Schemes

    What about having one table for the actual serial numbers (obviously not an autonumber field) and another table just with an autonumber field. When the customer defines a serial number, you just add it manually to a new record in the serial number table. When you want a number generated automatically, add a new record to the second table, get the new autonumber value from that table and put it into your serial number table. That way your automatically generated numbers will always be sequential.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbering Schemes

    If I understand things correctly then in the table you store the Widget info have an autonumber field (as the main key for the table) and a separate long field (say WidgetID).
    When details of the widget are entered then in the WidgetID the user can enter the special number from the customer. Now in the Form_BeforeUpdate event check if WidgetID has been entered. If not then make a copy of the Autonumber field and put it in the WidgetID field.

    Oh, one small point. your serial number is always numeric isn't it? I ask as I could easily conceive of a serial number such as ABC1234-x

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

    Re: Numbering Schemes

    If the serial number has leading zeros, you'll also have problems with a numeric value. Personally, I'd store the actual serial numbers as text. You aren't going to do calculations on serial numbers, so text is the logical format anyhow, and it will allow you things like letters, symbols/punctuation, and leading zeros. I also wouldn't use the serial number itself as the primary key. I'd use the autonumber and key the serial number unique instead. It would be faster that way, since numeric keys are faster than strings.
    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
  •