Where and how to code your own Autonumbers? (97 / SR-2)

Topic

New Reply

I’m starting to complete the design of a new database. Recently I was told if I’m going to be archiving I shouldn’t be using autonumbers. So I have since changed the autonumbers to number long integers. My questions are:
1)Where do I put the code to generate the next autonumber when a new record is entered?

I have tried placing this: =DMax(“tblPatient”, “PatientID”)+1 in the Default value under PatientID in table design view but get an error. I guess you can’t use DMax for Default value in table properties?

Or should I just place that code under the field’s default value in form design and also in the forms before update. If so do I leave the PatientID’s default value property in the table to default to zero?

2)If I can’t set the autonumbers to increment myself in the table properties, when I start to archive and restore archived records by the use of Append queries how will the autonumbers be generated if the code for them is only in the forms?

I have searched everywhere for examples but only found a couple very vague answers. Would appreciate any incite or a kick in the right direction of some past threads.
Thank you
Jols

Reply | Quote

Replies

I attach it to the OnClick Event of a button on a form. The user clicks this button to add a new record. Try:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

    DoCmd.GoToRecord , , acNewRec
    [Forms]![your formname]![PatientID] = DMax("[PatientID]", "tblPatient") + 1

Exit_cmdAdd_Click:
    Exit Sub

Err_cmdAdd_Click:
    MsgBox Err.Description
    Resume Exit_cmdAdd_Click
    
End Sub

Reply | Quote

I have gotten it to work in a somewhat similar fashion as you did except I put the code in the default value of my control and in the before update of the form. The problem I’m having with that is if you have no patients entered yet. You can enter the first patient no problem but when you go to enter the second patient…don’t you get a PK must contain a unique value. For some reason you can’t add 1 to 0??

Thanks for the reply,
Jols

Reply | Quote

I used the code because I was importing historical data from another database and the ID could not be reassigned, but new records needed to be added into the same table with incremental IDs thereafter. I would expect that adding a one to zero would work, so that seems unusual. However, given the other responses, it seems that you are best served with AutoNumber.

Reply | Quote

I’d make the point that using the Access generated autonumber is the way to go. I can’t imagine why it would prove problematic in any archiving scheme.

Was there a particular reason why using Access’ autonumber seemed like a bad idea? (not a flame, just curious if there was a buisness/other reason)

Reply | Quote

Well I have been doing alot of reading in this forum lately and keep seeing how autonumbers get reset to the next highest number in the table. So I was afraid that say I added a new record, PatientID =5 then archive that record. Later I compact and the autonumbers get reset to start at the autonumber 5 because that is the next highest number. So then if I went back in and added a new record to the active table it would give it a Autonumber of 5. I would be screwed if I needed to restore that archived record with PatientID = 5 because it would give a duplication error.
But since then I found a post by Mark Liquorman that stated:

“If you never archive the most recent active record (that is, with the highest autonumber) then even a sequential autonumber should not duplicate. Even after a compact, the next autonumber assigned will be 1 more than the current highest number.”

So I guess I don’t really need to worry about archiving using autonumbers. Am I right? Anyone have any suggestions before I commit to using autonumbers?

Thanks,
Jols

Reply | Quote

Good reason! Yup, Mark’s advice seems sage.

Reply | Quote

Check out the following threads:
Thread 1
Thread 2

My impression from these threads and some first-hand experience is that you will have problems with archiving if you use an incremental autonumber, but your chances of problems if you use random autonumbers will be somewhat worse than your chances of winning the Powerball lottery.

An alternative that Charlotte suggested is to use a “master” table with your autonumber field (random or incremental) and an “Archived?” Yes/No field (the latter for convenience only) and not much else. Then relate this table to your “active” and “archive” tables through the autonumbered master field I.e., when you create a new record, create it in the master table (generating a new autonumber). Then create the new record in the active table and copy this autonumber value into the link field (which itself is not an autonumber field).

Tom

Reply | Quote

Another way of doing this is to create a separate table with the next record number to be used. We did this with SQL Server tables where we didn’t originally have an autonumber field available. If your table has a large number of records, the DMax function can take quite a while to run.

As to the archiving issue, if you allow people to delete records, but want to leave the archived record in its table then the resetting of the autonumber field during the compact (if the last record or records have been deleted) can be a problem. One solution is to never allow a record to be actually deleted. In that case we put a “deleted” field in the table and only let users see the records that are not deleted. If the table gets really large you can go in and occasionally delete records or move them to an archive table.

Hope this helps!

Reply | Quote