Event that fires when moving from record to the next on a subform

Hello Experts:

I have a form that has a subform on it.  The subform is set to a data sheet.  Is there an even that fires when moving from one record to the next?  I want to make sure all of the required data is entered before the user adds a new record.


LVL 15
Juan OcasioApplication DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The Current event fires every time you enter a new record but that is NOT the event to use for validation.  That event is the BeforeUpdate event.  The BeforeUpdate event is the LAST event that fires BEFORE a record is saved so think of it as "the buck stops here".  All your final validation especially the Null and relationship checks go in this event.  There is no other event that you can use to ensure that data fields are not left null unless you set them to required on the table itself.  If they are set to required on the table, the save will simply fail with an Access error that can be confusing so the best alternative is to use the Form's BeforeUpdate event to make sure that all required fields are present and valid.  You also use this event when you have dependent data such as DateA must be <= DateB or if fldA is entered, fldB must also be entered.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Dev-Soln LLCCommented:
Yes, there are several.  The one that is probably best to use would be the Form_BeforeUpdate event, which looks something like:
Private Sub Form_BeforeUpdate(Cancel as integer)

    if me.txtField1 & "" = "" then
        msgbox "Enter Field1"
        Cancel = true
    elseif me.txtField2 & "" = "" then
        msgbox "Enter Field2"
        Cancel = true
    End if

End Sub

Open in new window

This code will fire before the record pointer leaves he current record which allows you to cancel the Update event and remain on the current record if all of the required fields are not entered.

However, if the record has not changed, but is not complete, the BeforeUpdate event will not fire.
Juan OcasioApplication DeveloperAuthor Commented:
Thank you both for a speedy answer!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.