Solved SetFocus Issue

mloucel

mloucel

Member
Local time
Yesterday, 20:40
Joined
Aug 5, 2020
Messages
309
Hello ALL
I have gone through several threads about this issue, and still I can't get it right..

I have a form that requires if the user enter today's date or any other date in ReferDate then the next field "AuthDateEntered" must be > to ReferDate and move to a field XXXX

I tried this:
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!AuthDateEntered.SetFocus
        DoCmd.CancelEvent
    Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub

but NO MATTER what I do, the dam thing keeps moving to the field XXXX after AuthDateEntered.

I have tried all the different solutions I have found here to keep the focus at AuthDateEntered and the instruction is simply ignored just before the End Sub.

I have stop the Tabs, change the name, using no date picker, using the picker, using a different picker, just simply I am at the end of the rope.

Form IS SINGLE bound to a Query, and the cycle is set to current record.

Any Ideas will be appreciated.
 
Since you seem to be trying to do some sort of data validation, perhaps you should consider using the control's BeforeUpdate event instead.
 
theDBguy said:
Since you seem to be trying to do some sort of data validation, perhaps you should consider using the control's BeforeUpdate event instead.
Click to expand...
That's right, and rather than DoCmd.CancelEvent set the Cancel argument to True.
 
theDBguy said:
Since you seem to be trying to do some sort of data validation, perhaps you should consider using the control's BeforeUpdate event instead.
Click to expand...
Already did, if you see the code I sent I did already, it was worst, the code never executed it just went right by..

'Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)

hence the reason I sent the code, I have no idea what to do, as well @tvanstiphout check the code, that was my first try, total failure, that code was not even executed, it was completely ignored, I tried
Before Update
on change
the one that got close and at least I know works is the lost focus I can see the Border Color go on effect.
IT Does do the set focus event, but right before the End Sub the next control got focus and I am dead meat.

Docmd.CancelEvent was suggested as well in this forum in another place, so I just added the line, heck didn't do any harm.
 
mloucel said:
Already did, if you see the code I sent I did already, it was worst, the code never executed it just went right by..
Click to expand...
Did you have the event property contain [Event Procedure]?

Are you able to post a sample db?
 
theDBguy said:
Did you have the event property contain [Event Procedure]?

Are you able to post a sample db?
Click to expand...
Did you have the event property contain [Event Procedure]?
I do really hope yes..

Are you able to post a sample db?
No, the forum sort of block me even with a DB containing only the bare table I use and the form, seems like even zipping the file is way too big, and I fully understand the procedure of being careful and all that, I will try to reduce everything a bit more, compact repair deleting all data (1 record) let me see if I can do it.
 
A few thoughts - I have not looked back over the previous attempts other than the above.
1. Try using the code on the BeforeUpdate event of the Form (along with any other validation checks on data entered in the form), not the Control. Also using the me. reference as the record may/will not be an existing record and so the data to validate is the data on the form and its controls, not in the record. Not tested (obviously).
Code: 
If isnull(Me.AuthDateEntered) then
    Msgbox "Need an AuthDate", ....
    me.AuthDateEntered.bordercolor = vbred
    me.AuthDateEntered.setfocus
else
    If isnull(Me.referDate (???)) then  ' I assume it is on this form
        Msgbox "ReferDate is required?", ...
        me.ReferDate.bordercolor = vbred
        me.ReferDate.setfocus   
    Else
        If me.AuthAateEntered < me.ReferDate Then
            Me.AuthDateEntered.BorderColor = vbRed
            MsgBox "This date cannot be before the referral date" & _
                vbCrLf & _
                "Please Correct before continue", vbCritical + vbOKOnly, "Error detected"
             Cancel = True
            Me.AuthDateEntered.SetFocus
        Else
            Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
           End If
    End if
end if
2. You also need to account for when the ReferDate has not been provided/entered - ie null
 
GaP42 said:
A few thoughts - I have not looked back over the previous attempts other than the above.
1. Try using the code on the BeforeUpdate event of the Form (along with any other validation checks on data entered in the form), not the Control. Also using the me. reference as the record may/will not be an existing record and so the data to validate is the data on the form and its controls, not in the record. Not tested (obviously).
Code: 
If isnull(Me.AuthDateEntered) then
    Msgbox "Need an AuthDate", ....
    me.AuthDateEntered.bordercolor = vbred
    me.AuthDateEntered.setfocus
