Numbering secondary primary keys

[harix]

I have a table for workorders(WO). The workorder ID is unique. Each WO can have one or more labor/repair operations (e.g. oil change, clutch, radiator etc.) which I have named WOItemNo. I would like the tech to be able to enter multiple repair items and have them start at item 1 (by default value of 1) and any additional entries to progress for each entry e.g. 2,3,4 etc. on each WO.

How can I get the items to be progressive. All the entries are made on a form. Or should I have a seperate table with the itemized records connected to the WO table? Again how do I get the item # to go from 1,2,3,4,5 etc. on each WO?

Thanks!

 

[RichMorrison]

Harry,

We have had similar questions but I can't remember any keywords for a search.

Briefly;
when you want to add the next WO Item, do a DMax to return the maximum WO Item number for a given Work Order.

MaxWO = DMax("WO_ItemNum","YourWO_Table","YourWO_Table.[WorkOrder]=" & Forms![YourForm]![UserEnteredWO_Num] )

NewWO = MaxWO + 1

HTH,
RichM

 

[Pat Hartman]

Yes you should have two tables. One for work orders with WO as the primary key and the second to hold the operations. The WO operation needs a two-field primary key, WO and the sequence number. Rich's suggestion tells you how to assign the next sequence number.