Form/Subform and record entry

[VBAhole22]

I am having some difficulty trying to figure out all of the events associated with a user skipping from a main form to a subform. My main and sub forms are in a one to many relationship, respectively. The main form has a user id that is autonumber. Now this number doesn't get assigned until a user actually submits the record, right? So if a user were to tab into, or click into, the subform then the record in the main form would get submitted and the the autonumber assigned? What if I wanted validation on the fields that the user filled in on the main form? What event would I use to check the status of the fields on the main form? How could I then pop up a message asking if the user knew that these fields were wrong and could they correct them?

I guess I am confused about all of the possible events going on. Also when a user is finished with the main and sub forms and they want to go on to another record on the main form what is a good event to attach code that will send up a message box telling them what autonumber they got assigned? I know it will come up in a field in my form but I want to be able to tell the user too. Any help would be greatly appreciated.

 

[Rich]

Use the Before Update event to validate, the main form record will be saved as soon as data is entered into the subform

 

[VBAhole22]

get id

I have a query that pulls the autonumber by sorting and finding the highest number. If I want to return this number to the user in a message box the BeforeUpdate event is too soon because this number has not been generated yet. What event comes next?

 

[Noreene Patrick]

I think the next event is On Dirty. Because if you have moved into the control then the autonumber generates then.

Correct me someone, if I am wrong.

 

[VBAhole22]

I did a little more snooping and isolated the problem more. What happens is when I move out of the subform and back into the main form a before_update event is being fired on the main form. Well this is where I have a lot of code to validate entries on incoming records. I build a list of things wrong with the entries and then show them to the user in a message box that asks if they want to continue. If they click cancel all is well. If they click ok then the write conflict message is raised. Now I need the code on the Before Update to validate but I don't understand why that event gets fired when tabbing or clicking back into the main form. Should my validation code be moved to some other event?

 

[Rich]

What other code do you have in the BeforeUpdate and Form_Current events?