Form Validation with multiple selections and/or fields? (1 Viewer)

T

T. McConnell

Registered User.
Local time
Today, 04:54
Joined
Jun 21, 2019
Messages
63
Hey everyone, I am need of help once again. Working on a simple expense tracker database. On the form I have two different comboboxes that based on certain selections with make some textfields visible. On the first combobox (ExpType) there are 3 different options within the list that if selected shows other textboxes on the form. (the options are Customer, Nextgear, and TBA)
The other combobox (MethodOfPayment) has one option (Check) that if selected will show a textbox (CheckNumber)
What I am wanting to do is basically if either of the first 3 options are selected to verify the fields aren't null before submitting the record. The same goes for the CheckNumber box, if null then give message to go back and enter.
The code I have used is below , however I have tried different ways of doing this with no luck. Basically what happens if there are any errors it does catch them and provides the error message, but if all fields are not null and are filled out properly, the Save button doesn't do anything. Any suggestions???
Code: 
Private Sub btnSave_Click()

If Me.MethodOfPayment = "Check" Then
    If IsNull(Me.CheckNumber) Then
        If MsgBox("Please Enter a Valid Check Number", vbRetryCancel, "Incorrect Check Number") = vbRetry Then
            Me.CheckNumber.SetFocus
        Else
            If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
                DoCmd.RunCommand (acCmdSaveRecord)
                DoCmd.RunCommand (acCmdRecordsGoToNew)
                    Me.ExpenseSummary.Requery
                    Exit Sub
            End If
        End If
     End If
End If

If Me.ExpType = "Customer" Then
    If IsNull(Me.Customer) Or IsNull(Me.CarYear) Or IsNull(Me.CarMake) Or IsNull(Me.CarModel) Or IsNull(Me.CarColor) Or IsNull(Me.PurchaseLocation) Then
        If MsgBox("Please Enter a Customer and Vehicle Information", vbRetryCancel, "Select Customer") = vbRetry Then
            Me.Customer.SetFocus
        Else
            If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
                DoCmd.RunCommand (acCmdSaveRecord)
                DoCmd.RunCommand (acCmdRecordsGoToNew)
                    Me.ExpenseSummary.Requery
                    Exit Sub
            End If
        End If
     End If
End If


If Me.ExpType = "NextGear" Then
    If IsNull(Me.CarYear) Or IsNull(Me.CarMake) Or IsNull(Me.CarModel) Or IsNull(Me.CarVIN) Or IsNull(Me.PurchaseLocation) Then
        If MsgBox("Please Enter Vehicle Information", vbRetryCancel, "Enter All Vehicle Information") = vbRetry Then
            Me.CarYear.SetFocus
            
        Else
            If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
                DoCmd.RunCommand (acCmdSaveRecord)
                DoCmd.RunCommand (acCmdRecordsGoToNew)
                    Me.ExpenseSummary.Requery
                    Exit Sub
            End If
        End If
     End If
End If

If Me.ExpType = "TBA" Then
    If IsNull(Me.CarYear) Or IsNull(Me.CarMake) Or IsNull(Me.CarModel) Or IsNull(Me.CarVIN) Or IsNull(Me.PurchaseLocation) Then
        If MsgBox("Please Enter Vehicle Information", vbRetryCancel, "Enter All Vehicle Information") = vbRetry Then
            Me.CarYear.SetFocus
        Else
            If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
                DoCmd.RunCommand (acCmdSaveRecord)
                DoCmd.RunCommand (acCmdRecordsGoToNew)
                    Me.ExpenseSummary.Requery
                    Exit Sub
            End If
        End If
     End If
End If

If IsNull(Me.ExpDescription) Or IsNull(Me.ExpType) Or IsNull(Me.Amount) Or IsNull(Me.MethodOfPayment) Then
    If MsgBox("Please Enter Expense Details", vbRetryCancel, "Enter All Expense Information") = vbRetry Then
        Me.ExpDescription.SetFocus
        Else
            If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
                DoCmd.RunCommand (acCmdSaveRecord)
                DoCmd.RunCommand (acCmdRecordsGoToNew)
                    Me.ExpenseSummary.Requery
                    Exit Sub
            End If
    End If
End If
End Sub

