Incremental number


S

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?
 
Ad

Advertisements

J

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
 
Ad

Advertisements

S

Silvio

Thanks John, this works just fine.

John W. Vinson said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top