Event on Form that Fires When AutoNumber Field Is Populated

[BikeToWork]

Is there a particular event that fires when the autonumber field on a form is populated with a number? How would I trap this? I have a form where I use the autonumber ID to create a job number for the records, which is basically zeros followed by the AutoID number, so if the AutoID is 25 then the job number is 00000025. I have tried all the different events on the AutoNum text box and the form's events but none of them fires when the autonumber field is populated. Before or After Update don't work on the AutoNum text box. The form's BeforeUpdate and AfterUpdate work for creating the JobNumber but they don't fire until the user moves to another record. Before Insert on the form does not work because it occurs before the AutoNumber field is populated. Any help with this is greatly appreciated.

[Seth Schrock]

I don't believe that there is an event specifically for this. What you can do is use the form's BeforeUpdate or AfterUpdate events and just test if it is on a new record using the Me.NewRecord property which returns true or false. You could then run your code inside the if/then statement.

[BikeToWork]

Thanks for the reply, Seth. The problem with the form's before or after update events is that they don't fire until the user moves onto a different record. What I really need is to fire the JobNumber function right after an AutoNumber ID value is generated in the form, if that is even possible. I don't understand why the AutoNum textbox AfterUpdate event doesn't fire when it changes from null to a number.

[Seth Schrock]

The reason the after update event of the auto number field doesn't fire is that you didn't enter the field and edit the data. Even setting the value in VBA won't trigger a control's after update event.

Assuming that the tables are Access tables and not linked to another data source through ODBC, the the autonumber field will get its value as soon as the first control is edited. So you could put it in a control's after update event.

[BikeToWork]

Thanks again, Seth. The problem with the after update event for other fields on the form firing the jobnumber function is that I would have to fire the event for every control on the form, because I have no way of telling what order users will edit the form in or which fields they will update in what order.

Ideally, there would be some way to anticipate what the autonumber is before it populates on the form but there is no guarantee that it will be sequentially one greater than Max(AutoNumber) .

[Seth Schrock]

You might try doing this in a query since this is sort of a calculated field. Since Access queries can use VBA functions, you would still be able to use your existing code.

[BikeToWork]

Thanks again for the advice, Seth. What I ended up doing is a little klugey but seems to work properly. On the form's On Dirty event, I put in code that will first set the job number text box control to "00000000." When that value is inserted, an autonumber for the record is generated and then I run the JobNumber function to update the correct value into the Job Number field.