asked on How use code just one time
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_Da
MsgBox "Enter selection in 9thField!!!"
Me.txt9thField.SetFocus
Exit Sub
Else
If Nz(Me.txtTraining_End_Date
MsgBox "Enter selection in 10thField!!!"
Me.txt10thField.SetFocus
Exit Sub
Else
If Nz(Me.txtMember_End_Date_T
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
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.
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
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.
Looks like a bug in the interface. I can't get the code tag to work no matter what I do.
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
OK, Dale, what am I doing wrong? Why does my code box put all the code on one line?
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.
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.
You may need to log out of EE and back in.
If that doesn't work, try deleting your EE cookies.
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
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
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
"Preferred Name is Required"
Instead I get...
"Please Fill TextBox #txtPreferred_Name"
Validation in events other than the FORM's BeforeUpdate event can always be bypassed.
Open in new window
Then call it where you need to by just using the Sub Procedure Name
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