Auto Generated Field with Specified Format and Acting as Primary Key

[Beaudry93]

Hello everyone,

If anyone could help me with this little dilemma, it would be greatly appreciated. Here is the scenario: I would like to generate a field called "PO" (which would be the primary key) that has the following fixed format (L-0000-00000)

ei. M-3055-00001

where cmbReqType = M; txtJobNumber = 3055 & TypeID = (Automated number)

The first letter is chosen by the user (with a combo box named cmbReqType) in the main form (frmPO). The second part is automatticaly added from txtJobNumber from a different form (constant for all POs). Then, I would like the third part to keep track and increment automatically depending on the ReqType that the user will choose.

ei. if the user chooses ReqType "M", the form will automatically generate the following PO: M-3055-00001

if the user chooses ReqType "M" again, the form will automatically generate the following PO: M-3055-00002

if the user chooses ReqType "S", the form will automatically generate the following PO: S-3055-00001 (Restarting the count for the ReqType "S")

if the user rechooses ReqType "M", the form will automatically generate the following PO: M-3055-00003

and so on and so forth.


After, these PO numbers will be the primary key linking various Cost Codes and Vendors.

For better picture of the hierarchy of the database (Roman letters indicate levels):

i) JobNumber (3055)

ii) PO1 (M-3055-00001)

iii) CostCode (3055.00100.0002) - Vendor (ABC Inc.)
iii) CostCode (3055.00100.0002) - Vendor (123 Inc.)
iii) CostCode (3055.00100.0005) - Vendor (ABC Inc.)

ii) PO2 (M-3055-00002)

iii) CostCode (3055.00100.0003) - Vendor (ABC Inc.)
iii) CostCode (3055.00100.0002) - Vendor (123 Inc.)

ii) PO3 (S-3055-00001)

iii) CostCode (3055.00100.0002) - Vendor (ABC Inc.)
iii) CostCode (3055.00200.0002) - Vendor (123 Inc.)

In word, one JobNumber can have many POs (No Duplicates), one PO can have many same or different CostCode, every PO will be linked to a Vendor and finally, one Vendor can be linked to many POs

All the tables & forms are made, and I am ready to create the relationships between the tables just didn't know how to generate my POs in this specifc from. If there is a way, please help me out.

Sincerely,

Kyle

[zmbd]

1) It is best practice to NOT use such a method to generate your primary key. The primary key should normally have no other use nor meaning other than to uniquely identify the record.

Why you might ask... I'll give you a for instance:
We used to have "audit" numbers from an AS400 system
The company was bought out, now they have sampleidnumber, ticket numbers, lot number, etc... and our old audit number that used to be unique is now a "ticket number" that is quite often re-used between differing customers and tests; however, my internal database used the autonumber field as the primary key... no harm no foul, I simply changed our "audit" to handle the sampleidnumber.

2) "generate a field called " is actully spot on; however, you would do this on demand within a query.
2a) let us take your fixed format (L-1111-22222)
where cmbReqType = L; txtJobNumber = 1111 & TypeID = (Automated number = 22222)

If you have the fields:[cmbReqType]; [txtJobNumber]; [TypeID]
such
[cmbReqType] = M;
[txtJobNumber] = 3055 &
[TypeID] = (Automated number)
Thn in a calculated field within the query, you could do:
PONum: [cmbReqType] & "-" & [txtJobNumber] & "-" & [TypeID]

3) The trick is this: [TypeID] = (Automated number)

M-3055-00001
M-3055-00002
and then
S-3055-00001 (Restarting the count for the ReqType "S")
and then
M-3055-00003

We query against [cmbReqType] and find max() for the given and add one.

Do you need this?
M-3055-00001
M-3055-00002
M-3055-00003
M-3056-00001
M-3055-00004
M-3055-00005
M-3056-00002
M-3056-00003

Once again easy enough to do, query on[cmbReqType] = M; and then [txtJobNumber]= (the desired) and return the current max in [TypeID] add one to the value.

4) I more than likely would create a single PO table:
tbl_purchaseorder
[purchaseorder_pk] autonumber primary key
[purchaseorder_ReqType]
[purchaseorder_JobNumber]
[purchaseorder_TypeNumber]

Link the [purchaseorder_pk] to your other tables as needed
The fields[purchaseorder_ReqType] and [purchaseorder_JobNumber] might also need to be linked out to your other tables; however, I don't know your database schema

Please refer to:[*]> Database Normalization and Table Structures.

You will find a lot of methods on how to handle this by typing "Access" and either "Lot Numbers" or "serial numbers" into the search box next to the Bytes.Com logo above.