Require value - whether user tabs or manually selects

[Caburky via AccessMonster.com]

Using a tabbed subform - the code below works perfect to validate one field
and force a required value for a second field IF the users tabs through the
fields. However, I'm not sure how to compile the code that gives the user
the option of tabbing or manually selecting each field.

Private Sub ALARM_Q2_REM_Exit(Cancel As Integer)

'if the alarm rate <3 and the rem doesn't have a value then pop a message
'and force the user to stay in the control until it has a value

If (Me.ALARM_Q2_RATE.Value) > 0 And (Me.ALARM_Q2_RATE.Value) < 3 And _
Nz(Me.ALARM_Q2_REM.Value, "") = "" Then

MsgBox "You must enter a value to continue."
Cancel = True
Else
Me.ALARM_Q2_REM.BackColor = 16777215

End If
End Sub

 

[Marshall Barton]

Using a tabbed subform - the code below works perfect to validate one field
and force a required value for a second field IF the users tabs through the
fields. However, I'm not sure how to compile the code that gives the user
the option of tabbing or manually selecting each field.

Private Sub ALARM_Q2_REM_Exit(Cancel As Integer)

'if the alarm rate <3 and the rem doesn't have a value then pop a message
'and force the user to stay in the control until it has a value

If (Me.ALARM_Q2_RATE.Value) > 0 And (Me.ALARM_Q2_RATE.Value) < 3 And _
Nz(Me.ALARM_Q2_REM.Value, "") = "" Then

MsgBox "You must enter a value to continue."
Cancel = True
Else
Me.ALARM_Q2_REM.BackColor = 16777215

End If
End Sub

Are you sure the second And should not be Or

If (Me.ALARM_Q2_RATE> 0 And Me.ALARM_Q2_RATE < 3) _
Or Nz(Me.ALARM_Q2_REM, "") = "" Then

BUT, there is no way to force users to go to the control.

It's is almost always better to put that kind of validation
in the form's BeforeUpdate event. Setting Cancel = True in
the BeforeUpdate event will prevent the record from being
saved. You would also want to add the line:
Me.ALARM_Q2_REM.SetFocus
after the Cancel line.

The color change logic would normally go in the text box's
AfterUpdate event.

Private Sub ALARM_Q2_REM_AfterUpdate()
If (Me.ALARM_Q2_RATE> 0 And Me.ALARM_Q2_RATE < 3) _
And Nz(Me.ALARM_Q2_REM, "") = "" Then
Me.ALARM_Q2_REM.BackColor = vbYellow ' or ???
Else
Me.ALARM_Q2_REM.BackColor = vbWhite
End If
End Sub

 

[Caburky via AccessMonster.com]

Marshall,
Thank you, by suggesting that I put this validation sequence on the
BeforeUpdate event it is in effect forcing the user to select a value for
ALARM_Q2_REM whether tabbing or selecting the combo box.

I have several alarm and remediation questions on this subform. How do I go
about evaluating each field? For example: I have ALARM_Q1_RATE and
ALARM_Q1_REM, ALARM_Q2_RATE and ALARM_Q2_REM, ALARM_Q3_RATE and ALARM_Q3_REM,
etc.
Thank you!

Using a tabbed subform - the code below works perfect to validate one field
and force a required value for a second field IF the users tabs through the

[quoted text clipped - 16 lines]

End If
End Sub

Are you sure the second And should not be Or

If (Me.ALARM_Q2_RATE> 0 And Me.ALARM_Q2_RATE < 3) _
Or Nz(Me.ALARM_Q2_REM, "") = "" Then

BUT, there is no way to force users to go to the control.

It's is almost always better to put that kind of validation
in the form's BeforeUpdate event. Setting Cancel = True in
the BeforeUpdate event will prevent the record from being
saved. You would also want to add the line:
Me.ALARM_Q2_REM.SetFocus
after the Cancel line.

The color change logic would normally go in the text box's
AfterUpdate event.

Private Sub ALARM_Q2_REM_AfterUpdate()
If (Me.ALARM_Q2_RATE> 0 And Me.ALARM_Q2_RATE < 3) _
And Nz(Me.ALARM_Q2_REM, "") = "" Then
Me.ALARM_Q2_REM.BackColor = vbYellow ' or ???
Else
Me.ALARM_Q2_REM.BackColor = vbWhite
End If
End Sub

 

[Marshall Barton]

Thank you, by suggesting that I put this validation sequence on the
BeforeUpdate event it is in effect forcing the user to select a value for
ALARM_Q2_REM whether tabbing or selecting the combo box.

I have several alarm and remediation questions on this subform. How do I go
about evaluating each field? For example: I have ALARM_Q1_RATE and
ALARM_Q1_REM, ALARM_Q2_RATE and ALARM_Q2_REM, ALARM_Q3_RATE and ALARM_Q3_REM,
etc.

Just do the same kind of thing for each control.

That kind of code can get pretty tedious to write, but a
loop can only be used in some special cases. Your exanple
would be one because the control names are the same except
for a single, sequential numeric part and the validation is
the same.

For K = 1 To 3
If (Me("ALARM_Q" & K & "_RATE") > 0 _
And Me("ALARM_Q" & K & "_RATE < 3) _
And Nz(Me("ALARM_Q" & K & "_REM, "") = "" Then
MsgBox "fill in the Alarm " & K & " values."
End If
Next K

 

[Caburky via AccessMonster.com]

Marshall,
I'm sorry not to have gotten back to you sooner, I was home with the flu.
Well, back to work. Thank you for the suggestion. I'm still a novice when
it comes to VBA, but after a struggle I found that the code below worked well
with a little tweaking with the )'s. It validated each of the fields
correctly and did not allow the user to leave the subform until they had
entered a rememediation value.

Thank you again, I couldn't have done it without you!


Dim k As Integer
'Loop through each control on the form
For k = 1 To 7
If (Me("ALARM-Q" & k & "_RATE") > 0 _
And Me("ALARM-Q" & k & "_RATE") < 3 _
And Nz(Me("ALARM-Q" & k & "_REM"), "") = "") Then

MsgBox "Alarm " & k & " requires a Remediation Value!"
Cancel = True
End If

Next k

Thank you, by suggesting that I put this validation sequence on the
BeforeUpdate event it is in effect forcing the user to select a value for

[quoted text clipped - 4 lines]

ALARM_Q1_REM, ALARM_Q2_RATE and ALARM_Q2_REM, ALARM_Q3_RATE and ALARM_Q3_REM,
etc.

Just do the same kind of thing for each control.

That kind of code can get pretty tedious to write, but a
loop can only be used in some special cases. Your exanple
would be one because the control names are the same except
for a single, sequential numeric part and the validation is
the same.

For K = 1 To 3
If (Me("ALARM_Q" & K & "_RATE") > 0 _
And Me("ALARM_Q" & K & "_RATE < 3) _
And Nz(Me("ALARM_Q" & K & "_REM, "") = "" Then
MsgBox "fill in the Alarm " & K & " values."
End If
Next K