Reference Problem

[ksmithson]

I am trying to validate records in a form by using the Before Update event in one of my subforms but when I put in the code, nothing happens. Do I need to reference the main form or subform?

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)If Me.cboxTeamWorkScore.ListIndex = -1 ThenMe.MsgBox "Please select a score."cboxTeamWorkScore.SetFocusCancel = TrueEnd IfIf Me.cboxReliabilityScore.ListIndex = -1 ThenMsgBox "Please select a score."Me.cboxReliabilityScore.SetFocusCancel = TrueEnd IfIf Me.cboxAdaptabilityWorkScore.ListIndex = -1 ThenMsgBox "Please select a score."Me.cboxAdaptabilityScore.SetFocusCancel = TrueEnd IfEnd Sub

[RuralGuy]

The SubFormBeforeUpdate event will occur when the SubForm record is Dirty and you either change to a different record in the SubForm or exit the SubForm back to the MainForm. Is that how you understand it? BTW it is much easier to read code if you surround it with the code tags (the # button when in advanced mode).

[ksmithson]

What I am trying to do is when the user goes to another record, the validation checks to see if all combo/text boxes in the sub forms are not null and if they are a message box pops up saying they have to fill in the nulls before they can move on to the next record. The code is in the sub form's Before Update event but nothing is happening and I am unsure if I have to reference the main form or something.

Here is the cleaned up 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

[RuralGuy]

Put a breakpoint at the beginning of the code and single step the code and you will see what is happening. If you hover over the variables the debugger will show you what they contain.

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

[ksmithson]

Well the code works now and I don't know why it fixed itself (I guess its my "Welcome to Access" initiaion/hazing) but can I be able to add the OR operator into the code so the message box does not pop up for each time one of the controls contain a null?

[RuralGuy]

Yes but you would still need to decide what control gets the focus next.

[ksmithson]

What way would be best to set the focus to the first control (set up from left to right) that has a null and then once the criteria is met the next null gets focused?

[RuralGuy]

Maybe you want to use the AfterUpdate event of each ComboBox.

[ksmithson]

You mean use the After Update event for each control and not for the subform itself?

[RuralGuy]

Nope. I would say use both.