SetFocus problem; goes to next field instead 2

[sxschech]

This code is supposed to alert user and allow them to change the value if the last two characters are not a 20 or a 40. Instead of staying in the field, the cursor moves to the next field. When I stepped through the code, it seemed to be working (sets focus and highlights portion of text to be edited), however when code hits End Sub, then focus moved to next field.

Private Sub txtCurrentTerm_AfterUpdate()

    If Right(Me.txtCurrentTerm, 2) <> "20" Or Right(Me.txtCurrentTerm, 2) <> "40" Then
        Me.txtCurrentTerm.SetFocus
        Me.txtCurrentTerm.SelStart = 4
        Me.txtCurrentTerm.SelLength = 2  ' Length of selection
        MsgBox Me.txtCurrentTerm & " is not a valid term.  Please enter term as a 4 digit year and " & _
                                   "academic period as 20 for Spring or 40 for Summer and Fall. " & vbCrLf & _
                                   "Example: 200840", vbOKOnly, "Invalid Term/Academic Period"
        
    End If
End Sub

 

[pbaldy]

Generally validation should be done in the before update event, which can be canceled. Try it there, deleting the set focus line and replacing it with

Cancel = True

Also, logically I think you're going to want And rather than Or in your test.

Paul
MS Access MVP 2007/2008

 

[TheAceMan1]

How are ya sxschech . . .

In the txtCurrentTerm [blue]BeforeUpdate[/blue] event, copy/paste the following (be sure to disable or remove the code in the AfterUpdate event):

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim NL As String, DL As String, ctl As Control
   
   Set ctl = Me.txtCurrentTerm
   Style = vbInformation + vbOKOnly
   NL = vbNewLine
   DL = NL & NL
   
   If Len(ctl) <> 6 Then
      Msg = "'Term' has too few or too many digits!" & DL & _
            "Exactly 6 digits are required!"
      Title = "Character Length Error! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
      ctl.SelStart = 0
      ctl.SelLength = Len(ctl)
   ElseIf InStr(1, "2040", Right(ctl, 2)) = 0 Then
       Cancel = True
       ctl.SelStart = 4
       ctl.SelLength = 2  ' Length of selection
       Msg = ctl & " is not a valid term. " & DL & _
            "Please enter term as a 4 digit year and " & _
            "academic period as 20 for Spring or 40 for Summer and Fall." & DL & _
            "Example: 200840"
      Title = "Invalid Term/Academic Period"
   End If
   
   Set ctl = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sxschech]

Hi Guys,

Sorry for the delay, catching up from the holiday...
Thanks both suggestions worked.


Since the other part of the code was working and didn't want to include more than necessary I left this part out of the request. I have an if statement that automatically sets the txtCurrentTerm to 40 if the user enters 30 so the user won't see an error message or have to type it. With the change to before update, this now generates a run-time error '-2147352567 (80020009)' "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field." Any thoughts where this code should go or be rewritten to handle this aspect?

If Right(Me.txtCurrentTerm, 2) = "30" Then
        Me.txtCurrentTerm = Replace(Me.txtCurrentTerm, "30", "40")
        Me.chkFall = True
End If

 

[pbaldy]

I don't think you can change the value in the control's before update event. I would tweak the validation in the before update event to allow the 30, and put that code in the after update event to change it to 40.

Paul
MS Access MVP 2007/2008

 

[sxschech]

Thanks pbaldy, added another if statement and put that portion of code in the afterupdate event.