I am sure there is a better way to do this, so I am open to suggestions.
Thanks again for any help. I can provide a sample of the DB if needed as still in testing.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:54
Joined
May 7, 2009
Messages
19,234
move your code to the BeforeUpdate Event of the Form.
 
T

T. McConnell

Registered User.
Local time
Today, 04:54
Joined
Jun 21, 2019
Messages
63
I was able to get it to work using the code below. Thank you both Arnelgp and GinaWhipp. I hadn't tried it on the BeforeUpdate event before getting this to work. I just noticed I had a hard time getting the form to validate beforehand because these validations were based off of 3 different selections from drop downs which would make other fields visible and those had to be filled out. However it is working now :)

Code: 
Private Sub btnSave_Click()

If Me.MethodOfPayment = "Check" Then
    If (IsNull(Me.CheckNumber)) Or (Me.CheckNumber = 0) Then
        MsgBox "Please Enter a Valid Check Number", vbRetryCancel, "Incorrect Check Number"
            Me.CheckNumber.SetFocus
        Exit Sub
    End If
End If

If Me.ExpType = "Customer" Then
    If (IsNull(Me.Customer)) Or (IsNull(Me.CarYear)) Or (IsNull(Me.CarMake)) Or (IsNull(Me.CarModel)) Or (IsNull(Me.CarColor)) Or (IsNull(Me.PurchaseLocation)) Then
        MsgBox "Please Enter a Customer and Vehicle Information", vbRetryCancel, "Select Customer"
            Me.Customer.SetFocus
        Exit Sub
    End If
End If

If Me.ExpType = "NextGear" Then
    If (IsNull(Me.CarYear)) Or (IsNull(Me.CarMake)) Or (IsNull(Me.CarModel)) Or (IsNull(Me.CarVIN)) Or (IsNull(Me.PurchaseLocation)) Then
        MsgBox "Please Enter All Vehicle Information", vbRetryCancel, "Enter All Vehicle Information"
            Me.CarYear.SetFocus
        Exit Sub
    End If
End If

If Me.ExpType = "TBA" Then
    If (IsNull(Me.CarYear)) Or (IsNull(Me.CarMake)) Or (IsNull(Me.CarModel)) Or (IsNull(Me.CarVIN)) Or (IsNull(Me.PurchaseLocation)) Then
        MsgBox "Please Enter All Vehicle Information", vbRetryCancel, "Enter All Vehicle Information"
            Me.CarYear.SetFocus
    Exit Sub
    End If
End If

If (IsNull(Me.ExpDescription)) Or (IsNull(Me.ExpType)) Or (IsNull(Me.Amount)) Or (IsNull(Me.MethodOfPayment)) Then
        MsgBox "Please Enter Expense Details", vbRetryCancel, "Enter All Expense Information"
            Me.ExpDescription.SetFocus
    Exit Sub
    Else
        If MsgBox("Are you sure you want to submit this Expense?", vbOKCancel, "Submit Expense?") = vbOK Then
              DoCmd.RunCommand (acCmdSaveRecord)
              DoCmd.RunCommand (acCmdRecordsGoToNew)
                  Me.ExpenseSummary.Requery
                    Exit Sub
    End If
End If

End Sub
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:54
Joined
Feb 19, 2002
Messages
43,263
You do realize that if you close the form without pressing the save button, Access will still save the record don't you? Given that, the best place for validation code is the Form's BeforeUpdate event since it can never be bypassed. It is always the last event to run before the record is actually saved. So in your save button, you would force the record to save and the validation would happen in the BeforeUpdate event.
 
T

T. McConnell

Registered User.
Local time
Today, 04:54
Joined
Jun 21, 2019
Messages
63
Obviously I'm oblivious to some things it seems lol. Thanks for that info, I'll make that change to avoid this.
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Value changed after Function
Replies
9
Views
169
mloucel
mloucel
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
430
gemma-the-husky
gemma-the-husky
M
Solved Cannot delete record from linked SQL Server table using VBA
Replies
1
Views
229
mikenyby
M
H
Datasheet form filter combo box with specific value and blanks
Replies
4
Views
187
Hedge01
H
R
Solved Form behavior for null/empty controls
Replies
12
Views
887
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom