Need sequential numbers sometimes

[itconc]

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

[Browni]

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.

[ruirib]

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.

[MarkLiquorman]

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.

[iansavell]

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.

[itconc]

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.

[ruirib]

It's easy to avoid repeated numbers, just change the column to have a unique index.