Thanks John, this works just fine.
"John W. Vinson" wrote:
[Quoted Text] > On Wed, 3 Dec 2008 13:00:25 -0800, Silvio <Silvio[ at ]discussions.microsoft.com>
> wrote:
>
> >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
>
> --
>
> John W. Vinson [MVP]
>
|