Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need sequential numbers sometimes

    I have a table for vendors. When we get a new vendor, the vendor number has not been assigned. I would like to find a way to assign a sequential number until we get the correct vendor number. How can I do this?

    Thanks for any help.

    itconc

  2. #2
    WS Lounge VIP Browni's Avatar
    Join Date
    Dec 2009
    Location
    Rochdale, UK
    Posts
    1,651
    Thanks
    38
    Thanked 161 Times in 139 Posts
    Not sure what you mean by correct vendor number but you can find the highest vendor number used by firing of a query to the database along the lines of

    select max(vendorno) as maxvendorno from vendors

    Then simply add 1 to it and apply it to the new vendor.
    Last edited by Browni; 2012-06-27 at 17:45.

  3. #3
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    There is no automated way to get sequences in Access. You can probably write a VBA function that looks for the values in the column and assigns a new record the first available value.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by itconc View Post
    I have a table for vendors. When we get a new vendor, the vendor number has not been assigned. I would like to find a way to assign a sequential number until we get the correct vendor number. How can I do this?

    Thanks for any help.

    itconc
    The only thing I can think of is to have 2 fields. VendorID would be an autonumber and is the Primary Key of the vendor table. It is the field that links to the other tables. The users never see this field.

    Another field would be "VendorNo" (or whatever). This is the field users will see. You can make anything in this field, even "Temp", and then enter the correct # when you get it (I'm assuming it is assigned by some other department). Changing this VendorNo won't affect anything. You don't have to Cascade updates or anything like that.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    itconc (2012-06-28)

  6. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Mark has the right idea, treat the "assigned" vendor number as just another data item like vendor name. Using an auto-increment field as the table primary key. That avoids a lot of problems if you have related records linked by a vendor number that will change when credit control have approved the vendor (or whatever triggers the assignment of the number). For most purposes you can hide the primary key field.

    In Access forms the autoincrement number is assigned as soon as you enter something in the "new" record so there's no problem creating linked records. In other cases you might need to re-read the record to retrieve the primary key. If you rarely enter vendors and the number is assigned quickly you could set a default value for the vendor number field and make the field a unique index. You can then easily find the newly created record from the default vendor number and retrieve it's primary key value.

    If you routinely add several vendors before getting an assigned vendor number you'll need unique temporary vendor numbers. Following Browni's suggestion, add some VBA to the "before insert" event on the form like

    vendornumber = nz(dmax("vendornumber", "vendors"),0) + 1

    That will have the effect of adding a sequential vendor number as you start to enter the new vendor details. (the nz() is just in case someone picks up this code and tries it on an empty table)

    Ian.

  7. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks to all of you.
    I will think about Mark's suggestion. We have to have access to the vendor number - it is assigned by our outsorced group in India.
    I am not sure I like the autonumber. There is a tendency to enter the vendor number twice. Autonumbers would not take care of that issue.
    However, I will think about this and might try it.
    Thanks again.

  8. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It's easy to avoid repeated numbers, just change the column to have a unique index.

Posting Permissions

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