Message Popup - Fields Need To Be Completed (1 Viewer)

stu_c · Sep 2, 2019

Hi all
I have a form with about 20 odd fields that all need to be filled in, once complete I want a button to be pressed to generate a report, if any filed are blank I want a message box to pop up, I have used the below code but is there an easier code to use for multiple fields or do I need to do this for each?

Example Names:
Field1
Field2
Field3

Code:

If IsNull(Me.Field1) Or Me.Field1 = "" Then
 MsgBox "All Fields Must Be Completed", vbOKOnly
 OK = True
 Me.Field1.SetFocus

theDBguy · Sep 2, 2019

Hi. One possible approach is to use the Tag property of the control. You can then loop through the controls on the form and examine the Tag property. If the control is tagged, you can then check if the control is empty or not.

Gasman · Sep 2, 2019

Most would tag the required controls, then loop through all the controls, checking the tag, and then check that control and if empty add name to a string, then display the string at the end of the loop if not an empty string.

This has been discussed several times on the forums, so a search should pull up some code.?

stu_c · Sep 3, 2019

all I can seem to find is for single fields

Gasman · Sep 3, 2019

stu_c said:
all I can seem to find is for single fields

That might be the case, but if you loop through all the controls on the form that have a relevant tag in each control you want to check.

Gasman · Sep 3, 2019

Colin uses the same method for hiding/revealin fields.

https://www.access-programmers.co.uk/forums/showthread.php?t=293439

stu_c · Sep 4, 2019

that's confused me even more than I was

isladogs · Sep 4, 2019

stu_c said:
that's confused me even more than I was

You need a similar approach to that in my example but in your case you loop through each of the controls and if the field is empty you build a message string. As an extra touch, you can change the colour of each control that is blank

First set the tag value of all required fields to e.g. REQ
Now set up a loop in your button click event...

Code:

Private Sub ButtonName_Click()

   Dim ctrl As Access.Control, strText As String

   strText="" 'clear existing value of strText

   For Each ctrl In Screen.ActiveForm.Controls
      'loop through
       If ctrl.Tag="REQ" And Nz(ctrl.Value,"") ="" Then
            strText=vbCrLf & vbTab & "- " & ctrl.Name
            ctrl.BackColor=vbCyan '-optional - highlight all empty controls
       Else
            ctrl.BackColor=vbWhite 'restore back colour if completed
        End If
    Next ctrl

    If strText<>"" Then
       MsgBox "You must complete all fields before you can open the report" & vbCrLf & _
        "The following required fields have not been completed" & vbCrLf & _
	strText , vbExclamation, "Missing fields"
    Else
        'all fields completed...open the report
        DoCmd.OpenReport "ReportName", acViewPreview
    End If	
   	
End Sub

Hope that helps remove your confusion

stu_c · Sep 9, 2019

isladogs said:
You need a similar approach to that in my example but in your case you loop through each of the controls and if the field is empty you build a message string. As an extra touch, you can change the colour of each control that is blank

First set the tag value of all required fields to e.g. REQ
Now set up a loop in your button click event...

Code:

Private Sub ButtonName_Click() Dim ctrl As Access.Control, strText As String strText="" 'clear existing value of strText For Each ctrl In Screen.ActiveForm.Controls 'loop through If ctrl.Tag="REQ" And Nz(ctrl.Value,"") ="" Then strText=vbCrLf & vbTab & "- " & ctrl.Name ctrl.BackColor=vbCyan '-optional - highlight all empty controls Else ctrl.BackColor=vbWhite 'restore back colour if completed End If Next ctrl If strText<>"" Then MsgBox "You must complete all fields before you can open the report" & vbCrLf & _ "The following required fields have not been completed" & vbCrLf & _ strText , vbExclamation, "Missing fields" Else 'all fields completed...open the report DoCmd.OpenReport "ReportName", acViewPreview End If End Sub

Hope that helps remove your confusion

Hello mate
I'm still a little confused by what I need to change and copy for the remaining fields, sorry I'm trying to teach myself code but not going well

isladogs · Sep 9, 2019

OK I'll try to find time to create a quick demo for you later

