Solved Autonumbering in tables

[Teri Bridges]

I have the following code written so that when a user does not want to save the data they entered they can opt not to save changes. I noticed that the autonumbering generates a number but does not save it.

So if the user enters record # 4 and they decide not to save it the # 4 line item is deleted and the next record entry is 5. i was wondering if there is a way for that not to happen. If they decided not to save entry #4 then # 4 would be the next available entry.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim answer As Integer
        answer = MsgBox("Save Changes?", vbQuestion + vbYesNo + vbDefaultButton2, "Change Prompt")
    If answer = vbYes Then
        DoCmd.Save
    Else
        Me.Undo
    End If
End Sub

 

[theDBguy]

Yes, that's the normal behavior. Is this for a multi or single user database?

 

[tvanstiphout]

If you want numbers to be sequential and without gaps, you should not use an AutoNumber.
But even if you use another technique, like DMax+1, what if the user deletes a previous record? Or is that not allowed and can patently never happen? It is VERY unusual to have that requirement. If you think you do, can you tell us more about it?

 

[Teri Bridges]

Yes, that's the normal behavior. Is this for a multi or single user database?

Multi User, well if I ever get done. There is that.

 

[Teri Bridges]

If you want numbers to be sequential and without gaps, you should not use an AutoNumber.
But even if you use another technique, like DMax+1, what if the user deletes a previous record? Or is that not allowed and can patently never happen? It is VERY unusual to have that requirement. If you think you do, can you tell us more about it?

I was just hoping to not have the record number post if the record was not saved. I do not have to have sequential numbers. I realize there will be gaps if records are deleted.

 

[The_Doc_Man]

The problem with autonumbers is that once you "consume" one, it is gone for good. However, as long as there is no accounting reason for you to have contiguous numbering, autonumbers would work fine for a long time. With multi-user databases, you have to be careful because any number method other than autonumber runs a risk of number duplication and gets a LOT more complex. Further, if a record can ever be deleted, you would have gaps anyway and you would play havoc to correct discontiguous numbering in that case. Therefore, my advice is to live with autonumber as the best automated way to generate unique numbers for a table's primary key.

 

[KitaYama]

It is VERY unusual to have that requirement.

Why do you think it's VERY unusual?
There are many cases that gaps may be trouble makers. At least in my profession, 95% of what we do either in CAD or production line can not have gaps and in the rest 5%, they are not welcomed.
When you create an assembly and add several instances of the same parts, CAD software gives them an ID and this ID can be used in a macro for creating BOM in drawings. If there's a gap in the used parts, the number of the component in BOM will differ with the balloon on the drawing that's stuck to it. How will you explain that to the people on the shop floor who read your drawing?

There are a lot of cases that invoice numbers can not have gaps for reporting cases.
Just search Sequential Number in this forum or google it to see how many times users have asked how to roll their own sequential number to get ride of the gaps. That number of the result tells you it's not unusual to prevent gaps.

 

[The_Doc_Man]

@tvanstiphout -

Contiguous numbering is commonly required for fiduciary institutions as an accounting artifice to verify that no data has been deleted. Also, when you are doing doctoral research, you need contiguous numbering on your experiments so that your examining committee can verify and account for what you did. (Sometimes.) In working for the U.S. Navy, there were times where for security reasons I had to sequentially number certain system actions in my log files because the auditors were literal-minded and never learned how to skip numbers. (At least, that is what I was told...)

 

[Teri Bridges]

I think I bit off more than I can Chew.

 

[KitaYama]

I think I bit off more than I can Chew.

Watch this video.
It explains how to do what you want.
Let the auto number field be what it is for, and roll your own.

 

[ebs17]

answer = MsgBox("Save Changes?" ....

When my users write something, they want it to be saved; they have the necessary clarity in their minds about what they are doing.
They would be very annoyed if they were asked whether their entries should be put in the bin, and they would also be annoyed about this interruption in the flow of work. This raises the question of whether such a query, which creates the problem, is even necessary.

As an alternative to sequential numbers, you can also use an unbound form for data entry. With a (well-considered) click on the save button, you would execute an append query to the table. The auto number is only assigned when the data record is saved.

 

[The_Doc_Man]

Valid point, Eberhard. The autonumber only updates when you need to store something in the record, and that moment (needing to store something in a record) depends on forms being bound vs. unbound. However, for persons uncertain of their ability for handling more complex situations, the need to manually do what a bound form automatically does for you can be daunting in its own right.

@Teri Bridges - the Access interface is an environment of trade-offs. You can do something the easy way or the hard way, and each has both advantages and disadvantages. Sometimes it is lop-sided. Sometimes the alternatives are clear-cut. As long as you do not require continuous numbering with no gaps, using an autonumber field as the prime key is safe and easy. But your requirements must be your guide here. Ask yourself what you need, make your choice, and don't look back.

 

[GPGeorge]

I think I bit off more than I can Chew.

You will learn to chew over time, so don't give up trying.

 

[Pat Hartman]

@Teri Bridges When you generate your own sequence numbers in a multi-user environment, you always have to worry about generating a duplicate. One way to minimize the risk is rather than using the BeforeInsert event to generate the number, use the form's BeforeUpdate event (you have to check the Me.NewRecord property and only generate for new records) and make the calculation using dMax() the last line of code after all your validation and before the end of the procedure. This minimizes the risk of a duplicate because when you use the BeforeInsert event, the number is generated as soon as the user types the first character into the form to dirty it. This can potentially leave a couple of minutes between when you generate the sequence number and when it gets saved and therefore there is more risk of someone starting after you but saving before you. I disagree with Richards proposed solution of saving immediately for two reasons. The downside to using the BeforeUpdate event is the user won't see the sequence number before the record is saved. That isn't generally important but if it is in your case, use a save button to save the record but not advance to a new record or leave the form.
1. You are forced to save the record with no other data filled in so it is essentially an "empty" record and this also interferes with any form validation you have in the BeforeUpdate event.
2. It still is not 100%.

That means that to be truly safe you still need to trap the duplicate error and either ask the user to resave so you can generate a new number or add a loop that keeps generating new numbers until one finally succeeds. I always make these loops finite just in case there is a problem. So, the loop makes 5 tries and then stops and gives the user an error message.

Another alternative which goes part way is to use a separate table to encueue the seq number. In the BeforeInsert event run an append query to append a row to the sequence number table. Have the dMax() in the append query rather than in VBA. Also, add a field in the sequence number table to include the autonumber of the record you just started to add. That allows you, in the next statement to use a dLookup() on the autonumber to retrieve the generated seq num. This is also not guaranteed to work in a very busy environment. The only guaranteed method is the code loop. This method also runs the risk of the current record never being saved so I would never use it.

 

[Teri Bridges]

Valid point, Eberhard. The autonumber only updates when you need to store something in the record, and that moment (needing to store something in a record) depends on forms being bound vs. unbound. However, for persons uncertain of their ability for handling more complex situations, the need to manually do what a bound form automatically does for you can be daunting in its own right.

@Teri Bridges - the Access interface is an environment of trade-offs. You can do something the easy way or the hard way, and each has both advantages and disadvantages. Sometimes it is lop-sided. Sometimes the alternatives are clear-cut. As long as you do not require continuous numbering with no gaps, using an autonumber field as the prime key is safe and easy. But your requirements must be your guide here. Ask yourself what you need, make your choice, and don't look back.

Yes, I am going to use the auto numbering. My skills are not apt for other options at this point. I thank you all for your guidance.

 

[Teri Bridges]

@Teri Bridges When you generate your own sequence numbers in a multi-user environment, you always have to worry about generating a duplicate. One way to minimize the risk is rather than using the BeforeInsert event to generate the number, use the form's BeforeUpdate event (you have to check the Me.NewRecord property and only generate for new records) and make the calculation using dMax() the last line of code after all your validation and before the end of the procedure. This minimizes the risk of a duplicate because when you use the BeforeInsert event, the number is generated as soon as the user types the first character into the form to dirty it. This can potentially leave a couple of minutes between when you generate the sequence number and when it gets saved and therefore there is more risk of someone starting after you but saving before you. I disagree with Richards proposed solution of saving immediately for two reasons. The downside to using the BeforeUpdate event is the user won't see the sequence number before the record is saved. That isn't generally important but if it is in your case, use a save button to save the record but not advance to a new record or leave the form.
1. You are forced to save the record with no other data filled in so it is essentially an "empty" record and this also interferes with any form validation you have in the BeforeUpdate event.
2. It still is not 100%.

That means that to be truly safe you still need to trap the duplicate error and either ask the user to resave so you can generate a new number or add a loop that keeps generating new numbers until one finally succeeds. I always make these loops finite just in case there is a problem. So, the loop makes 5 tries and then stops and gives the user an error message.

Another alternative which goes part way is to use a separate table to encueue the seq number. In the BeforeInsert event run an append query to append a row to the sequence number table. Have the dMax() in the append query rather than in VBA. Also, add a field in the sequence number table to include the autonumber of the record you just started to add. That allows you, in the next statement to use a dLookup() on the autonumber to retrieve the generated seq num. This is also not guaranteed to work in a very busy environment. The only guaranteed method is the code loop. This method also runs the risk of the current record never being saved so I would never use it.

Thank you for taking the time to explain.

 

[gemma-the-husky]

Where I want an intact sequence I tend to use a "seed" value from my own table, but you can still get the same problem.
If the next record seed is record No 26, say

User A gets a new record, and get's allocated No 26, and the seed is updated to 27
User B gets new record, and the seed is updated again to 28,

If Users A discards his record, you have still burnt no 26. (which is what happens with an autonumber)

The only way you avoid this is to delay reading the new record ID until you accept the record.
Then you read 26 (or a higher number), and you won't delete this record and waste the number, as you read it at the end of accept process. (which is basically what Pat said in #14.)

The other advantage is that you can change the next number seed to any particular value. If you need a block of 50 records for a purpose, you can change your next record seed to 100, say, and then you know that records 27 to 99 can be used for other purposes. It gives you more control if you need it.

I don't use dmax as I prefer the control offered by a "seed" mechanism.

 

[Pat Hartman]

In #14, I suggested generating the sequence at the last possible moment before the record was saved. That means using the last line of the BeforeUpdate event because that is the last line of code that runs before the record is saved. This minimizes the problem and is good enough for "government" work as we say. But generating in the BeforeInsert event, is way too early. It is too easy for a user to be distracted and minutes could easily elapse between typing the first character and actually getting to the save part of the process.

In environments where people are not doing heads down data entry, there is usually very little chance of generating a duplicate. You just need to know that if something can happen, it will at some point. So a compromise is to:
1. Generate the seq as the last statement of the BeforeUpdate event (don't forget to put it inside an If statement that checks the NewRecord property)
2. Add code to the on error event that recognizes the duplicate error code and display a meaningful message to the user that tells him to press the save button again.