Subform: Incrementing a Line Item Field 1

[scausey]

Hi,

I have a form consisting of document header informaiton and a subform consisting of line item information. I would like to increment the line item field automatically for each line item on the subform. I would like to reset the counter for each new record in the main form. Here is what I am trying to accomplish.
Main Form Subform Subform
Document # Line Duty Rate
12345 1 .015
2 .025
2345 1 .001
2 .015

I am having some problems dealing with the order of events. I have tried several approaches without finding an optimal solution. Here is the best that I could come up with:

In the Main Form:

Private Sub Line_Items_Enter()
'Set the first record to 1 on the subform.
Me!Line_Items![EntryLineItem] = 1
End Sub

In the Subform:

Private Sub EntryLineItem_GotFocus()
Static counter As Integer
'For some reason, the first record does not get focused
counter = counter + 1
If counter = 1 Then 'First Record is set by main form.
counter = counter + 1
End If
Me!EntryLineItem = counter

End Sub


Unfortunately, the counter increments every time the user clicks on the field. Does anyone have any suggestions on how to get a subform field to automatically increment by one, but start over for each record in the main form without getting this side effect? I do not have a clue as to how to proceed.

Thank you for your time and help.

 

[rafe]

As a test I put a DCount() function in the Form_AfterInsert event of the subform. it worked OK but I didn't test extensively. so beware

Private Sub Form_AfterInsert()
    Me.Line = DCount("[Line]", "SubTable", "[Doc #]=" & Me.DocNo)
End Sub

can explain further if needed

 

[rafe]

that's a big fat oops! what happens if you delete & then add? duplicate line#... bad rafe! bad rafe! test more!

change that DCount to a DMax and trap the case where the max is null (1st time thru) with a Nz() function.

Me.Line = Nz(DMax(same stuff)+1,1)

still beware... i'm not having my best day.