Auto increment field in a sub form

[Bogzla]

Basically, we have different camera modules on which we perform a given test a number of times, giving rise to 2 tables, tblCamera which will store basic info about the module and tblTest which will hold info for each test perfomed.

The camera module has a unique ID (CameraID, the Primary key in tblCamera and foreign key in tblTest (one to many)). The primary key in tblTest will be a compound key of CameraID and an incremental number for each test performed on a given camera (TestNo). The data for the tests will need to be entered through a form, so I can build a form based on tblCamera with a subform based on tblTest (which would be in datasheet view)... What I am looking for is a way to increment TestNo in the subform, starting at 1. I have tried using

=Nz(DMax("TestNo","tblTest","CameraID = '" & [Forms]![frmCamera]![txtCameraID] & "'"),0) + 1

in the Default Value box for this field, which works to a point but has the curious effect of adding each number twice (as the default value for a new record seems to be added as soon as you start typing in the current 'last row')

I just can't seem to get my head around this one, any suggestions would be much appreciated...

Thanks,
Bogzla

 

[ShaneMan]

Basically, we have different camera modules on which we perform a given test a number of times, giving rise to 2 tables, tblCamera which will store basic info about the module and tblTest which will hold info for each test perfomed.

The camera module has a unique ID (CameraID, the Primary key in tblCamera and foreign key in tblTest (one to many)). The primary key in tblTest will be a compound key of CameraID and an incremental number for each test performed on a given camera (TestNo). The data for the tests will need to be entered through a form, so I can build a form based on tblCamera with a subform based on tblTest (which would be in datasheet view)... What I am looking for is a way to increment TestNo in the subform, starting at 1. I have tried using

=Nz(DMax("TestNo","tblTest","CameraID = '" & [Forms]![frmCamera]![txtCameraID] & "'"),0) + 1

in the Default Value box for this field, which works to a point but has the curious effect of adding each number twice (as the default value for a new record seems to be added as soon as you start typing in the current 'last row')

I just can't seem to get my head around this one, any suggestions would be much appreciated...

Thanks,
Bogzla

Hey Bogzla,

How bout if you cut you code out of the DefaultValue and placed it in [TheFirstFieldToBeFilledOut] AfterUpdate event. See if that changes things for ya.

HTH,
Shane

 

[Bogzla]

Thanks Shane, but no joy
It's frustrating, this is not a problem if the subform is in form view, as it doesn't generate the DefaultValue before updating, as it seems to in datasheet view. I'll probably end up typing it in by hand or having it in form view...

Edit: after some tinkering, I put

Me.[txtTestNo]=Nz(DMax("TestNo","tblTest","CameraID = '" & [Forms]![frmCamera]![txtCameraID] & "'"),0) + 1

in the BeforeUpdate event of the subform (where txtTestNo is the test number field on the subform), which does the trick of saving the correct number to the table, now I just need to figure out how to display it before it is saved...