Prevent user from moving to another control 1

[caburky]

I have two simple functions on a control; 1 changes the backcolor of the control and requires a value to be input if criteria in another control equals a specific value, and the second to reset the backcolor on exit. My question is this: Is there a way to prevent the user from exiting the control that has the focus if they don't enter a value?

Private Sub ALARM_Q3_REM_GotFocus()
If (ALARM_Q3_RATE) < 3 Then
MsgBox "A rating of 1 or 2 requires remediation. " & _
"Use REM column to indicate method used!", vbOKOnly, "Remeditation Required"
Me.ALARM_Q3_REM.BackColor = RGB(255, 0, 0)
Me.ALARM_Q3_REM.SetFocus
End If
End Sub

Private Sub ALARM_Q3_REM_Exit(Cancel As Integer)
If Not IsNull(Me.ALARM_Q3_REM) Then
Me.ALARM_Q3_REM.BackColor = 16777215
End If
End Sub

 

[lameid]

I would think something like the following would work. Although I have never thought of cancelling the exit event and only did because your example listed the parameter. If that does not work you could set a boolean scoped to the procedure and try to test the value if it is true on events and then set the focus back on the control.

Private Sub ALARM_Q3_REM_Exit(Cancel As Integer)
    If Not IsNull(Me.ALARM_Q3_REM) Then
        Me.ALARM_Q3_REM.BackColor = 16777215
    Else
        Msgbox "You must enter a value to continue."
        Cancel = True
    End If
End Sub

 

[caburky]

lameid thanks for your help! This did prevent me from going any further until a value was added. However, once I did add the value I could not stop the msgbox.

I did not understand what you meant by set a boolean scoped to the procedure and try to test the value if it is true on events and then set the focus back on the control. Would you please elaborate.

 

[lameid]

I meant scoped to the module not the procedure...

Private blALARM_Q3_REMNoContinue as boolean
Private Sub ALARM_Q3_REM_Exit(Cancel As Integer)
    If Not IsNull(Me.ALARM_Q3_REM) Then
        Me.ALARM_Q3_REM.BackColor = 16777215
    Else
        blALARM_Q3_REMNoContinue
    End If
End Sub

Of course you also have to initialize the variable On Open and write code to test it.

Although you might have luck with...

Private Sub ALARM_Q3_REM_Exit(Cancel As Integer)
    If Nz(Me.ALARM_Q3_REM.textvalue, "") <> "" Then
        Me.ALARM_Q3_REM.BackColor = 16777215
    Else
        Msgbox "You must enter a value to continue."
        Cancel = True
    End If
End Sub

It seems like Exit fires before the update events, interesting. I don't think that has come up for me before.

 

[caburky]

I really appreciate you taking the time to help me out with this.

I seem to have lost the OnGotFocus functionality with the change to the OnExit.

Maybe to spell it out would help...

If ALARM_Q1_RATE >= 3 then jump to ALARM_Q2_RATE, else
If ALARM_Q1_RATE <3 then set the focus to ALARM_Q1_REM display the remediation message, turn the backcolor red and prevent them from leaving unless they enter a value. After the value is entered reset the backcolor.

I hope this makes sense.


>>>>>>>>
Private Sub ALARM_Q1_REM_GotFocus()
If Me.ALARM_Q1_RATE >= 3 Then

If Me.ALARM_Q1_RATE < 3 Then
MsgBox "A rating of 1 or 2 requires remediation. " & _
"Use REM column to indicate method used!", vbOKOnly, "Remeditation Required"
Me.ALARM_Q1_REM.BackColor = RGB(255, 0, 0)
Me.ALARM_Q1_REM.SetFocus
End If
Else
Me.ALARM_Q2_RATE.SetFocus
End If
End Sub

Private Sub ALARM_Q1_REM_Exit(Cancel As Integer)
If Nz(Me.ALARM_Q1_REM.Text, "") <> "" Then
Me.ALARM_Q1_REM.BackColor = 16777215
Else
MsgBox "You must enter a value to continue."
Cancel = True
End If
End Sub

 

[lameid]

You are saying that the Gotfocus event for the same control does not seem to run now that you have updated the exit event?

If your answer is yes, I got what you are saying. That should not be the case, try compacting and repairing the database.

 

[caburky]

Yes, that is what was happening. Compacted and repaired.

I modified the code slightly and the first IF fires OnExit fine. What is happening with the Else is this: When I enter a value of 2 in ALARM_Q1_RATE then I am prompted for a ALARM_Q1_REM value, which is correct. However, when I DO NOT enter ALARM_Q1_REM value it allows me to tab to the next control. If I DO enter ALARM_Q1_REM value, which I should, then I continually get the message that a value is required to continue. It seems to work, but backwards.