arnelgp · Sep 9, 2019

for multiple field validatation, you may try this also:

Code:

    Dim ctl As Control
    Dim first_ctl As String
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Or _
           TypeOf ctl Is ComboBox Or _
           TypeOf ctl Is ListBox Or _
           TypeOf ctl Is CheckBox Or _
           TypeOf ctl Is OptionButton Or _
           TypeOf ctl Is OptionGroup Then
           
            If (ct.Valuel & "") = "" Then
                If first_ctl = "" Then _
                    first_ctl = ctl.Name
            End If
        End If
    Next
    If first_ctl <> "" Then
        MsgBox "All fields must be completed", vbInformation + vbOKOnly
        Me(first_ctl).SetFocus
    End If

isladogs · Sep 9, 2019

Attached is a simple example as promised.
The code could be improved further but you should hopefully get the idea.

It contains two forms showing different ways of doing this:
Form1 - set the Required property to Yes for the required fields of your table
Form2 - uses code similar to that I provided in post #7 based on setting the tag property to REQ

In both cases, any blank fields that are not required are ignored

The first method is very simple to setup. Access will give an error message for the first blank required field it finds. If there are 20 required fields and 17 are empty, you could potentially get 17 messages in turn if you only complete the field indicated in the message

The second method is more powerful as you can get a list of all missing fields as well as a visual display by changing the backcolor to e.g. cyan

Hope that helps

Pat Hartman · Sep 10, 2019

In reality, users rarely make multiple mistakes so trying to write code that shows multiple errors at once and then walks the user through fixing them is a waste of time.

In the Form's BeforeUpdate event edit each control and if it is missing or invalid, cancel the update, display a message and return control to the user. You don't need to write complex code and the user still gets walked through the errors one at a time.

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.fld1 & "" = "" Then
        Cancel = True
        MsgBox "Fld1 is required.",vbOKOnly
        Me.fld1.SetFocus
        Exit Sub
    End If
    If Me.fld1 > 12 Then
         MsgBox "Fld1 must be <= 12.",vbOKOnly
        Me.fld1.SetFocus
        Exit Sub
    End If  
    If IsDate(Me.fld2) Then
        If Me.fld2 > Date() Then
            Cancel = True
            Msgbox "fld2 must be <= today's date.",vbOKOnly
            Me.fld2.SetFocus
            Exit Sub
        End If   
    Else
        Cancel = True
        MsgBox "Fld2 is not a valid date.",vbOKOnly
        Me.fld2.SetFocus
        Exit Sub
    End If
             
End Sub

isladogs · Sep 10, 2019

Pat Hartman said:
In reality, users rarely make multiple mistakes so trying to write code that shows multiple errors at once and then walks the user through fixing them is a waste of time.

In the Form's BeforeUpdate event edit each control and if it is missing or invalid, cancel the update, display a message and return control to the user. You don't need to write complex code and the user still gets walked through the errors one at a time.

Pat
That appears to be directed at my suggestions
From experience, I disagree about the first point above.
However, the 2nd approach I used in my example app is basically the same as your second paragraph plus a visual display. The code is no more complex than yours!

Micron · Sep 10, 2019

I agree with those who prefer to compile all the missing controls into one message when there are many involved. Perhaps Pat only has experience with users who make only one omission at a time, but I suspect there are many here who have a different experience. IMHO, 20 required fields are worth the effort, and the time spent by programming a more sensible, user-friendly user experience is not a waste of time. What is a waste of programing time (again, IMHO) is writing repetitive IF blocks for 20 fields and having users wonder why they have to get multiple prompts for multiple omissions. Good design is never a waste of time, and what makes good design is a matter of opinion.

Message Popup - Fields Need To Be Completed (1 Viewer)

stu_c

Registered User.

theDBguy

I’m here to help

Gasman

Enthusiastic Amateur

stu_c

Registered User.

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

stu_c

Registered User.

isladogs

MVP / VIP

stu_c

Registered User.

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Attachments

Pat Hartman

Super Moderator

isladogs

MVP / VIP

Micron

AWF VIP

Similar threads

Users who are viewing this thread