Data validation for multiple fields on form using VBA with MsgBox

[Stephenson]

Hello all. I have an ongoing problem with data validation. I have certain TextBoxes and ComboBoxes I'd like to add MsgBoxes to and not allow the user to proceed if they are left blank. In addition I want to make sure tboEquipmentTime is filed in if cboEquipment is used. Once these fields are used I would like the form to give a MsgBox telling the user a record for the employee they've entered has been added then move to a new record.

I'm currently using the following code.

Private Sub cmdSave_Click()       If (IsNull(Me.cboJob)) Or (Me.cboJob = "") Then
        MsgBox "Please Enter Job Name", vbOKOnly Or vbExclamation, "JOB NAME"
        Me.cboJob.SetFocus
   Exit Sub
    End If
   If IsNull(Me.cboEmployee) Then
        MsgBox "Please Enter Employee Name", vbOKOnly Or vbExclamation, "EMPLOYEE NAME"
        Me.cboEmployee.SetFocus
   Exit Sub
    End If
   If IsNull(Me.tboDate) Then
        MsgBox "Please Enter Date", vbOKOnly Or vbExclamation, "Date"
        Me.tboDate.SetFocus
   Exit Sub
    End If
   If IsNull(Me.cboService) Then
        MsgBox "Please Enter Service Type", vbOKOnly Or vbExclamation, "SERVICE"
        Me.cboEmployee.SetFocus
   Exit Sub
    End If
       
    If (IsNull(Me.cboEquipment)) Or (Me.cboEquipment = "") Then
    Else
    If (IsNull(Me.tboEquipmentTime)) Or (Me.tboEquipmentTime.Value = "") Then
          MsgBox "Please Enter Time For " & (DLookup("[Model]", "Equipment", "ID=" & [cboEquipment])) & " ", vbOKOnly Or vbExclamation, "Enter Equipment Time"
          Me.tboEquipmentTime.SetFocus
    Exit Sub
     End If
     End If
        
        MsgBox "Record for " & Me.lblFrmName.Caption & " has been added", vbOKOnly Or vbInformation, "Saved"
        DoCmd.GoToRecord , , acNewRec
End Sub

This line of code does not run for cboJob or cboService. It does however run for cboEmployee and tboDate. In addition it will bring up the message box for tboEquipmentTime regardless if cboEquipment has been filled out or not. It will also continue to the MsgBox telling the user the record has been added even though it has not nor has it proceeded to the next record. (it drives me nuts)

I have this code in my on click procedure, but have also tried it in the before update for the form with no luck. There must be something simple that an newbie such as myself is missing. If anyone sees what it is please let me know this glitch has me going crazy.

Thanks for looking. AReport_8.zip

[RuralGuy]

Here's your code properly indented:

Private Sub cmdSave_Click()
   If (IsNull(Me.cboJob)) Or (Me.cboJob = "") Then
      MsgBox "Please Enter Job Name", vbOKOnly Or vbExclamation, "JOB NAME"
      Me.cboJob.SetFocus
      Exit Sub
   End If
   If IsNull(Me.cboEmployee) Then
      MsgBox "Please Enter Employee Name", vbOKOnly Or vbExclamation, "EMPLOYEE NAME"
      Me.cboEmployee.SetFocus
      Exit Sub
   End If
   If IsNull(Me.tboDate) Then
      MsgBox "Please Enter Date", vbOKOnly Or vbExclamation, "Date"
      Me.tboDate.SetFocus
      Exit Sub
   End If
   If IsNull(Me.cboService) Then
      MsgBox "Please Enter Service Type", vbOKOnly Or vbExclamation, "SERVICE"
      Me.cboEmployee.SetFocus
      Exit Sub
   End If 
   If (IsNull(Me.cboEquipment)) Or (Me.cboEquipment = "") Then
   Else
      If (IsNull(Me.tboEquipmentTime)) Or (Me.tboEquipmentTime.Value = "") Then
         MsgBox "Please Enter Time For " & (DLookup("[Model]", "Equipment", "ID=" & [cboEquipment])) & " ", vbOKOnly Or vbExclamation, "Enter Equipment Time"
         Me.tboEquipmentTime.SetFocus
         Exit Sub
      End If
   End If
   MsgBox "Record for " & Me.lblFrmName.Caption & " has been added", vbOKOnly Or vbInformation, "Saved"
   DoCmd.GoToRecord , , acNewRec
End Sub

Note: An FYI:
If Len(YourControl & "") = 0 Then
...covers BOTH a Null and zero length field.

[RuralGuy]

Are you aware that unless you take extraordinary steps, Access WILL save your record if anything has changed! No "Save Record" button is necessary.

[Stephenson]

RuralGuy,

Thank you for properly indenting my code. All this is still very new to me.

I was unaware that using If Len would do this. I'll give it a shot in my code, as well as give it proper indenting. As for cmdSave this is just for the end user. I know that access saves automatically, but they do not. I'm using this code to validate the field entries, give the user a nice warm message and to advance the form to a new record so they can continue with entries.

Thank you for your suggestion.

[RuralGuy]

I use SMART INDENTER to format my VBA. http://www.oaltd.co.uk/

[Stephenson]

That's great. I had no idea there was such a thing. Thanks for the link.