automatically increase number

  • Thread starter Thread starter ashley
  • Start date Start date
A

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.
 
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".
 
Back
Top