Solved SetFocus Issue (1 Viewer)

mloucel

Member
Local time
Today, 12:36
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.
 
Since you seem to be trying to do some sort of data validation, perhaps you should consider using the control's BeforeUpdate event instead.
That's right, and rather than DoCmd.CancelEvent set the Cancel argument to True.
 
Since you seem to be trying to do some sort of data validation, perhaps you should consider using the control's BeforeUpdate event instead.
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.
 
Already did, if you see the code I sent I did already, it was worst, the code never executed it just went right by..
Did you have the event property contain [Event Procedure]?

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

Are you able to post a sample db?
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
 
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
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
 
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.
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.
 
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.

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
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.
 
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.
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

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
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.
 
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.
I found some of those suggestions on other threads about a similar issue to mine in this forums.
 
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.
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.
 
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.
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?
 
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.
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.. :) :)
 

Users who are viewing this thread

Back
Top Bottom