I can't seem to get the Validation set for form fields.

[shane201980]

So I've look all over, but can't seem to grasp the concept of validating my form code wise.



I have a contact form that I need to validate prior to saving to verify the following:
1 - If a company or (first name & last name) have not been entered then cancel the save/close event.

2 - I have 3 cascading comboboxes that if the fields are null, then cancel the save/close event.

(If the above field(s) has been validated then I want to run a message box verifying that they want to save the contact.)

I'm including the validation code I have now, but it doesn't work. With the code below, it just saves everything, or if I input a company name it asks if I want to save. It's like i'm only getting to the first If only. Also, i have not included the comboboxes as I figure I'll start small and add in until I get what I need.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next

If Me.Dirty Then
   If Not IsNull(Me.Company) Then
      If MsgBox("The record has changed - do you want to save it?", _
         vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Me.Undo
      ElseIf IsNull(Me.First_Name) Then
         MsgBox "You need to fill out a 'Company' or 'First & Last Name'!"
         Cancel = True
      ElseIf IsNull(Me.Last_Name) Then
         MsgBox "You need to fill out a 'Company' or 'First & Last Name'!"
         Cancel = True
      Else
         If MsgBox("The record has changed - do you want to save it?", _
            vbYesNo + vbQuestion, "Save Changes") = vbNo Then
            Me.Undo
         End If
      End If
   End If
End If

End Sub

I've done ElseIf and Else If, that doesn't seem to make a difference. I believe I'm going about the validation process all wrong, but I just can't seem to grasp the idea yet. Anything is a help, Thanks!

[ssanfu]

I have a contact form that I need to validate prior to saving to verify the following:
1 - If a company or (first name & last name) have not been entered then cancel the save/close event.

2 - I have 3 cascading comboboxes that if the fields are null, then cancel the save/close event.

(If the above field(s) has been validated then I want to run a message box verifying that they want to save the contact.)

If I read the above correctly, this might (should?) meet your requirements:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error Resume Next

   If Me.Dirty Then
      'company name must be filled in OR
      'First AND Last names must be filled in
      If IsNull(Me.Company) Or (IsNull(Me.Last_Name) And IsNull(Me.Last_Name)) Then
         MsgBox "You need to fill out a 'Company' or 'First & Last Name'!"
         Cancel = True
      End If
   Else
      'company name OR
      ' first AND last names are filled in

      ' now check the other controls.
      '========================================
      'check the cascading combo boxes
      '========================================
      If IsNull(Me.combobox1) Then
         MsgBox "You must make a selection in Combo box 1 !"
         Cancel = True
      End If
      If IsNull(Me.combobox2) Then
         MsgBox "You must make a selection in Combo box 2 !"
         Cancel = True
      End If
      If IsNull(Me.combobox3) Then
         MsgBox "You must make a selection in Combo box 3 !"
         Cancel = True
      End If


      ' now ask if the record should be saved
      If MsgBox("The record has changed - do you want to save it?", _
                vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Cancel = True
         Me.Undo
      End If
   End If

End Sub

Note: change the names in BLUE to your control names

[June7]

Maybe:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next

If Me.Dirty Then
   If MsgBox("The record has changed - do you want to save it?", _
      vbYesNo + vbQuestion, "Save Changes") = vbYes Then
      If IsNull(Me.Company) Or IsNull(Me.First_Name) Or IsNull(Me.Last_Name) Then
         If MsgBox("You need to fill out a 'Company' or 'First Name' or 'Last Name' before record can be saved!" & _
            vbCrLf & "Do you want to continue with edit?") = vbYes Then
            Cancel = True
         Else
            Me.Undo
         End If
      End If
   Else
      Me.Undo
   EndIf
End If

End Sub

Refer to link at bottom of my post for debug techniques.

[shane201980]

Steve,
I've copied the code you posted, and only changed the (Last_Name to First_Name) and the combobox names, but it doesn't work either. Here's what happens:

1. If I only enter one of the following ie. Company, or First Name & Last Name; it gives me the message box that i need one or the others.
2. If I enter all 3 fields, it saves and never gives the msgbox to ask if I want to save.
3. The combobox validation never fires, or is skipped because they are empty and it never runs this portion.

It looked really good and I was able to follow it, or so I thought. There is absolutely nothing else in my before update beyond this code. Any ideas?

June7, I've favorited the link. Thanks, it looks like I might get some use out of it, really quickly.

[shane201980]

June 7,

I guess I need to slow down with this since I overlooked the code you posted. Your code gets me a little closer at this point. I had to change the msgbox to vbYesNo since all I got was an OK button. But this almost works, here's the problem that I have with this code:

1.If I only enter one of the following ie. Company, or First Name & Last Name; it gives me the message box that i need one or the others. (If I enter all 3 fields, everything is good and saves as it should.)

So now i'm really stuck, I can't understand why these codes are requiring all fields and one or another.

[June7]

What do you want? Do you want to require users to enter a value in each of the 3 fields for a valid record? That was my impression from your original code.

[shane201980]

Sorry for the confusion.

I will have a multitude of cases where the contact may be an individual, a company, or both.

i would like to make sure that the user cannot input unless one of the following has been inputed:

1. Company
or
2. First Name and Last Name

Hope that clears it up, I don't need all fields; just the field(s) in 1 or 2.

[shane201980]

Ok, so I've got it working at 98% and the code ain't purrty.
I'll attach the code below, but I'll be attaching more code since I still have a problem.

Working at 98%

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If Me.Dirty Then
If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbYes Then
If Not IsNull(Me.Company) Then
Exit Sub
Else
If IsNull(Me.First_Name) Then
If MsgBox("You need to fill out a 'Company' or 'First and Last Name' before record can be saved!", _
vbYesNo, "Do you want to continue with edit?") = vbYes Then
Cancel = True
Else
Me.Undo
End If
Else
If IsNull(Me.Last_Name) Then
If MsgBox("You need to fill out a 'Company' or 'First and Last Name' before record can be saved!", _
vbYesNo, "Do you want to continue with edit?") = vbYes Then
Cancel = True
Else
Me.Undo
End If
Else
Exit Sub
End If
End If
End If
Else
Me.Undo
End If
End If
End Sub

My latest problem is that if i close the document with "X" everything works great, i go through the save motions and the form closes. However, if I use the Add New Record cmdbutton, the record saves, but does not open to a new record for inputting, it stays on the record that was just saved. Here is the code for the cmdButton:

Private Sub cmdAddNewRecord_Click()
On Error GoTo MyErr
DoCmd.GoToRecord , , acNewRec
Me.[Events].RowSource = Me.[Events].RowSource
MyExit:
Exit Sub
MyErr:
If Err.Number <> 2105 Then
MsgBox Err.Description
End If
Resume MyExit

End Sub

[ssanfu]

Had an errant "ELSE" statement.

And had an "AND" where it should have been "OR" (in red)

See if this works better:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error Resume Next

   If Me.Dirty Then
      'company name must be filled in OR
      'First AND Last names must be filled in
      If IsNull(Me.Company) Or IsNull(Me.Last_Name) OR IsNull(Me.Last_Name) Then
         MsgBox "You need to fill out a 'Company' or 'First & Last Name'!"
         Cancel = True
      End If

      'company name OR
      ' first AND last names are filled in

      ' now check the other controls.
      '========================================
      'check the cascading combo boxes
      '========================================
      If IsNull(Me.combobox1) Then
         MsgBox "You must make a selection in Combo box 1 !"
         Cancel = True
      End If
      If IsNull(Me.combobox2) Then
         MsgBox "You must make a selection in Combo box 2 !"
         Cancel = True
      End If
      If IsNull(Me.combobox3) Then
         MsgBox "You must make a selection in Combo box 3 !"
         Cancel = True
      End If

      'controls are filled in, so
      ' now ask if the record should be saved
      If MsgBox("The record has changed - do you want to save it?", _
                vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Cancel = True
         Me.Undo
      End If
   End If

End Sub

Note: change the names in BLUE to your control names

----------
I don't like to use IsNull(Me.Company) to determine if a control is NULL because someone could just enter one or more spaces.
So I use
If Len(Trim(Me.Company & ""))>0 Then

[shane201980]

Thanks Steve and June 7, your help has been extremely helpful.

So far the last code i listed seems to be the direction I need since I don't need all the fields filled, just either the company or First & Last Names. And Steve, i totally agree with the Len(trim) i remember seeing it before, but unfortunately I couldn't get it to work. No worries as i still can't save anything with just spaces, only physical characters.

And lastly, I figured out why it would not save and go to a new record, and that is because i was recieving an error saying "You can't go to the specific record". I'm still working on that, as it seems that the goto new record function of the cmdbutton is firing before the record is saved. Hmm, myabe I can do a command save at the end of the before update. Sounds like it might loop, worth a try. Lol!

***Ok, I got it. My Add New Record button was trying to go to new record while I was on the new record. So I change my button to basically the following and everything in life is good again.

Private Sub cmdAddNewRecord_Click()
If Me.NewRecord Then
DoCmd.RunCommand (acCmdSaveRecord)
DoCmd.RunCommand (acCmdRecordsGoToNew)
Else
DoCmd.RunCommand (acCmdRecordsGoToNew)
End If
End Sub


Anyhow, thanks guys.

[ssanfu]

Great...

Looking at your new record code, since there are duplicate lines in the TRUE clause and the FALSE clause, so you could move the line outside the IF() function.

Private Sub cmdAddNewRecord_Click()
      If Me.NewRecord Then
          DoCmd.RunCommand (acCmdSaveRecord)

     End If

     DoCmd.RunCommand (acCmdRecordsGoToNew)

End Sub