Data Validation On Exit Event

[ksmithson]

Hey guys,

I'm new to using events in Access for forms and subforms. I am looking to make it that when the user moves on to another record in the form, it validates to make sure all combo boxes and text boxes are not null before you can continue onto the next record. I figured using the On Exit event in the subforms where the records that need validation are would trigger the validation but nothing happens when I move onto another record or if I exit out of the form.

Here is my code:

Private Sub sbfrmOtherStats_Exit()
If cboxTeamWorkScore.ListIndex = -1 Then
MsgBox "Please select a score."
cboxTeamWorkScore.SetFocus
Cancel = True
End If
If cboxReliabilityScore.ListIndex = -1 Then
MsgBox "Please select a score."
cboxReliabilityScore.SetFocus
Cancel = True
End If

If cboxAdaptabilityWorkScore.ListIndex = -1 Then
MsgBox "Please select a score."
cboxAdaptabilityScore.SetFocus
Cancel = True
End If
End Sub

Any help is appreciated!

 

[boblarson]

You need to use the form's BEFORE UPDATE event for validation, not the exit event.

 

[ksmithson]

The subform does not have a Before Update option in the events tab, only Enter and Exit.

 

[ksmithson]

So I have to use the main Form's Before Update event to trigger validation in the controls in the subform? How do I reference the controls from the subform to the form? The Form has three subforms: sbfrmAttendance, sbfrmMetrics, sbfrmOtherMetrics

 

[boblarson]

The subform does not have a Before Update option in the events tab, only Enter and Exit.

You need to put it in the SUBFORM's Before Update event. What you have selected there is the Subform CONTROL (not the subform). It is the control which HOUSES the subform on the main form.

See my quick tutorial here to see the first screenshot which explains the difference.

 

[ksmithson]

Hey,

Thanks for showing me where I find the Before Update event in the subform. I am now having a problem where I put the code in the event, but nothing happens when I run the form and try to move onto another record when the combo boxes have nulls in them.

 

[ghudson]

We need to see all the code you are using or post your db to see what is going on.

 

[ksmithson]

Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.cboxTeamWorkScore.ListIndex = -1 Then
MsgBox "Please select a score."
Me.cboxTeamWorkScore.SetFocus
Cancel = True
End If
If Me.cboxReliabilityScore.ListIndex = -1 Then
MsgBox "Please select a score."
Me.cboxReliabilityScore.SetFocus
Cancel = True
End If
If Me.cboxAdaptabilityWorkScore.ListIndex = -1 Then
MsgBox "Please select a score."
Me.cboxAdaptabilityScore.SetFocus
Cancel = True
End If
End Sub

 

[boblarson]

The Before Update event will only fire if you have either made a change to an existing record or have started to modify a new record. So, if you haven't made any changes to the existing record it won't run the event.

Can you give us a better explanation of how your form is used and what you are expecting to happen?

 

[ksmithson]

I am wanting it to where the end user has to input data by using combo boxes and text boxes and cannot leave any of them null. I do not want the validation to pop up an error message until they try to move to the next record. The error message will pop up and then set focus back to the nulled record. I updated the code so if there was more than one null you would not get more than one error message.

The code for that is this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.cboxTeamWorkScore.ListIndex = -1 Or Me.cboxReliabilityScore.ListIndex = -1 Or Me.cboxAdaptabilityScore.ListIndex = -1 Then
MsgBox "Please select a score."
Me.cboxTeamWorkScore.SetFocus
Cancel = True
End If

However, if the user tries to exit the form while there are nulls, Access warns them that the form will not be saved. I will need to make sure the warning will not pop up and keep them from exiting the form until the null in the current record is gone.

 

[ksmithson]

I solved it. I disabled the exit button, made my own, and added data validation code that must be satisfied before you can exit.