AutoNumber field that increments problem

[ZiiX]

I have a 'testID' field in a table I've set to autonumber. However, when someone starts a new test but doesnt save and that number isnt really used it skips... i.e.

testID is recorded as '1' for test 1

testID is started for '2' but they don't save.

testID is recordend next as '3' for test 2 because this one is saved.

I want it to read and save the next 'available' value.

How do I set it to use ONLY the next available value? Because I dont want there to be any phantom test numbers assuming people will always start filling in a form but may start over and not save etc...


Also, how do I reset the first entry to testID 1? I deleted all of the records but it starts the first value as 90?

Thanks for your time.
ZiiX aka Shane

 

[sha76]

Shane,

The autonumber is assigned when a new record is created, not when it's saved. It should not be used in a situation such as you describe. Personally, when I've needed something similar then I've put in code the On_Current event of my form to query for the max value of the field and increment it (NB I've not tested this on multi-user dbs, you'd either have to use strict record locking, or change the number if it errored due to already being saved by someone else).

 

[ZiiX]

Thanks for the reply.

So what would be the code to assign the Primary Key "testID" to do that? This is multi-user only in the sense of viewing not adding entries. The lab tech adds them.

Thanks.
Shane

 

[sha76]

Private Sub Form_Current()

If IsNull(Me.txtTestID) Then
Me.txtTestID = DMax("[Field1]", "[TableB]") + 1
End If

End Sub