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

Juan Ocasio
Juan Ocasio used Ask the Experts™
on
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.

Thanks!

Juan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Distinguished Expert 2017
Commented:
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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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 Developer

Author

Commented:
Thank you both for a speedy answer!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial