Incremental number

[Silvio]

I have a main form with a subform the subform is used to create new records
(One-to-Many relation). What I am trying to do is to fill a field [Cycle]
with the next incremental record number. This is what I have entered in
Default Value of the [Cycle] filed of the subform:

=DMax("Cycle","tblOxygen","[Calibration_ID]=Forms![tblCalibration]![CalibrationID]")+1

The problem is that it does not work properly, meaning that it will enter a
couple times the same (e.g. 2, 2, 3, 3 etc) and I could have created already
5 record and the system will insert a 3 instead. The table that stores the
data for the subform (tblOxygen) will have many record with different
CalibrationID therefore, it is important to count and increment only within
records with identical CalibrationID (e.g. If I have 2 records with
CalibrationID = 15 then the new 3rd record will be cycle 3 and so on.)

Any idea of what I am missing out?

 

[John W. Vinson]

I have a main form with a subform the subform is used to create new records
(One-to-Many relation). What I am trying to do is to fill a field [Cycle]
with the next incremental record number. This is what I have entered in
Default Value of the [Cycle] filed of the subform:

=DMax("Cycle","tblOxygen","[Calibration_ID]=Forms![tblCalibration]![CalibrationID]")+1

The problem is that it does not work properly, meaning that it will enter a
couple times the same (e.g. 2, 2, 3, 3 etc) and I could have created already
5 record and the system will insert a 3 instead. The table that stores the
data for the subform (tblOxygen) will have many record with different
CalibrationID therefore, it is important to count and increment only within
records with identical CalibrationID (e.g. If I have 2 records with
CalibrationID = 15 then the new 3rd record will be cycle 3 and so on.)

Any idea of what I am missing out?

I'd suggest a different approach. It's likely that the DefaultValue property
isn't being refreshed when the data changes.

You can instead use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Cycle = NZ(DMax("[Cycle]", "[tblOxygen]", _
"[CalibrationID] = " & Me!CalibrationID)) + 1
End Sub

 

[Silvio]

Thanks John, this works just fine.

I have a main form with a subform the subform is used to create new records
(One-to-Many relation). What I am trying to do is to fill a field [Cycle]
with the next incremental record number. This is what I have entered in
Default Value of the [Cycle] filed of the subform:

=DMax("Cycle","tblOxygen","[Calibration_ID]=Forms![tblCalibration]![CalibrationID]")+1

The problem is that it does not work properly, meaning that it will enter a
couple times the same (e.g. 2, 2, 3, 3 etc) and I could have created already
5 record and the system will insert a 3 instead. The table that stores the
data for the subform (tblOxygen) will have many record with different
CalibrationID therefore, it is important to count and increment only within
records with identical CalibrationID (e.g. If I have 2 records with
CalibrationID = 15 then the new 3rd record will be cycle 3 and so on.)

Any idea of what I am missing out?

I'd suggest a different approach. It's likely that the DefaultValue property
isn't being refreshed when the data changes.

You can instead use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!Cycle = NZ(DMax("[Cycle]", "[tblOxygen]", _
"[CalibrationID] = " & Me!CalibrationID)) + 1
End Sub