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

[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.

 

[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.

 

[Gasman]

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