Returning Focus to a textbox on an access form using On Exit Event

[Pat1362]

I have recently returned to programming in MS Access after almost 13 years. I am in the process of creating a small membership application and have come up agains the following problem.



I am coding a form to allow the user add a new member to the database. I have a text box on the form (txtMemNo) where user enters the Membership Number and I'm using the OnExit event of the text box to validate the entry. The validation works fine, my problem is that when it fails I want to return the focus to txtMemNo but my code below doesn't work.

Private Sub txtMemNo_Exit(Cancel As Integer)
Dim strNumber As String
Dim blnNumber As Boolean
If txtMemNo.Text <> "" Then 'check that something has been entered in textbox
strNumber = txtMemNo.Value 'populate the string variable
blnNumber = IsNumeric(strNumber) 'numeric check
    If blnNumber = True Then
    MsgBox ("Checking if membership number already exists")
    'Code here to call routine to check if membership number exists
    Else
    MsgBox ("Please enter a number") 'Error message
    
    txtMemNo.Text = "" 'clear text
    txtMemNo.SetFocus 'return cursor to text box
    End If
End If
End Sub

If I tab out of txtMemNo the focus moves to the text box that is next in tab order and if I click out of it focus remains in the textbox I click into. I'd be grateful for any advice on how to fix this. TIA.

[Bob Fitz]

The "BeforeUpdate" would be a better place for validation code. If the validation fails the update can be cancelled.

See: https://docs.microsoft.com/en-us/off...reupdate-event

[Pat1362]

Hi Bob,

Both the form and the textbox are unbound so cancelling an update is not the issue. My problem is returning focus to the text box when validation fails.

[Bob Fitz]

Hi Bob,

Both the form and the textbox are unbound so cancelling an update is not the issue. My problem is returning focus to the text box when validation fails.

The BeforeUpdate event is the best place to handle validation. Try something like:

If Not IsNumeric(Me.ActiveControl) Then    
    MsgBox ("Please enter a numeric value")
    Cancel = True
End If

[Bob Fitz]

If you use the BeforeUpdate event and cancel if the validation fails, then the focus never leaves the control.

[Micron]

Agree with Bob. After LostFocus event for controls happens after Exit, so you're setting focus for 1 microsecond in Exit code and then LostFocus occurs.
Two things I'd rethink:
- making users dismiss the message box, especially since the check cannot happen until it's dismissed. Just run it an message if there's a problem
- IsNumeric(12a) should raise an error and you have no error handler. IsNumeric(a12) will return True. There's really no need for this test. Use DLookup for whatever value is entered, which will return a valid value or not. It's really that simple I think.

[Pat1362]

Ok that part I can understand. If I place the code you posted above in the BeforeUpdate event I get a compile error saying that I've "used an End If without a block If" ??

[Micron]

Post your code. Please use code tags (# on posting toolbar) and indentation. Or look for which END IF has no IF and fix that. Could also happen with nested blocks such as IF nested in WITH.

[Pat1362]

Hi Micron, my comment about compile error was not in reference to my own code but the code Bob had posted. I have a good understanding of basic coding principles as I worked as a database programmer on an IBM AS400 system for many years. I'm just a little rusty on vba especially with regard to the sequence in which control events happen as I haven't used it for such a long time. Anyway I have decided to implement the validation when the user clicks an update button as it's easier to set the focus at that point. Thanks to both you and Bob for taking the time to reply to my post.

[Micron]

I saw nothing wrong with the If / End If part of his code so I figured your error had something to do with how you incorporated it. Glad you solved it, and post back if you need anything else.