We help IT Professionals succeed at work.

How use code just one time

SteveL13
SteveL13 asked
on
I have the following code in several onclick events on a form.  But it seems redundant to keep repeating the code for each onclick event.  How can I use this code just one time and call it from each onclick event?

    If Nz(Me.txtPreferredName, "") = "" Then
        MsgBox "Enter selection in 1stField!!!"
        Me.txt1stField.SetFocus
        Exit Sub
    Else
        If Nz(Me.cboCampus, "") = "" Then
            MsgBox "Enter selection in 2ndField!!!"
            Me.cbo2ndField.SetFocus
            Exit Sub
        Else
            If Nz(Me.txtLastName, "") = "" Then
                MsgBox "Enter selection in 3rdField!!!"
                Me.txt3rdField.SetFocus
                Exit Sub
            Else
                If Nz(Me.txtFirstName, "") = "" Then
                    MsgBox "Enter selection in 4thField!!!"
                    Me.txt4thField.SetFocus
                    Exit Sub
                Else
                    If Nz(Me.cboClass, "") = "" Then
                        MsgBox "Enter selection in 5thField!!!"
                        Me.cbo5thField.SetFocus
                        Exit Sub
                    Else
                        If Nz(Me.txtPhone1, "") = "" Then
                            MsgBox "Enter selection in 6thField!!!"
                            Me.txt6thField.SetFocus
                            Exit Sub
                        Else
                            If Nz(Me.cboCycle, "") = "" Then
                                MsgBox "Enter selection in 7thField!!!"
                                Me.cbo7thField.SetFocus
                                Exit Sub
                            Else
                                If Nz(Me.txtEmailAddress1, "") = "" Then
                                    MsgBox "Enter selection in 8thField!!!"
                                    Me.txt8thField.SetFocus
                                    Exit Sub
                                Else
                                    If Nz(Me.txtTraining_Start_Date, "") = "" Then
                                        MsgBox "Enter selection in 9thField!!!"
                                        Me.txt9thField.SetFocus
                                        Exit Sub
                                    Else
                                        If Nz(Me.txtTraining_End_Date, "") = "" Then
                                            MsgBox "Enter selection in 10thField!!!"
                                            Me.txt10thField.SetFocus
                                            Exit Sub
                                        Else
                                            If Nz(Me.txtMember_End_Date_Term_Of_Service, "") = "" Then
                                                MsgBox "Enter selection in 11thField!!!"
                                                Me.txt11thField.SetFocus
                                                Exit Sub
                                            Else
                                                If Nz(Me.cboNCCC_status, "") = "" Then
                                                    MsgBox "Enter selection in 12thField!!!"
                                                    Me.cbo12thField.SetFocus
                                                    Exit Sub
                                                Else
                                                    If Nz(Me.cboProgram, "") = "" Then
                                                        MsgBox "Enter selection in 13thField!!!"
                                                        Me.cbo13thField.SetFocus
                                                        Exit Sub
                                                    Else
                                                        If Nz(Me.cboGroup, "") = "" Then
                                                            MsgBox "Enter selection in 14thField!!!"
                                                            Me.cbo14thField.SetFocus
                                                            Exit Sub
                                                        Else
                                                            If Nz(Me.txtNSA_ID, "") = "" Then
                                                                MsgBox "Enter selection in 15Field!!!"
                                                                Me.txt15Field.SetFocus
                                                            End If
                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
Comment
Watch Question

You could place your code in a Sub Procedure and then just call that.

Sub VerifyFields
  
all of your code here

End Sub

Open in new window


Then call it where you need to by just using the Sub Procedure Name
Private Sub Text1_Click()
    VerifyFields
End Sub

Open in new window



If you need to use this across more than one form then you will need to make the sub "Public" and you will need to update the textbox references to use the Form!ControlName rather than Me.Control
Top Expert 2014

Commented:
Be careful when you do validation this way.  You can get into an infinite loop, where your validation code keeps setting the focus between two empty controls.

It is preferable if you set the back color of an invalid control to yellow and only set the focus to the first (tab order) yellow colored control when it is time to make the user correct their mistake.
Software & Systems Engineer
Distinguished Expert 2019
Commented:
Such kind of code is handled by Table Oriented Programming
Just store every control name and message to a table like tblControlsCheck
ControlName                           Message
.........                                      ........................
cboCycle                              Enter selection in 7thField
................                               ......................................
txtTraining_Start_Date      Enter selection in 9thField!!!
.........................                        ..............................  

