Autonumbers

[Guest]

I run a supply database and order from multiple companies. My users have the
opportunity to use the database to request supplies. I have different
tables/forms for each vendor describing what they offer. These tables/forms
are linked together through an OrderRequest table which tracks order numbers.

I would like to have different Order Numbers for each type of company. For
example if an order is placed using CompanyA Form I would like the UserNumber
(autonumber) to display something like A001, and if an order is placed using
CompanyB Form the autonumber in OrderRequest table to display B002.

I want these text prefixes to be embedded in the table so that I can
reference them with queries. Anyone got any ideas?

 

[Guest]

You can not do this with an autonumber. You will have to write some code in
your forms before update to assign a number. You may want to look at your
design if you have a different table for each vendor. If the tables are the
same structure, you should just add an identifier to show the vendor and
filter the forms, queries and reports how you would like to see the data.
Right now, from what you are describing, you will have to create all new
forms and tables to add a new vendor.

 

[Joseph Meehan]

I run a supply database and order from multiple companies. My users
have the opportunity to use the database to request supplies. I have
different tables/forms for each vendor describing what they offer.
These tables/forms are linked together through an OrderRequest table
which tracks order numbers.

I would like to have different Order Numbers for each type of
company. For example if an order is placed using CompanyA Form I
would like the UserNumber (autonumber) to display something like
A001, and if an order is placed using CompanyB Form the autonumber in
OrderRequest table to display B002.

I want these text prefixes to be embedded in the table so that I can
reference them with queries. Anyone got any ideas?

First remember Autonumbers are designed to provide unique numbers. It
in not designed to provide numbers in order and for a number of reasons may
not do so. As a result using them in any application where the user sees
the numbers is likely to end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

Assuming you don't mind the occasional missed number or strange number
(I suspect someone using the database will and will bug you about it.) then
I suggest using two fields. A letter field and the number field. Just
display both as the User Number and don't worry that the A group may be:

A001
A007
A008

and B

B002
B003
B009