Gary wrote:
Thanks for the advice. I think I will simply go with the number and
remove the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName",
"TableName")+1 to give me the next sequential number.
Basically i have the frm_main which is the viewing of all records. The
addition of new records is done in frm_dataentry.
In this frm_dataentry, I added that Dmax formula (in the JobNo field
- in the Controlsource section). It does give me the next sequential
number when I go into the data entry form, but when I go back to the
main form (after completing the dataentry form with info) to see if
it was saved, it did save all the data previously entered in the
dataentry form but not the JobNo - that was given to me automatically
while in the data entry form. That field was simply blank.
You can't use an expression as a ControlSouce if you want the value saved. The
only time a control's value is saved to the table is when it has a ControlSource
that is the name of a field (and nothing else).
You need to set the ControlSource to your field and then in the BeforeUpdate
event of your Form have code...
If Nz(Me.JobNo, 0) = 0 Then
Me.JobNo = Nz(DMax("JobNo", "TableName"), 0) + 1
End If
You could (in theory) use the DMax() in your DefaultValue property or assign it
in a different form event, but those options do not work reliably in a
Multi-User environment. BeforeUpdate is the only event where the calculation of
the next number and the saving of the record happen almost at the same time so
that there is (almost) no chance of two users grabbing the same value.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com