Form Validation with multiple selections and/or fields?

[T. McConnell]

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???

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]

move your code to the BeforeUpdate Event of the Form.

 

[GinaWhipp]

You could use something like...
https://www.access-diva.com/vba13.html

You can then just put require on the Tag property of the control without having to list each one out.

 

[T. McConnell]

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

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

 

[Pat Hartman]

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. McConnell]

Obviously I'm oblivious to some things it seems lol. Thanks for that info, I'll make that change to avoid this.

 

[Pat Hartman]

Excellent choice. You're welcome