When a bound form is opened, I need to change, via vba, the autonumber/primary key field from "(New)" to an actual new autonumber (1 Viewer)

[Bettany]

I open a form and it says "(New)" in the primary key field. I need to write code such that the end result is that the primary key field will change to the value of the new record, rather than the form saying "(New)" and I need to do this as early after the form opens as possible. What command/code can I use to do this. To be perfectly clear, at some point, either through data entry to a field or some other change to the form, the form will eventually get a new autonumber primary key, I need to control that process via code, and not rely on user interaction to get a new primary key. Thanks.

 

[Gasman]

Just save the record.

 

[theDBguy]

Not sure I understand your full intention, but it sounds like an unwise idea. What is the ultimate purpose of having the next autonumber value visible? Is your database a multi-user application? If so, how will you control which user should get which autonumber value?

 

[The_Doc_Man]

I need to control that process via code, and not rely on user interaction to get a new primary key.

While there are ways to force that to happen, they almost invariably involve doing something else out of order. It is my opinion that you really do NOT want to do that, despite what you may think at the moment.

Access manages autonumbers in a way that commits the number only when the record is about to be saved for the first time. If you force a record to be saved, you have now consumed an autonumber that you can never get back. If it is possible for your user to cancel that record (i.e. back out and say "I didn't mean it." AND if your autonumber is in ANY WAY involved with some kind of audit rule that requires sequential numbering, you just introduced the concept of a voided record into your process, just like a voided paper check.

 

[XPS35]

As soon as you enter a value in a field other than the autonumber, a value will be automatically assigned to the autonumber.

 

[jdraw]

As has been said, the autonumber field will be valued when the record is saved. It will be lost if the edit( record) is cancelled or deleted. It appears you have a concept of how Access is doing, or should be doing, something, and your current concept is not correct. Autonumbers are unique to records within a table and are handled by the database system. They are not necessarily sequential and should not have significance to the user.

 

[Pat Hartman]

I open a form and it says "(New)" in the primary key field. I need to write code such that the end result is that the primary key field will change to the value of the new record, rather than the form saying "(New)" and I need to do this as early after the form opens as possible. What command/code can I use to do this. To be perfectly clear, at some point, either through data entry to a field or some other change to the form, the form will eventually get a new autonumber primary key, I need to control that process via code, and not rely on user interaction to get a new primary key. Thanks.

If the BE is Jet/ACE, the autonumber will be generated as soon as the user types the first character into the record. If the BE is SQL Server or other RDBMS, the identity column is not generated until the record is sent to the server to be saved.

Under no condition should you ever save an empty record just to generate an autonumber.

If you want control of the PK, then do not use an autonumber. Generate your own sequence number.

 

[gemma-the-husky]

If you want to produce your own sequence, then you can set the next number in code in the current event

If Me.newrecord then IID=whatever but you are likely to end up with blank records you don't really need.

 

[Mike Krailo]

I'm still interested in hearing the reasoning behind wanting to do this. When I want to control an ID or Number for some reason, I just add a column for that exact specific purpose and although I could use it as a primary key, I choose to use a generic primary key instead (auto number) and do as Pat is suggesting and never saving empty records. Learn how to use the Before Update event with Cancel option.

 

[Pat Hartman]

When you use your own code to generate a unique ID, then do it as the LAST instruction in the form's BeforeUpdate event. That way you at least have a shot at not burning a number if the user elects to not save the record. Also, depending on how busy your environment is, you always run the risk of generating a duplicate number if two people are in the process of saving at the same time. So, the first person who actually saves, "wins" and the other(s) "lose" and their insert is rejected due to a duplicate unique ID. You need to trap this error and either loop in the program code to generate a new number or force the user to press save again to go through the normal process of saving.

If you generate the unique ID in the BeforeInsert event - which is the earliest rational place to do it, your risk of having to burn a number increases dramatically. Another "never" is, you never want to dirty a record before the user does. This is the path to saving incomplete records unless your validation code in the form's Beforeupdate event is sound and ensures that all required data is present and valid.

We don't have a clue what business rule is driving this request so we can't make actual suggestions on how you might accomplish your goal. If you decide that it is more important that the user see the new ID before he even starts entering data, you would need to generate the ID in the form's Current event whenever the form moves to a "new" record. This is really bad design because at this point you don't know what your user's intention even is. Does he actually want to add a new record? I would never generate the ID in this event.

 

[Gasman]

Pat, could you not use Before_Insert for that?
Surely that runs after the Update has been validated?
Just curious?

 

[Pat Hartman]

Pat, could you not use Before_Insert for that?
Surely that runs after the Update has been validated?

BeforeInsert runs as soon as a single character it typed. The Dirty event runs either just before or just after. But, using the on Dirty event means that you would need to test for a new record. The BeforeInsert only runs when a new record is dirtied for the first time.

Have fun with this:

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk