Make TextBox Border visible via VBA

[kimperkins1]

I'm a VBA rookie. I have an MS Access form with multiple textboxes. I found how to lock and unlock all boxes on the form via a click event and a Public Function, but I don't know how to change their borders from transparent to solid when they are unlocked.

 

[John Big Booty]

Welcome to the forum.

As part of the Click event that is locking/unlocking your Text Boxes, use;

Me.YourTextBoxName.BorderStyle = 1

for a solid border and

Me.YourTextBoxName.BorderStyle = 0

for a transparent border, see this link for other options.

Also note that changing the border may not be readily visible if the text box Special Effect is anything other than Flat.

 

[kimperkins1]

Excellent. I was trying .BorderStyle = "Solid", "Transparent". Now, do you know a way to apply the change to all text boxes at once instead of referencing each one in the code?

 

[John Big Booty]

Can you not apply the same technique, that you are already using to Lock/Unlock your Text Boxes

 

[kimperkins1]

Probably, if I knew what I was doing. The Function is pasted below. When I try to create another Case acTextBox, I don't know how to refer to the .BorderStyle property as you can see. It works on the individual control, txt400HzType, commented out a few lines later, but not in the Case acTextBox.

Public Function LockBoundControls(frm As Form, bLock As Boolean, ParamArray avarExceptionList())
On Error GoTo Err_Handler
'Purpose: Lock the bound controls and prevent deletes on the form any its subforms.
'Arguments frm = the form to be locked
' bLock = True to lock, False to unlock.
' avarExceptionList: Names of the controls NOT to lock (variant array of strings).
'Usage: Call LockBoundControls(Me. True)
Dim ctl As Control 'Each control on the form
Dim lngI As Long 'Loop controller.
Dim bSkip As Boolean

'Save any edits.
If frm.Dirty Then
frm.Dirty = False
End If
'Block deletions.
frm.AllowDeletions = Not bLock

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox, acOptionButton, acToggleButton
'Lock/unlock these controls if bound to fields.
bSkip = False
For lngI = LBound(avarExceptionList) To UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Not ctl.ControlSource Like "=*" Then
If ctl.Locked <> bLock Then
ctl.Locked = bLock
End If
End If
End If
End If


Case acSubform
'Recursive call to handle all subforms.
bSkip = False
For lngI = LBound(avarExceptionList) To UBound(avarExceptionList)
If avarExceptionList(lngI) = ctl.Name Then
bSkip = True
Exit For
End If
Next
If Not bSkip Then
If Len(Nz(ctl.SourceObject, vbNullString)) > 0 Then
ctl.Form.AllowDeletions = Not bLock
ctl.Form.AllowAdditions = Not bLock
Call LockBoundControls(ctl.Form, bLock)
End If
End If

Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl, acPage, acPageBreak, acImage, acObjectFrame
'Do nothing

Case acTextBox
BorderStyle = IIf(bLock, 0, 1)
ForeColor = IIf(bLock, 16777215, 250)

Case Else
'Includes acBoundObjectFrame, acCustomControl
Debug.Print ctl.Name & " not handled " & Now()
End Select
Next

'Set the visual indicators on the form.
On Error Resume Next
lblEditRec.Caption = IIf(bLock, "EDIT THIS RECORD", "RECORD OPEN FOR EDIT")

'txt400HzType.BorderStyle = IIf(bLock, 0, 1)
'txt400HzType.ForeColor = IIf(bLock, 16777215, 250)

Exit_Handler:
Set ctl = Nothing
Exit Function
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_Handler
End Function

 

[John Big Booty]

My apologies for the delay in getting back to this thread, the following should do what you are after;

Dim ctrl As Control

For Each ctrl In Forms("YourFormName").Controls
    If ctrl.ControlType = acTextBox Then
        If ctrl.Locked = True Then
            ctrl.Locked = False
            ctrl.BorderStyle = 0
        Else: ctrl.Locked = False
            ctrl.Locked = True
            ctrl.BorderStyle = 1
        End If
    End If
Next

You can paste it straight into the Click event of your button. If you are feeling real keen you could turn it into Public Function.

 

[kimperkins1]

Thanks so much. I figured out how to create a Case acTextBox and got it working before your reply. The piece of info I was missing was that the property could be referred to as " ctl.BorderStyle". Your method looks a lot cleaner. I'll try it when I get back in the office.