Show highest.

[rob p]

I have a form / subform that are called frmmain and frmsub. I have a table
not related to these forms with a field for checknumber. Is there a way to
put an unbound text field on the main form showing the hightest checknumber.
(I would be displaying it so I would know the last number used.) Relatedly,
is there a way to have a field on the main form increment by one (for each
record) the checknumber from the unbound text field??
thanks.

 

[Allen Browne]

You could display the hidhgest value in the "checknumber" field of the table
"MyTable" by setting the Control Source of your text box to:
=DMax("checknumber", "MyTable")

To automatically assign the next available check number just before the
record is saved, use this code in the BeforeUpdate event procedure of your
form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.checknumber = Nz(DMax("checknumber", "MyTable"), 0) + 1
End If
End Sub

Note that if two users tried to save records at the same time, they might
get the same number, and so the latter one may not save. This is less likely
by leaving the assignment to the last possible moment - hence the suggestion
to use Form_BeforeUpdate rather than Form_BeforeInsert.