Form Validation Help

[accessma]

I have a form I need to validate with the code below. While this may work it would be a lot easier on the user to be presented with a list of incomplete fields at time of either Cancel or Save rather than potentially having to answer multiple warning boxes in succession (I know that would certainly annoy me).

I would like to have a list presented similar to the ones you see on the net telling you what you didn't fill out. Im not sure if this is even possible especially with my lack of Access VBA coding experience but thought I would post anyway to see if anyone could assist me.

Private Sub chkCompleted_BeforeUpdate(Cancel As Integer)
If chkCompleted = False Then
    lblDateCompleted.Visible = False
    lblDateCompleted.Visible = False
else
 lblDateCompleted.Visible = False
    lblDateCompleted.Visible = False
End If


If ((IsNull(Me.QueryPhone) Or Me.QueryPhone = "")) Then
MsgBox "You must enter a Contact telephone number", vbInformation, "Data Validation"
Me.QueryPhone.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.DateEntered) Or Me.DateEntered = "")) Then
         MsgBox "You must enter today's date",  
          vbInformation, "Data Validation"
Me.DateEntered.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.PSUDept) Or Me.PSUDept = "")) Then
         MsgBox "You must enter a department",  
          vbInformation, "Data Validation"
Me.PSUDept.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.FormCompBy) Or Me.FormCompBy = "")) Then
         MsgBox "You must enter your name",  
          vbInformation, "Data Validation"
Me.FormCompBy.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.ProjTitle) Or Me.ProjTitle = "")) Then
         MsgBox "You must enter a Project Title",  
          vbInformation, "Data Validation"
Me.ProjTitle.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.ProjDesc) Or Me.ProjDesc = "")) Then
         MsgBox "You must enter a description",  
          vbInformation, "Data Validation"
Me.ProjDesc.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.StartDate) Or Me.StartDate = "")) Then
         MsgBox "You must enter a Start Date",  
          vbInformation, "Data Validation"
Me.StartDate.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.ProjectedEndDate) Or Me.ProjectedEndDate = "")) Then
         MsgBox "You must enter a Projected End Date",  
          vbInformation, "Data Validation"
Me.ProjectedEndDate.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.Hours) Or Me.Hours = "")) Then
         MsgBox "You must enter hours",  
          vbInformation, "Data Validation"
Me.Hours.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.LevelOfEffort) Or Me.LevelOfEffort = "")) Then
         MsgBox "You must enter LevelOfEffort",  
          vbInformation, "Data Validation"
Me.LevelOfEffort.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.OtherDivisionalGoal) Or Me.OtherDivisionalGoal = "")) Then
         MsgBox "You must enter a Other Divisional Goal",  
          vbInformation, "Data Validation"
Me.OtherDivisionalGoal.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.ProjectSponsor) Or Me.ProjectSponsor = "")) Then
         MsgBox "You must enter a Project Sponsor",  
          vbInformation, "Data Validation"
Me.ProjectSponsor.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.SponsoringDepartment) Or Me.SponsoringDepartment = "")) Then
         MsgBox "You must enter a Sponsoring Department",  
          vbInformation, "Data Validation"
Me.SponsoringDepartment.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.Collaborators) Or Me.Collaborators = "")) Then
         MsgBox "You must enter Collaborators",  
          vbInformation, "Data Validation"
Me.Collaborators.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.PrimaryClient) Or Me.PrimaryClient = "")) Then
         MsgBox "You must enter a Primary Client",  
          vbInformation, "Data Validation"
Me.PrimaryClient.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.PrimaryClientDept) Or Me.PrimaryClientDept = "")) Then
         MsgBox "You must enter a Primary Client Department",  
          vbInformation, "Data Validation"
Me.PrimaryClientDept.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.ProjectType) Or Me.ProjectType = "")) Then
         MsgBox "You must enter a Project Type",  
          vbInformation, "Data Validation"
Me.ProjectType.SetFocus
Cancel = True
ElseIf
           ((IsNull(Me.DepartmentID) Or Me.DepartmentID = "")) Then
         MsgBox "You must enter a Department ID",  
          vbInformation, "Data Validation"
Me.DepartmentID.SetFocus
Cancel = True

End Sub

 

[pbaldy]

The essence of it is to build a message with each failed test:

strMessage = strMessage & "You must fill out blah" & vbCrLf

Then at the end:

MsgBox strMessage

 

[accessma]

Will this list each field? Still a little confused.

 

[pbaldy]

You still have to test every control. Within each test, if it fails, add it to the string. At the end, that string will contain a line for every failed test.

 

[missinglinq]

Here's code that insists that the user fill in every textbox, one at a time. Perhaps this is waht you're looking for.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control

   DL = vbNewLine & vbNewLine

   For Each ctl In Me.Controls

     If ctl.ControlType = acTextBox Then
           If Trim(ctl.Value & "") = "" Then

        Msg = "'" & ctl.Name & "' is Required!" & DL & _
               "Please enter a value to proceed . . ."
         Style = vbInformation + vbOKOnly
         Title = "Required Data Missing! . . ."
         MsgBox Msg, Style, Title
         ctl.SetFocus
         Cancel = True
         Exit For
         End If
      End If
   Next
End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, ctl As Control

   DL = vbNewLine & vbNewLine

   For Each ctl In Me.Controls

     If ctl.ControlType = acTextBox Then
           If Trim(ctl.Value & "") = "" Then

        Msg = "'" & ctl.Name & "' is Required!" & DL & _
               "Please enter a value to proceed . . ."
         Style = vbInformation + vbOKOnly
         Title = "Required Data Missing! . . ."
         MsgBox Msg, Style, Title
         ctl.SetFocus
        ' Cancel = True
         Exit For
         End If
      End If
   Next
End Sub

 

[accessma]

Thanks for the code, but there are 4 i do not want to test. Is there a work around that? Thanks

 

[accessma]

Here is what I came up with:

I went into the Tab property of each form element at typed “Required” no quotes. I then entered the following in the Before_Update sub:

Private Sub chkCompleted_BeforeUpdate(Cancel As Integer)
If chkCompleted = False Then
        lblDateCompleted.Visible = False
        txtDateCompleted.Visible = False
    Else
        lblDateCompleted.Visible = True
        txtDateCompleted.Visible = True
    End If
    Dim ctl As Variant
    For Each ctl In Me
        If ctl.Tag = "Required" Then
           If IsNull(ctl) Or ctl = "" Then
              MsgBox "You must complete the required field '" & " " & ctl.Name
              ctl.SetFocus
              Cancel = True
              Exit Sub
          End If
       End If
    Next
    Set ctl = Nothing
    
End Sub

I read somewhere on this board that the Before Update was the last procedure to be executed if you did a regular save, or canceled out of the form. Obviously this code isn’t working since I am posting again. Can anyone tell me what I am doing wrong? Thanks

 

[pbaldy]

You want the before update event of the form, not a control. Plus, that technique will give you a message box for each empty control rather than 1 message box with all empty controls.

 

[accessma]

Sorry to be so dense, just learning this. So could you provide a working example? Thanks for your replies.

 

[accessma]

Sorry to be so dense, just trying to learn. Could you explain further in more detail and provide an example. Thanks for your patience.

 

[accessma]

I'd be glad to email the DB to you if it would be easier to see what I have done.

 

[accessma]

I'd be glad to email the form code so you can see what I am trying to do. It may be to big to post here. Thanks.