Solved Saving procedure in datasheet form (1 Viewer)

Kayleigh

Member
Local time
Today, 00:56
Joined
Sep 24, 2020
Messages
706
Hi,
I have a datasheet form for data entry purposes only. I would like to validate the data user inputs once a record is input but before creating a new record. Have tried several events but couldn't achieve desired outcome.

BeforeInsert/BeforeUpdate fires for every new field entered - I would just like to run once the record is filled in.
Current runs as soon as the form opens when no data is even input and also it is referring to the record is has moved onto.

What's best way to do this please?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:56
Joined
Oct 29, 2018
Messages
21,358
Have you tried using the Form's BeforeUpdate event? That should only fire once.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:56
Joined
Feb 19, 2002
Messages
42,981
As theDBguy suggested the FORM's BeforeUpdate event is the best event in which to do validation. You cn do some types of validation in the control's BeforeUpdate event and in some cases you might want to do this if you want eh user to get the error message immediately before he goes to a new control. However, you still need validation in the Form's BeforeUpdate event to ensure that required fields are not empty. If the user doesn't ever change a value in a control, that control's BeforeUpdate event doesn't run so it can never detect missing data. Here's an example of what validation code might look like:
Code:
If IsDate(DOB) Then
    If DateDiff("y",Date(), DOB) >= 16, Then
        If DateDif("y",Date(), DOB) < = 75 then
        Else
            msgbox "Employee is past retirement age.", vbOKOnly
            Me.DOB.SetFocus
            Cancel = True
            Exit Sub
        End If
    Else
        msgbox "Employee is too young.",vbOKOnly
        Me.DOB.SetFocus
        Cancel = True
        Exit Sub
    End If
Else
    msgbox "Date of Birth is required",vbOKOnly
    Me.Dob.SetFocus
    Cancel = True
    Exit Sub
End If

If Me.LastName & "" = "" Then
    msgbox "Last Name is required.", vbOKOnly
    Me.LastName.Setfocus
    Cancel = True
    Exit Sub
End If
 

Users who are viewing this thread

Top Bottom