SetFocus and Select Text

[sxschech]

I am using the following to Select the textbox when the date is before another date. I thought it wasn't working. However when I stepped through the code it does select the text until the End Sub and then becomes unselected. I have this in the after update event. Is this the right spot? Or should I use another technique to highlight the textbox for user to decide whether to adjust the date.

If Me.tb_DateTaken < dtRWP Then
        MsgBox "Training Date of " & Me.tb_DateTaken & " is before the RWP date of " & dtRWP & ".  Please verify this is correct or enter a date after the RWP date.", vbOKOnly + vbQuestion, "Date Issue"
        Me.tb_DateTaken.SetFocus
        Me.tb_DateTaken.SelStart = 0
        Me.tb_DateTaken.SelLength = Len(Me.tb_DateTaken)
    End If
End Sub

DateSelected is how it looks prior to End Sub and DateNotSelected is how it looks when the code has finished.

 

[June7]

I use Enter or Click event of control that gets focus to set itself.

Which textbox AfterUpdate has this code?

 

[sxschech]

I have this in the after update of the field in question: Me.tb_DateTaken

 

[June7]

Use BeforeUpdate event to validate data.

 

[sxschech]

Changed the code as you advised to BeforeUpdate. Same result, after the End Sub the selection goes away.

Private Sub tb_DateTaken_BeforeUpdate(Cancel As Integer)
Dim dtRWP As Date
    
    dtRWP = DLookup("EmployeeTraining_DateTaken", "tbl_dt_EmployeeTraining", "EmployeeTraining_EmployeeID=" & Me.EmployeeTraining_EmployeeID & " AND EmployeeTraining_TrainingTypeID = 6")
   
    If Me.tb_DateTaken < dtRWP Then
        MsgBox "Training Date of " & Me.tb_DateTaken & " is before the RWP date of " & dtRWP & ".  Please verify this is correct or enter a date after the RWP date.", vbOKOnly + vbQuestion, "Date Issue"
        
        Me.tb_DateTaken.SelStart = 0
        Me.tb_DateTaken.SelLength = Len(Me.tb_DateTaken)
    End If
End Sub

 

[June7]

And does control lose focus?

Need use:

Cancel = True

If you want control to retain focus.

 

[sxschech]

Would Cancel remove the entry? They don't want to undo the entry since there are exceptions, it is only for information purposes and I thought would be helpful if the textbox would be highlighted so user can decide if the date needs to be revised. Maybe tomorrow I'll try a different approach and see about placing a rectangle around the date and then hide/unhide it?

 

[June7]

Does not remove entry. Try it.

If you want to allow user to keep the entry, then probably need a nested If Then:

If MsgBox("Do you want edit date?", vbYesNo) = vbYes Then
Cancel = True
…
End If

 

[sxschech]

I'll try tomorrow when back in the office.

 

[arnelgp]

rivate Sub tb_DateTaken_BeforeUpdate(Cancel As Integer)
Dim dtRWP As Date
    
    dtRWP = DLookup("EmployeeTraining_DateTaken", "tbl_dt_EmployeeTraining", "EmployeeTraining_EmployeeID=" & Me.EmployeeTraining_EmployeeID & " AND EmployeeTraining_TrainingTypeID = 6")
   
    If Me.tb_DateTaken < dtRWP Then
        MsgBox "Training Date of " & Me.tb_DateTaken & " is before the RWP date of " & dtRWP & ".  Please verify this is correct or enter a date after the RWP date.", vbOKOnly + vbQuestion, "Date Issue"
        [COLOR="Blue"]Cancel = True[/COLOR]
        Me.tb_DateTaken.SelStart = 0
        Me.tb_DateTaken.SelLength = Len(Me.tb_DateTaken[COLOR="blue"].Text[/COLOR])
    End If
End Sub

 

[sxschech]

Getting closer, thanks June7 and arnelgp. After adding your code, it does keep the highlight and the date just like you said. The new issue is it puts up a message box "The value violates the validation rule for the field or record". Since the user has already been prompted that there is an issue, this secondary message is not needed. I put in an error handler and stepped through the code and like before the error message pops up after the code has finished (Pressing F8 on End Sub). It does not seem to be generating an error while in the code as I did a debug.print just before the End Sub for err.Number and it was 0.

 

[arnelgp]

capture the Error number.
add code the the Form's Error Event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 'the error number here'
    ' already handled
    Response = acDataErrContinue
End If
End Sub

 

[June7]

Post your code. Did you consider the nested If Then I suggested?

What Validation Rule did you set?

 

[sxschech]

Thanks arnelgp. That sorted it out.

Thanks June7 for your suggestions/advice and code. I tried both your version and arnelgp's and since they both returned the same result, decided to go with arnelgp's since his worked without the nested If.

 

[June7]

Interesting, I did not get error in my test. Did not have ValidationRule property set either.