Creating a field to automatically assign the next number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way I can create a field that will automatically generate a
"ordernumber", BUT it cant be an autonumber really since i want it that lets
say i will click on a drop down list of customers and based on what i have
preset as this customers prefix it will use that and add the next number for
that customer.

For example. A company called Texas Pipes, so i set their customer prefix to
be "TP", so now when i choose to create an order for that customer it will
create a number stating TP002. But I dont want it to get messed up if lets
say i then do another customer, so the next number will be independent based
on the customer.
The Order for m i am using is a modifed northwind databse.

thankx
 
Skulmat,

You should not include the Customer Prefix in the OrderNumber. You can
use concatenation, within a calculated field in a query, or in a
calculated control on a form or report, to display as TP002 whenever it
needs to be presented in that configuration. Other than that, the
Customer Prefix is in the Customers table, and the OrderNumber is in the
orders table and is a Number data type.

On the orders entry form, use a VBA procedure on an appropriate event,
to do the equivalent of this...
Me.OrderNumber = DMax("[OrderNumber]","YourTable","[CustomerID]=" &
Me.CustomerID)+1

Depending on the details of your form setup, "appropriate event" might
be the Before Insert event of the form, or the Before Update evetn of
the form, or the After Update event of CustomerID.
 
Back
Top