Why doesn't my SetFocus work?

[Petrol]

I have several forms containing bound controls in which a phone number is to be entered. Because I want the saved text string to include spaces here and there (e.g. 07 1234 5678 for land lines, or 0412 345 678 for mobiles), I have a function which reformats the given text string. Since the control is bound to the requisite control source, the reformatted phone number is automatically stored in the record. The function is called from the AfterUpdate procedure of the control, and works well.
If, however, the user enters an invalid string (e.g. nonnumeric characters, or wrong number of digits), the functon outputs an error message asking the user to re-enter it, and returns the word "Invalid". To avoid the risk of the user missing the message and moving on, I then want to keep the focus on the same control. However the SetFocus command doesn't work (or perhaps it works but then is immediately overridden) and the focus moves to control with the the next tab stop. Is there any way I can fix this? (The control is, of course, enabled and unlocked).

Private Sub txtHome_phone_AfterUpdate()
'
'   Checks  and standardises format of home phone numbers.  It will accept them either with or without area codes.
'
Me!txtHome_phone = Nz(FixPhoneNum(Me!txtHome_phone))
If Me!txtHome_phone = "Invalid" Then
    Me!txtHome_phone.SetFocus           '  Unfortunately, this doesn't work and I don't know why  :-(
End If

Exit Sub
End Sub

[NeoPa]

Hi Petrol.

Without answering directly for now, the usual approach for checking user input is to include it in the BeforeUpdate Event Handler
(txtHome_phone_B eforeUpdate(Can cel As Integer)). Notice that has a Cancel parameter.

If/when Cancel is set to TRUE then the update doesn't proceed - which is different from your logic where it updates to the wrong value before being updated again to the text "Invalid". This goes through your txtHome_phone_A fterUpdate() process twice, of course. Not ideal.

See if that approach works better for you. If I remember correctly, you won't even need to use SetFocus() when the data fails as it won't move off that Control after a failed update.

[Petrol]

Thanks, NeoPa. Yes, I did originally have the code in BeforeUpdate, and of course you are quite right, it works perfectly there for illegal entries; when the FixPhoneNum function returns "Invalid" the BeforeUpdate procedure sets Cancel=True and the focus stays squarely on the same control.
The only problem is that when a valid phone mumber is entered, it doesn't get saved to the record. What is saved is exactly what the user entered, so any reformatting is lost. I tried storing the reformatted number in the text control, but that generated Error 2115:

I did finally get it to work - sort of - by calling the reformatting funtion BOTH BeforeUpdate and AfterUpdate,

Private Sub txtHome_phone_BeforeUpdate(Cancel As Integer)
'
'   Checks to make sure home phone number appears valid (numeric, with 8, 10 or 12 digits with or without embedded spaces)
'
If Nz(FixPhoneNum(Me!txtHome_phone)) = "Invalid" Then Cancel = True
End Sub

Private Sub txtHome_phone_AfterUpdate()
'
'   Standardises the format of home phone numbers.  It will accept them either with or without area codes.
'
Me!txtHome_phone = Nz(FixPhoneNum(Me!txtHome_phone))
End Sub

but this seems messy and wasteful, and is a pest to implement, since there are over a dozen places in various forms where phone numbners are entered. And if the function needs to output an informative message through MsgBox. the user will see it twice :-(.

[NeoPa]

It seems you've already found the solution Petrol.

However, to your point about a message going out to notify the user, let's deal with that. There are two main ways to handle that :

1. Probably a bit more common is that you move the code that displays the message into the AfterUpdate() section of your code after the FixPhoneNum() procedure has completed. It's main drawback is that it might need to be repeated in every place you update a phone.
2. Alternatively though, you could pass an Optional parameter (Boolean) into the procedure to be used to determine whether or not it sends the message. You would only trigger the message in the AfterUpdate() code.

Do be aware that when you update the value in the AfterUpdate() Event Handler you are causing another circle of BeforeUpdate()/Update/AfterUpdate() to be processed so it's important that you only ever make the change when the value entered is different from the formatted version you want to be stored (otherwise the code would never come out of the loop of course).

Something like :

Private Sub txtHome_phone_AfterUpdate()
'
' Standardises the format of home phone numbers. It will accept them either with or without area codes.
'
If Me.txtHome_phone = Nz(FixPhoneNum(Me.txtHome_phone)) Then Exit Sub
Me.txtHome_phone = Nz(FixPhoneNum(Me.txtHome_phone))

[Petrol]

Yes, thanks, I've found a soluton. I wasn't particularly happy with it, but ... c'est la vie.
Also, I used the optional argument to suppress the message when called before update. (The message has to be produced in the FixPhoneNum procedure because it is conditioned on the given input - specifically, whether an area code is included or not).

One last mystery: Your comment "Do be aware that when you update the value in the AfterUpdate() Event Handler you are causing another circle of BeforeUpdate()/Update/AfterUpdate() to be processed" makes eminent sense, but for some reason it's not true - at least in my case. I didn't include the suggested If test, but it all works fine. Breakpoints and Debug.Print statements confirm that the function is only executed twice, no matter what (legal) input it's given.

[NeoPa]

Interesting. Maybe setting it to the same value that it already has doesn't get recognised as an update - even when your code sets the .Value property of a bound Control.