AutoNumber Configuration

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

William K - 16 Feb 2004 20:07 GMT

Greetings,

I have a table with the following fields:
OrderNumber
OrderLocation
OrderID
OrderDate

I want the OrderNumber field to be an autogenerated
primary key based on the OrderLocation and OrderID fields.

So if the Order Location were in Michigan the
OrderLocation Field would contain MI. The OrderID field
would be an Auto Increment number of say "1". Therefore
the OrderNumber should be MI1.

I tried to set this up by making the field OrderLocation a
text field with a defualt value of MI. I made the OrderID
field an Autoincrement number field. Then I made the
OrderNumber field a text field with the following default
value =[OrderLocation]&[OrderID]. However, when I try to
save the table, I am told that the reference fields are
not valid.

What am I doing wrong? Thanks in advance for your help.

William K.

Reply to this Message

Jeff Boyce - 16 Feb 2004 22:28 GMT

William

You are trying to: 1) save "calculated" data -- there's no reason to do so
when you can use a query to reconstruct the concatenated value; 2) create
an "intelligent" key (this is a derogatory term); 3) put more than one fact
in one field, a "no-no" in relational database design; 4) create a default
value of the concatenated values of other fields BEFORE there's a value in
those fields.

Use a query to combine the Location info and the autonumber ID -- use the
query for your forms, reports, etc.

Signature

Good luck

Jeff Boyce
<Access MVP

Reply to this Message