I use the following workaround:
Before setting the focus to the current control, set the focus to another control, it doesn't matter which one.
Example:
Code:
Private Sub AuthDateEntered_LostFocus()
'Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
If AuthDateEntered < ReferDate Then
Me.AuthDateEntered.BorderColor = vbRed
MsgBox "This date cannot be before the referral date" & _
vbCrLf & _
"Please Correct before continue", vbCritical + vbOKOnly, "Error detected"
DoEvents
'Cancel = True
Me!OTHERCONTROL.SetFocus
Me!AuthDateEntered.SetFocus
DoCmd.CancelEvent
Else
Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
[QUOTE="CJ_London, post: 1923417, member: 117916"]
before update the AuthDateEntered control will hold the previous value. You need to use the .text property
[CODE]Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
If AuthDateEntered.text < ReferDate Then
since these are dates and the control is text, you may need
In this case, I set the focus to the OTHERCONTROL control before resetting it to the AuthDateEntered control.
[/QUOTE]
SIR.. Hats Off to you
IT WORKED..
Just the way I wanted it, this is pure genius, thank you so much, I set the focus first to ReferDate, followed with the ADE, it works.. PROBLEM SOLVED..
I got a runtime error 2185
you can't reference a property or method for a control unless the control has the focus.
upon debug:
goes straight to this line
If CDate(AuthDateEntered.Text) < CDate(ReferDate.Text) Then
But I was able to solve the issue, user @xavier.batlle gave me a tip, set the focus to a different control, then immediately set the focus to the control I want, so I did:
Me!ReferDate.SetFocus
Me!AuthDateEntered.SetFocus
and it works ..
Data validation NEEDS to be done in the before update event of the form. Regardless if you support it with other events. It is a catch all. As stated below
You fail to understand the difference between the Form_BeforeUpdate and the controls BeforeUpdate. Those are two different things, see the demo I attached which does work as you desire to prevent the record from saving with incorrect data. You will see that the code is for the whole form and not just one control. Did you try the demo?
The adopted solution can fail easily without this final trap.
Put in a ref date of 1/1/2024 and a auth date of 1/2/2024. All is good. Put now you realize the ref date is wrong and modify it to 1/11/2024. Your validation will not catch this. That is why use ALWAYS (not sometimes) validate in the FORMS before update.
However. You may want this to happen earlier. So in ADDITION (not a replacement) you can also validate in the controls before update.
This is the correct solution, modifying Mikes code to happen as soon as you try to leave the control and also when you try to move to next record. The lost focus solution is JUNK
Code:
Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
Cancel = cancelValidation
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = cancelValidation
If Cancel Then Me!AuthDateEntered.SetFocus
End Sub
Public Function cancelValidation() As Boolean
If IsNull(Me.AuthDateEntered) Then
Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
Exit Function
End If
If AuthDateEntered < ReferDate Then
Me.AuthDateEntered.BorderColor = vbRed
MsgBox "This date cannot be before the referral date" & _
vbCrLf & _
"Please Correct before continue", vbCritical + vbOKOnly, "Error detected"
cancelValidation = True
Else
Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
End If
End Function
The reason to do this in the Controls event (in addition) is so you cannot leave the control without fixing. Moving focus to another control then back is just bad.
Data validation NEEDS to be done in the before update event of the form. Regardless if you support it with other events. It is a catch all. As stated below
The adopted solution can fail easily without this final trap.
Put in a ref date of 1/1/2024 and a auth date of 1/2/2024. All is good. Put now you realize the ref date is wrong and modify it to 1/11/2024. Your validation will not catch this. That is why use ALWAYS (not sometimes) validate in the FORMS before update.
However. You may want this to happen earlier. So in ADDITION (not a replacement) you can also validate in the controls before update.
This is the correct solution, modifying Mikes code to happen as soon as you try to leave the control and also when you try to move to next record. The lost focus solution is JUNK
Code:
Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
Cancel = cancelValidation
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = cancelValidation
If Cancel Then Me!AuthDateEntered.SetFocus
End Sub
Public Function cancelValidation() As Boolean
If IsNull(Me.AuthDateEntered) Then
Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
Exit Function
End If
If AuthDateEntered < ReferDate Then
Me.AuthDateEntered.BorderColor = vbRed
MsgBox "This date cannot be before the referral date" & _
vbCrLf & _
"Please Correct before continue", vbCritical + vbOKOnly, "Error detected"
cancelValidation = True
Else
Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
End If
End Function
The reason to do this in the Controls event (in addition) is so you cannot leave the control without fixing. Moving focus to another control then back is just bad.
Thanks for your suggestion, I tried the code AS IS
NO error at compile
problem is that
Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
is not working and unless I go to different record then it works, which defies the whole thing.
leaving the code with the Lost Focus works as I need to.
Thanks for your suggestion, I tried the code AS IS
NO error at compile
problem is that
Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
is not working and unless I go to different record then it works, which defies the whole thing.
leaving the code with the Lost Focus works as I need to.
You are doing something wrong. Works as described. You cannot leave the control if validation fails and happens as soon as you try to leave the control.