Private Sub ALARM_Q1_REM_Exit(Cancel As Integer)
If (Me.ALARM_Q1_RATE.Value) >= 3 And IsNull(Me.ALARM_Q1_REM.Value) Then
Me.ALARM_Q1_REM.BackColor = 16777215
Else
If (Me.ALARM_Q1_RATE.Value) < 3 And Nz(Me.ALARM_Q1_REM.Value, "") <> "" Then
MsgBox "You must enter a value to continue."
Cancel = True
End If
End If
End Sub

Private Sub ALARM_Q1_REM_GotFocus()
If (Me.ALARM_Q1_RATE.Value) < 3 Then
MsgBox "A rating of 1 or 2 requires remediation. " & _
"Use REM column to indicate method used!", vbOKOnly, "Remeditation Required"
Me.ALARM_Q1_REM.BackColor = RGB(255, 0, 0)
Me.ALARM_Q1_REM.SetFocus
End If
'End If
End Sub

 

[lameid]

I think you intended something like...

Private Sub ALARM_Q1_REM_Exit(Cancel As Integer)
    If (Me.ALARM_Q1_RATE.Value) >= 3 [COLOR=Red]OR[/Color Red] IsNull(Me.ALARM_Q1_REM.Value) Then
        Me.ALARM_Q1_REM.BackColor = 16777215
    Else
        MsgBox "You must enter a value to continue."
        Cancel = True
    End If
End Sub

However to allow for problems form nulls, I would go with...

Private Sub ALARM_Q1_REM_Exit(Cancel As Integer)
    If (Me.ALARM_Q1_RATE.Value) < 3 And _
      Nz(Me.ALARM_Q1_REM.Value, "") <> "" Then

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

    End If
End Sub

I only used the line continuation (_) because it wraps in this little window... you might take it out and put it on one line.

 

[caburky]

lameid, I must not be seeing the forest for the trees. I tried both of your suggestions.

What it is doing:
If RATE is <3 it fires off the backcolor change and the "remediation message", but I am able to tab to the next control without entering a REM value.

However, if the RATE is <3 and I enter a REM value it will not let me out of the control and keeps firing the "cannot continue message" repeatedly until I close the form.

 

[ProgramError]

You may like to set the 16777215 background colour as a constant in the top of your module using

const acBGcolour = 16777215

Substitute 16777215 in your procedures with acBGcolour. Then you can change any references to the background colour by just changing the value.
Your code will be easier to read because acBGcolour is more informative, whereas 16777215 isn't quite so obvious.

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[ProgramError]

If ALARM_Q1_RATE >= 3 then jump to ALARM_Q2_RATE, else
If ALARM_Q1_RATE <3 then set the focus to ALARM_Q1_REM display the remediation message, turn the backcolor red and prevent them from leaving unless they enter a value. After the value is entered reset the backcolor.

you may want to try this code (untested)

const acRed = RGB(255, 0, 0)
const acNormalBGcolour = 16777215

'check if rate is greater or equals 3 if so then rate needs changing otherwise wait until a number to be entered in rem

Private Sub ALARM_Q3_REM_GotFocus()
    If ALARM_Q3_RATE > 2 or ALARM_Q3_RATE < 1 or Then
        Me.ALARM_Q3_RATE.SetFocus
    else
        MsgBox "A rating of 1 or 2 requires remediation. " & "Use REM column to indicate method used!", vbOKOnly,"Remeditation Required"        
        Me.ALARM_Q3_REM.BackColor = acRed
    End If
End Sub

'prevent them from leaving unless they enter a value
'otherwise
'After the value is entered reset the backcolor.

Private Sub ALARM_Q1_REM_Exit()
    If Is Null(Me.ALARM_Q1_REM) Then
       MsgBox "Value required to continue!"
       Me.ALARM_Q1_REM.SetFocus
    else
       Me.ALARM_Q3_REM.BackColor = acNormalBGcolour
    End If
End Sub

You may not need this but if you want them to enter a specific value in REM then add the following

const UpperValue = 3
const LowerValue = 0
Private sub ALARM_Q1_REM_AfterUpdate()
if  Me.ALARM_Q1_REM.Value > LowerValue and  Me.ALARM_Q1_REM.Value < UpperValue and not(is null(Me.ALARM_Q1_RATE.Value))) then

Else
    Msgbox "You must enter a correct value to continue."
     Me.ALARM_Q1_REM.SetFocus
Endif
end sub

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[lameid]

Somewhere I made a mistake or didn't have it straight. Only if it is blank do you care about the rate value.

I think that one change it red will make all the difference.

Private Sub ALARM_Q1_REM_Exit(Cancel As Integer)
    If (Me.ALARM_Q1_RATE.Value) < 3 AND _
      Nz(Me.ALARM_Q1_REM.Value, "") [COLOR=Red]=[/Color Red] "" Then

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

    End If
End Sub

 

[caburky]

lameid if you were here I'd kiss you. Thank you, thank you, thank you, it's perfect!!!