Changing Bound Text Box Border Width Property Screws Up Tab Order and Value Display

[Dan]

Hi, I am using Access 2007. I have module sub that is passed the
form's name and it checks to see if control is a text box that just
received focus and if so thickens it border to queue user where
insertion pointer is. The problem is as the user tabs from control
to control the text will disappear in the text box that got the focus
and reappear on exit from control or a mouse click in it. In addition
the tab order no longing works either. Here is the code:

' This function, HighlightTextbox(), highlights a textbox border to
indicate it has the Focus and de-highlights
' previous object focus if textbox. Since Access cannot trigger this
function on the form level, a call must be made
' in each textbox on the form.
Public Sub HighlightTextbox(strCallingForm As String)

Dim ctrlActive As Control
Dim strActiveControlSource As String
Dim ctrlPrev As Control
Dim strPrevControlSource As String
Dim intRetval As Integer



On Error GoTo HighlightTextbox_Error

'If Forms(strCallingForm).Visible = True Then

'Forms(strCallingForm).AllowEdits = False

Set ctrlActive = Screen.ActiveControl

If TypeOf ctrlActive Is TextBox Then
'ctrlActive.Enabled = False
'strActiveControlSource = ctrlActive.ControlSource
'ctrlActive.ControlSource = ""
'ctrlActive.Locked = True
ctrlActive.BorderWidth = 2
intRetval = DoEvents()
Debug.Print "active", ctrlActive.Name
End If

Set ctrlPrev = Screen.PreviousControl
If TypeOf ctrlPrev Is TextBox Then
'ctrlPrev.Enabled = False
'strPrevControlSource = ctrlPrev.ControlSource
'ctrlPrev.ControlSource = ""
'ctrlPrev.Locked = True
ctrlPrev.BorderWidth = 0
intRetval = DoEvents()
Debug.Print "previous", ctrlPrev.Name
End If

Debug.Print Time, ctrlActive.Name, ctrlPrev.Name
'ctrlActive.Enabled = True
'ctrlPrev.Enabled = True
'ctrlActive.ControlSource = strActiveControlSource
'ctrlPrev.ControlSource = strPrevControlSource
'ctrlActive.Locked = False
'ctrlPrev.Locked = False
'Forms(strCallingForm).AllowEdits = False

'End If

Exit Sub

HighlightTextbox_Error:

Exit Sub

End Sub


How can I get Access to behave properly? I am assuming I have to
suspend the Control Source in some fashion but tried many ways with no
luck. TIA

[goodguy]

Why have you commented out parts of the code?
Are they commented as such in your source?

[Dan]

Why have you commented out parts of the code?
Are they commented as such in your source?

Those are steps I tried to disable the bound text box from bound behavior. This bug is only reproducible with bound text boxes.

[goodguy]

strPrevControlSource = ctrlPrev.ControlSource

Why are you modifying the controlsource property in code?
This is the crux of your problem. Controlsource should best be set at runtime and not altered afterward unless you are a pro with VBA.

[Dan]

It was just a test to see if it would prevent the problem. Ignore the remarked executable code.

[goodguy]

Please attach your db.

[boblarson]

1. You don't need to pass the form's name. You aren't even using it.

2. The procedure should be a FUNCTION and not a SUB if you want to set the event property to =HighlightTextBox()



3. This works for me but I'm using Access 2003 at the moment at work but you might want to double check that your text boxes don't have the backstyle set to transparent which could be why the text appears to disappear when the focus is set on it.

Public Function HighlightTextbox()
    Dim ctrlActive As Control
    Dim strActiveControlSource As String
    Dim ctrlPrev As Control
    Dim strPrevControlSource As String
    Dim intRetval As Integer
    On Error GoTo HighlightTextbox_Error
 
    Set ctrlActive = Screen.ActiveControl
    If TypeOf ctrlActive Is TextBox Then
        ctrlActive.BorderWidth = 6
        intRetval = DoEvents()
        Debug.Print "active", ctrlActive.Name
    End If
    Set ctrlPrev = Screen.PreviousControl
    If TypeOf ctrlPrev Is TextBox Then
        ctrlPrev.BorderWidth = 0
        intRetval = DoEvents()
        Debug.Print "previous", ctrlPrev.Name
    End If
    Debug.Print Time, ctrlActive.Name, ctrlPrev.Name
ExitHere:
     Exit Function
HighlightTextbox_Error:
 
            MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure HighlightTextbox "
            Resume ExitHere
            Resume
End Function

Now it may be that it will work better to have another function which will set the control back to normal instead of trying to do it here and you would put that call in the control's Lost Focus event.

[Dan]

Bob thanks for tips but it did not clear up the problem with Access 2007. My Borderstyles are set to SOLID so that was not the issue. Also, changing the SUB to a FUNCTION and referencing it in the EVENT list did not clear up issue.

By changing the borderwidth in the GotFocus() and LostFocus() events the disappearing text value problem is fixed so that problem has something to do with jumping out to a Module function call. Big pain to change all the values in each event though!

I still have the remaining TAB issue though.