Then you need some code
Dim ctl as Control
For Each ctl in Me.Controls
If Len(ctl)=0
msgbox Dlookup(Message,tblControlsCheck,"ControlName = ' " & ctl.Name")
 
end if
Next

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

Steve, you can also modify that code so it looks something like:

If Nz(Me.txtPreferredName, "") = "" Then
     MsgBox "Enter selection in 1stField!!!"
     Me.txt1stField.SetFocus
 ElseIf Nz(Me.cboCampus, "") = "" Then
     MsgBox "Enter selection in 2ndField!!!"
     Me.cbo2ndField.SetFocus
ElseIf Nz(Me.txtLastName, "") = "" Then
     MsgBox "Enter selection in 3rdField!!!"
     Me.txt3rdField.SetFocus
End If
 

Which is a lot easier to read than the format you are using.

But I would not put this in the Click event of any controls, I would put it in the BeforeUpdate event of the form.  Actually, I have a function in each data entry form called PassesChecks.  It returns a boolean value back to the BeforeUpdate event which sets the Cancel argument of that event and prevents the form from updating if any of the criteria is not met.  It looks something like:

Private Sub Form_BeforeUpdate(Cancel as integer)
    if NOT PassesChecks then exit sub
   'do whatever else you want to do here
   me!ModifiedBy = "steve13"
   me!modifiedDT = Now()
End Sub

and

 Private Function PassesChecks() as boolean
   
   If Nz(Me.txtPreferredName, "") = "" Then
      MsgBox "Enter selection in 1stField!!!"
      Me.txt1stField.SetFocus
   ElseIf Nz(Me.cboCampus, "") = "" Then
      MsgBox "Enter selection in 2ndField!!!"
      Me.cbo2ndField.SetFocus
   ElseIf Nz(Me.txtLastName, "") = "" Then
      MsgBox "Enter selection in 3rdField!!!"
      Me.txt3rdField.SetFocus
   Else
      PassesChecks = true
   End If
End Function



Distinguished Expert 2017

Commented:

Dale's solution is probably what you want but he left out something critical.  When you validate data and the validation fails, you MUST cancel the event in order to prevent the bad data from being saved.

If Not PassesChecks Then 
    Cancel = True 
    Exit Sub 
End If 
'do whatever else you want to do here 
    Me!ModifiedBy = "steve13" 
    Me!modifiedDT = Now() 
End Sub 

To be clear, most validation belongs in the FORM's BeforeUpdate event.  You can NEVER put validation in click events because if the user doesn't enter a field, the validation code won't run.  The BeforeUpdate event is the last Form level event that runs before a record is saved.  It runs regardless of what caused the record to be saved and YOU are in control of whether or not the record get saved.  Think of the BeforeUpdate event as the flapper at the end of a funnel.  If you cancel the event, the flapper stays closed and the record doesn't get saved.  If you don't cancel the event, the record falls through and gets saved.


PS, I'm pretty sure we've had this discussion before.

Distinguished Expert 2017

Commented:

Looks like a bug in the interface.  I can't get the code tag to work no matter what I do.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

Great catch, Pat.  What I usually do is:


Private Sub Form_BeforeUpdate(Cancel as integer)
   Cancel = NOT PassesChecks
   if Cancel then exit sub
   'do whatever else you want to do here
   me!ModifiedBy = "steve13"      me!modifiedDT = Now() 
End Sub
Distinguished Expert 2017

Commented:

OK, Dale, what am I doing wrong?  Why does my code box put all the code on one line?

Commented:
Hi Pat,

I changed your comment to be in a Code Block, which seems to have formatted the output correctly. I'm not sure why your comments did not render correctly,  but we did change editors recently so it could be due to that.
Distinguished Expert 2017

Commented:

Thanks.  Interface I'm seeing doesn't have the correct icons.  The one to the right of the quotes is the one that produced the problem so it is still not fixed although on a different thread, I saw the correct icon which is the three dots with the down arrow.

Top Expert 2014

Commented:
Pat,

You may need to log out of EE and back in.
If that doesn't work, try deleting your EE cookies.

Author

Commented:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    If ErrorChecking = True Then
        Cancel = True
        Exit Sub
    End If

Exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_Form_BeforeUpdate
   
End Sub


Then also…


Private Function ErrorChecking()
On Error GoTo Err_ErrorChecking

    If Nz(Me.txt1stField, "") = "" Then
        ErrorChecking = True
        MsgBox "1st Field is required!"
        Me.txt1stField.SetFocus
        Exit Function
    End If
    If Nz(Me.txt2ndField, "") = "" Then
        ErrorChecking = True
        MsgBox "2nd Field is required!"
        Me.txt2ndField.SetFocus
        Exit Function
    End If
    If Nz(Me.txt3rdField, "") = "" Then
        ErrorChecking = True
        MsgBox "3rd Field is required!"
        Me.txt3rdField.SetFocus
        Exit Function
    End If
    If Nz(Me.txt4thField, "") = "" Then
        ErrorChecking = True
        MsgBox "4th Field is required!"
        Me.txt4thField.SetFocus
        Exit Function
    End If
 
 And so forth...
 
    End If

Exit_ErrorChecking:
    Exit Function

Err_ErrorChecking:
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Error Description: " & Err.Description & vbCrLf & "Error Source: " & Err.Source
    Resume Exit_ErrorChecking

End Function

Author

Commented:
By the way, I can't find the code button.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
Have you tried my method of handling this cases of endless Ifs. ?
Here is  modified code without table
Private Function ErrorChecking()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If Len(Nz(ctl, "")) = 0 Then
MsgBox "Please Fill TextBox #" & ctl.Name
Exit For
 End If
End If
Next
End Function

Open in new window

Tested and working fine..... (the previous sample had some missing 'Then'....i will never be good typing code from my mobile)

Author

Commented:
John,  I may be mistaken but wouldn't that make every text box required?  I have some on the form that are not required.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
If this the case you can skip them...for example I use frequent the Tag property to store kind of info.
e.g for textboxes that you want to check...put in the Tag property the word "required" and modify the code like this:
If ctl.ControlType = acTextBox and ctl.Tag ="required" then

Author

Commented:
Hmmm...   that doesn't give the user the exact message like for example,

"Preferred Name is Required"

Instead I get...

"Please Fill TextBox #txtPreferred_Name"
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:
That's why in my 1st post I mentioned the use of a table to store the textboxes names and the message to display

Author

Commented:
Aahhh…  Now I get it.
Distinguished Expert 2017

Commented:

Validation in events other than the FORM's BeforeUpdate event can always be bypassed.