I have created a form to input data into a table. The ID blank shows the next number automatically each time I click or type data in other blanks of the form. But it seems that the ID change is irreversible and goes up. I mean even if I clear the whole form or I cancel the data to be inserted into the table, next time that I open the form, the ID blank will show the next number. As a result, there will be missing ID numbers in the table. How can I make the ID blank to show the next consecutive number only if the previous one is recorded?
The ID number in Access is only there for unique reference purposes. Based upon that, it should not make any difference whether it skips numbers or not. If you are wanting to have a one up number to use for a different purpose then you will need to create special code to do this. Something like this
Note that the hack Alan gave you a link to tells you to place the code in the Form_BeforeInsert event. As the author of the code stated, the number will be assigned the instant that a single character is entered into any Control on the Form.
This is fine, as long as the database is free-standing, i.e. has a single-user on one machine. But if this is a split database, with multiple-users, you run the chance of having two or more Records with the same number!
The problem is that the number is assigned, as noted, when a new Record is started. With multiple-users, you run the chance that UserA will start a newRecord, be assigned their number, and before they complete/save their Record, UserB will start a new Record, and be assigned the same number!
The way to avoid this, if yours is multi-user app, is to run the code to generate the number at the last possible nanosecond before saving the Record. To do this place the code in the Form_BeforeUpdate event.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
MyCounter = Nz(DMax("MyCounter", "CustomerTabel")) + 1
End If
End Sub
Done in this fashion, I've never seen a number duplicated.
I would also like to add to linq's suggestion two things.
1. Make the counter the primary key and get rid of the autonumber or add a unique index on the counter field which will prevent a duplicate from actually being saved.
2. Add code that traps the duplicate message and increments the counter to try again. You can write simpler code that is not recursive by simply giving the users a message and asking them to save the record again. Unless you have an extremely busy application, you are very unlikely to run into a "duplicate" counter but you have to be prepared to handle the situation gracefully should it arise.