else
    If isnull(Me.referDate (???)) then  ' I assume it is on this form
        Msgbox "ReferDate is required?", ...
        me.ReferDate.bordercolor = vbred
        me.ReferDate.setfocus 
    Else
        If me.AuthAateEntered < me.ReferDate Then
            Me.AuthDateEntered.BorderColor = vbRed
            MsgBox "This date cannot be before the referral date" & _
                vbCrLf & _
                "Please Correct before continue", vbCritical + vbOKOnly, "Error detected"
             Cancel = True
            Me.AuthDateEntered.SetFocus
        Else
            Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
           End If
    End if
end if
2. You also need to account for when the ReferDate has not been provided/entered - ie null
Click to expand...
I didn't try the form, I've been always thinking that I need to trap the property itself since I thought the form will do something else, let me try this first and see if it works, I will create a copy to try..

Complete Ignore I comment out the whole block of my original code and left yours [with the corresponding corrections of course] and nothing

I will try to post the DB
 
Last edited:
Sorry I've tried making the file to barebones and still got the file is too large error, [Even zipped] I don't want to put a link to the file, since I got into troubles in the forum last time.
I'm out of Ideas, I know many of you will simply find my error in just 10 seconds, I honestly envy all of you.
 
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

if cDate(AuthDateEntered.text)< ReferDate then
 
mloucel said:
Sorry I've tried making the file to barebones and still got the file is too large error, [Even zipped] I don't want to put a link to the file, since I got into troubles in the forum last time.
I'm out of Ideas, I know many of you will simply find my error in just 10 seconds, I honestly envy all of you.
Click to expand...
Have you tried creating a new db and importing the relevant form, query and table. Delete all records in the new db and then create a couple of fictitious records. Compact, zip and post the file.
 
mloucel said:
Hello ALL
I have gone through several threads about this issue, and still I can't get it right..

I have a form that requires if the user enter today's date or any other date in ReferDate then the next field "AuthDateEntered" must be > to ReferDate and move to a field XXXX

but NO MATTER what I do, the dam thing keeps moving to the field XXXX after AuthDateEntered.

I have tried all the different solutions I have found here to keep the focus at AuthDateEntered and the instruction is simply ignored just before the End Sub.

I have stop the Tabs, change the name, using no date picker, using the picker, using a different picker, just simply I am at the end of the rope.

Form IS SINGLE bound to a Query, and the cycle is set to current record.

Any Ideas will be appreciated.
Click to expand...

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)
    End If
End Sub

In this case, I set the focus to the OTHERCONTROL control before resetting it to the AuthDateEntered control.
 
already did, if you see the code I sent I did already, it was worst, the code never executed it just went right by..

'Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)

hence the reason I sent the code, I have no idea what to do, as well @tvanstiphout check the code, that was my first try, total failure, that code was not even executed, it was completely ignored, I tried
Before Update
on change
Click to expand...
Need to learn to debug your own code. Msgboxes or debugs is one of the easiest.
There are lots of possibilities why this is happening

Code: 
'Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
   ' 1. Verify the code is being called might forgot [Event Procedure]
     msgbox "before Update called"
   ' 2. Verify the values are correct and as you think
     msgbox "Auth: " & authdateEntered & " Refer " & ReferDate & " And is less:  "  & AuthDateEntered < ReferDate
   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"
        Cancel = True
        Me!AuthDateEntered.SetFocus ' probably not needed
     ' 3. Verify focus set
        msgbox "Has focus: " & me.activecontrol.name
 Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub

Either the code is not being called, the values are not what you think, the set focus worked, but following the code it was lost.
 
mloucel said:
Hello ALL
I have gone through several threads about this issue, and still I can't get it right..

I have a form that requires if the user enter today's date or any other date in ReferDate then the next field "AuthDateEntered" must be > to ReferDate and move to a field XXXX

I tried this:
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!AuthDateEntered.SetFocus
        DoCmd.CancelEvent
    Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub

