Create Sequentiaql Numbering on a Subform

[cschmid]

Need to know how to code a field to autonumber on a subform while I am inputting. I have a field that I want to start at 1 each time I go to enter a record. I want the field to sequentially increment from there and then I will save the record. When I go to enter a new record the field will start back at 1 and increase again. The subform stores part numbers and descriptions based on the PK known as the Catalog Number on the main form. Assistance with this is much appreciated.

 

[tsumrall]

The seq number in your table behind your subform needs to be a number not autonumber.

On the subform make it's default value

=dmax("[partnumber]", "subformpartnumber","[catalog number]=" & me.catalog number) + 1


Catalog number is likely what you use to link the main form and the subform (should be unique). If it's not a number then use

=dmax("[seqnumber]", "subformtable","[mainformnumber]= '" & me.mainformnumber & "'&quot + 1

The first subform record you will need to enter 1 but after that it should number them for you.

You can check the first one with iif and have it return a 1 but that's extra