Increment a field in subform, not AutoNumber

[meadows43]

Hello,

I have a subform that has a field that I would like to increment starting with 1 for each new record entered in the subform. The field is one of two fields that is part of the primary key. I can't use an autonumber because I need the value in that field to start at 1 for each new record in the main form.

Can anyone please help?

Thanks,
CM

 

[WayneRyan]

CM,

Look in help for the DCount function. It will let you see how
many "child" records there are. Increment it by 1 and assign it
to your field using the BeforeInsert event.

hth,
Wayne

 

[Rich]

In the Current event of your subform
Private Sub Form_Current()
Me.txtNum = Form.CurrentRecord
End Sub

 

[meadows43]

Thank you both for your help. I tried Wayne's suggestion using the Code Builder, but must have been doing something wrong because it did not recognize the field. I used Rich's second line of code in the subprocedure replacing txtNum with my subform field to be incremented, and that worked.

Thanks again,
CM

 

[Pat Hartman]

Unless you do not allow deletes, I would suggest using DMax() rather than DCount(). If you add 1, 2, and 3 and then delete 1, DCount() will result in a value of 2 + 1 = 3 which is already used. DMax() will result in a value of 3 + 1 = 4 which will not produce a duplicate.

 

[meadows43]

I moved this line of code:

Me.[Step Num] = Form.CurrentRecord

from the OnCurrent event to the BeforeInsert event of the subform because having it called on the OnCurrent event was causing a "Index or Primary Key cannot contain a Null value" error for the main form's primary key, which was an autonumber. After making the switch, the subform field increments on each new entry and I do not receive the error.

Thanks,
CM