but NO MATTER what I do, the dam thing keeps moving to the field XXXX after AuthDateEntered.

I have tried all the different solutions I have found here to keep the focus at AuthDateEntered and the instruction is simply ignored just before the End Sub.

I have stop the Tabs, change the name, using no date picker, using the picker, using a different picker, just simply I am at the end of the rope.

Form IS SINGLE bound to a Query, and the cycle is set to current record.

Any Ideas will be appreciated.
Click to expand...
It appears to me that you have set the focus properly (I would use Me.AuthDateEntered.SetFocus) and then cancelled it with DoCmd.CancelEvent. And why are you using the DoEvents command? I doubt you need that.
 
This worked for me. Here I removed what was unnecessary to show that it works as is. To handle the null, I just exit the sub which allows a null value but doesn't try and validate in that circumstance. Very simple code, but it works. Notice this event is in the forms BeforeUpdate event. See attached example file.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.AuthDateEntered) Then
        Exit Sub
    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"
        Me!AuthDateEntered.SetFocus
        Cancel = True
    Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub
 

Attachments

Mike Krailo said:
This worked for me. Here I removed what was unnecessary to show that it works as is. To handle the null, I just exit the sub which allows a null value but doesn't try and validate in that circumstance. Very simple code, but it works. Notice this event is in the forms BeforeUpdate event. See attached example file.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.AuthDateEntered) Then
        Exit Sub
    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"
        Me!AuthDateEntered.SetFocus
        Cancel = True
    Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub
Click to expand...
Thanks
I had already tested before the code as you kindly proposed, the problem is that the code is executed once either you try to close the form or move to a different record, then yes it works.
My issue is that I have to FORCE the user to correct the date before they continue, I have some very interesting characters, some will ask me to go to their desk and correct the error for them, YES IT HAPPENS, so I've been forced to do things in a different way.
Thank you so much.
 
LarryE said:
It appears to me that you have set the focus properly (I would use Me.AuthDateEntered.SetFocus) and then cancelled it with DoCmd.CancelEvent. And why are you using the DoEvents command? I doubt you need that.
Click to expand...
I found some of those suggestions on other threads about a similar issue to mine in this forums.
 
MajP said:
Need to learn to debug your own code. Msgboxes or debugs is one of the easiest.
There are lots of possibilities why this is happening

Code: 
'Private Sub AuthDateEntered_BeforeUpdate(Cancel As Integer)
   ' 1. Verify the code is being called might forgot [Event Procedure]
     msgbox "before Update called"
   ' 2. Verify the values are correct and as you think
     msgbox "Auth: " & authdateEntered & " Refer " & ReferDate & " And is less:  "  & AuthDateEntered < ReferDate
   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"
        Cancel = True
        Me!AuthDateEntered.SetFocus ' probably not needed
     ' 3. Verify focus set
        msgbox "Has focus: " & me.activecontrol.name
Else
        Me.AuthDateEntered.BorderColor = RGB(166, 166, 166)
    End If
End Sub

Either the code is not being called, the values are not what you think, the set focus worked, but following the code it was lost.
Click to expand...
I just did, but using the lost focus, which is what I need.
I have to force the user to update that even before they continue, the code works, but at the very last second once the code do the END SUB the pointer moves to the next field.
 
mloucel said:
I had already tested before the code as you kindly proposed, the problem is that the code is executed once either you try to close the form or move to a different record, then yes it works.
Click to expand...
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?
 
xavier.batlle said:
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)
    End If
End Sub

In this case, I set the focus to the OTHERCONTROL control before resetting it to the AuthDateEntered control.
Click to expand...
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.. :) :)
 
You must log in or register to reply here.

Similar threads

mloucel
Solved Refresh a query thru VBA
Replies
7
Views
191
mloucel
mloucel
S
How do I turn RGB color statements into variables?
Replies
12
Views
941
theDBguy
theDBguy
M
Solved Date Picker value depending on another control
Replies
7
Views
847
mafhobb
M
C
Cancel closing form without error message
Replies
11
Views
355
Pat Hartman
P
C
Option group not working
Replies
17
Views
177
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom