Increment Key Field

[RobNauticus]

Hello, I have a table (in the midst of several one to many relationships.

The primary key is a combo of:
JobID
LotID
TaskID
TripID

The only one that will not be driven from the parent will be TripID. When the user creates a new record, the first three fields will be combo boxes, and the TripID needs to start at 1 then increment for each record with JobID, LotID, and TaskID that is created.

I know an autonumber will not work, since it will increment over the whole table not just the key fields.

Any help would be appreciated...


Rob

 

[PHV]

Provided you don't have several users creating same JobID, LotID and TaskID record at the same time you may try this expression:
1 + Nz(DMax("TripID", "yourTable", "JobID=" & Me!JobID & " AND LotID=" & Me!LotID & " AND TaskID=" & Me!TaskID), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886