automatically increase number

[ashley]

I have a main form and a subform.

For each item in the main form, there is a list of steps.
How can I have a text box to automatically increase the
number everytime a step is added in the subform for that
particularly item on the main form?

Thanks.

 

[Allen Browne]

Use the Before Insert event of the subform to lookup the subform's table and
get the highest Step number used so far for this main form record:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the main form record first.
Else
strWhere = "[MyForeignKey] = " & Me.Parent.[MainID]
Me.Step = Nz(DMax("Step", "MySubformsTable", strWhere), 0) + 1
End If
End Sub

That assumes:
- the subform is bound to "MySubformsTable";
- the field in that table that relates to the main form's table is
"MyFoeignKey";
- the primary key in the main form is an AutoNumber field named "MainID";
- the field you are after is named "Step".