Next Sequence

[Matt]

How can I design my sub-form to autofill the next sequence
when using dual primary keys?

Currently, the dual primary keys get generated in the sub-
form, but I have to input the sequence for each repair
item. I would like to be able to have access generate the
next sequence in line.

Acct Seq Part Seq
generated generated manual input

Would need the 'Part Seq' to be 1,2,3,4 etc. and begin
anew for the next Acct/Seq record.

Thanks in advance for your inputs!

Matt

 

[JP]

Look at the DMax function, which can tell you the highest value in a field
given a criteria (e.g., the highest used Part Seq number for a given Acct
and Seq). You can then increment that by one.

Beware of a null return in DMax when there are no records fitting the
criteria (e.g., the first time you are adding a record for a certain
Acct/Seq). Use the Nz function to trap for that

[Part Seq] = nz(dmax(<field>, <domain>, <criteria>), 0) + 1

If this is a multi-user app in which multiple users might be trying to
simultaneously increment the Part Seq for the same Acct/Seq you may just
have to keep a table with the highest number used and lock the table while
you are retrieving that number, incrementing it, and then storing the new
highest number used.

 

[Matt]

Thanks for the feedback. I will try this once our server
comes back up.

-----Original Message-----
Look at the DMax function, which can tell you the highest value in a field
given a criteria (e.g., the highest used Part Seq number for a given Acct
and Seq). You can then increment that by one.

Beware of a null return in DMax when there are no records fitting the
criteria (e.g., the first time you are adding a record for a certain
Acct/Seq). Use the Nz function to trap for that

[Part Seq] = nz(dmax(<field>, <domain>, <criteria>), 0) + 1

If this is a multi-user app in which multiple users might be trying to
simultaneously increment the Part Seq for the same Acct/Seq you may just
have to keep a table with the highest number used and lock the table while
you are retrieving that number, incrementing it, and then storing the new
highest number used.

How can I design my sub-form to autofill the next sequence
when using dual primary keys?

Currently, the dual primary keys get generated in the sub-
form, but I have to input the sequence for each repair
item. I would like to be able to have access generate the
next sequence in line.

Acct Seq Part Seq
generated generated manual input

Would need the 'Part Seq' to be 1,2,3,4 etc. and begin
anew for the next Acct/Seq record.

Thanks in advance for your inputs!

Matt

.