Solved Creating Function To Lock Text Boxes and Combo Boxes When A Form Loads

[Benginner2212]

I have two forms where I would like to have the combo boxes and text boxes based on how a user opens the form. Since I have two forms, I thought that I would write a function but I am having issues. First off, I know that my code in the function to lock the text box is wrong, but I can get the function to run in the first place to begin trouble shooting that issue.

So the code that I am trying execute in the function is

Public Sub lockTxtBox(txtBox As TextBox)

    Dim txtBoxColor As Long
    Dim txtCtrl As Control
    
    txtBoxColor = RGB(211, 211, 211)
    
    For Each txtCtrl In Me.Controls
        
            If TypeOf txtCtrl Is TextBox Then
                txtCtrl.BackColor = BoxColor
                txtCtrl.Locked = True
            End If
        Next
        
End Sub

If my understanding is correct, since a text box is an object I have to pass it by reference and passing by reference is the default in Access I don't think that I needed to have it written as

Public Sub (By REF txtBox As TextBox)

And then is the main code I am trying to call the function like this:

Private Sub Form_Load()

Dim cableBoxColor As Long
Dim cableTxtCtrl As Control
Dim cableCboCtrl As Control

cableBoxColor = RGB(211, 211, 211)


If Me.OpenArgs = "NewRecord" Then
Me.cboCategoryFilter.Visible = False
Me.txtCableNumber.Visible = False
Me.btnClearSearch.Enabled = False
Me.btnClearSearch.Visible = False

ElseIf Me.OpenArgs = "SearchRecord" Then
Me.cboCategoryFilter.Visible = True
Me.txtCableNumber.Visible = True
Me.btnClearSearch.Enabled = True
Me.btnClearSearch.Visible = True
Me.btnAddCableCategory.Visible = False
Me.btnAddSourceRack.Visible = False
Me.btnAddDestinationRack.Visible = False
Me.btnSaveRecord.Caption = "Update"

lockTxtBox Me.cableSouceDescription

ElseIf Me.OpenArgs = "DeleteRecord" Then
Me.cboCategoryFilter.Visible = True
Me.txtCableNumber.Visible = True
Me.btnClearSearch.Enabled = True
Me.btnClearSearch.Visible = True
Me.btnSaveRecord.Caption = "Delete"
Me.btnAddCableCategory.Visible = False
Me.btnAddSourceRack.Visible = False
Me.btnAddDestinationRack.Visible = False
End If


End Sub

When the code runs, I get an Expected Variable or procedure, not module error.

I also tried

Call  lockTxtBox Me.cableSouceDescription

hoping that would work, but I got a syntax error which I am guessing I got because I don't need to use call.

Not sure what I am doing wrong.

 

[Gasman]

You cannot use Me. outside of a form.
Sounds like you have named the module with that name?

 

[The_Doc_Man]

If you plan to use this generically, you must define the sub as Public in a general module. The name of the sub must not match the name of the module. Then, if you want to do this for every text box, you got the idea of the loop right but not the arguments.

You can pass a form object to a sub. I'll give you the skeleton, you can flesh it out.

Public Sub LockTextBoxes( frm as Access.Form )
Dim ctl as Access.Textbox
... here is where you compute any constants you might need (like your preferred color)
    For Each ctl in frm.Controls
        If ctl.Type = acTextBox Then
... diddle with your text boxes here.
        End If
    Next ctl

End Sub

Then you call this from the Form_Load or Form_Current event but NOT the Form_Open event. The controls don't exist to be diddled until after the form is opened and the control loading has been done - which isn't complete until the Form_Load event.

    LockTextBoxes Me

 

[theDBguy]

If you just want to lock a single control on your form based on whether it's a new record or not, I think a common function may be an overkill.

To call that function takes one line of code but so does disabling the control, so you may not be gaining any advantage at all.

 

[Benginner2212]

If you just want to lock a single control on your form based on whether it's a new record or not, I think a common function may be an overkill.

To call that function takes one line of code but so does disabling the control, so you may not be gaining any advantage at all.

I think the function is most likely overkill and I am not even sure I am going down the right road for what I am trying to do.

Basically, when I don't want a user to be able to change the data on a form when they are searching and filtering the form. So I was hoping that instead of having to write a line of code for each field on the form that I don't want the user to change I could write a function to do that. But I don't think it will work.

 

[theDBguy]

I think the function is most likely overkill and I am not even sure I am going down the right road for what I am trying to do.

Basically, when I don't want a user to be able to change the data on a form when they are searching and filtering the form. So I was hoping that instead of having to write a line of code for each field on the form that I don't want the user to change I could write a function to do that. But I don't think it will work.

If you can post a sample db and tell us what you want to happen, maybe someone can show you how to do it.

 

[Uncle Gizmo]

You may find my blog on Lock - Unlock controls useful.

Lock, Unlock Controls – Nifty Access

www.niftyaccess.com

YouTube

 

[C3P420]

I think the function is most likely overkill and I am not even sure I am going down the right road for what I am trying to do.

Basically, when I don't want a user to be able to change the data on a form when they are searching and filtering the form. So I was hoping that instead of having to write a line of code for each field on the form that I don't want the user to change I could write a function to do that. But I don't think it will work.

If you're just looking to restrict editing, you can toggle a form properties easily.

Me.AllowEdits = False

Alternatively, you could specify "acFormReadOnly" as the DataMode when opening the Form

 

[Benginner2212]

If you can post a sample db and tell us what you want to happen, maybe someone can show you how to do it.

When you click on the button that reads Search/Edit Records and the form frmCableInfo open up the fields cboCategory, CableNumber, cboSource, srcDescription, DrawingNumber, cboDestination, and dstDescription are locked and not able to be edited.

When you click on Add Cable the frmCableInfo is opened and the fields cboCategory, CableNumber, cboSource, srcDescription, DrawingNumber, cboDestination, and dstDescription are unlocked and the user would be able to edit the fields.

 

[Benginner2212]

If you're just looking to restrict editing, you can toggle a form properties easily.

Me.AllowEdits = False

Alternatively, you could specify "acFormReadOnly" as the DataMode when opening the Form

The only problem with that method is that I have text boxes and combo boxes on the form that I am using for search functions.

 

[C3P420]

The only problem with that method is that I have text boxes and combo boxes on the form that I am using for search functions.

Here's another trick. You can set the Tag property (It's on the Property Sheet / Other Tab for the control) on Form controls as appropriate. Then, when the Form Opens, you could call something like the function below:

LockFormControls Me.Form, True
LockFormControls Me.Form, False

This would allow you to use the method on multiple forms, one function. Just send it whether you're locking or unlocking the tagged controls.

Public Sub LockFormControls(frmF as Form, blLock as Boolean)

    Dim varCtrl As Control
   
    For Each varCtrl In frmF.Controls
        If varCtrl.Properties("TAG") = "LOCK" Then
            frmF.Controls(varCtrl.Name).Locked = blLock
        End If
    Next varCtrl

End Sub

 

[Pat Hartman]

You can lock the controls using a function as suggested above. I can post a more sophisticated version that lets you have controls that are always locked or always unlocked and you only have to tag those two types. You don't need to tag all controls that you want to lock which becomes a pain once you have to go back and change a form. Essentially a control marked "lock" is always locked ( such as an autonumber) regardless of whether you are toggling to lock or unlock and a control marked "unlock" is always unlocked (like your search combos) regardless of whether you are toggling to lock or unlock. You don't want to forget to set the tag property. If there is no tag, a control gets locked if block is true or unlocked if block is false. Just ask.

But, better still is to allow the user to type whatever he wants and you stop the save by using the form's BeforeUpdate event.

You would need to better describe how you know who can update and under what conditions but essential it is just a couple of lines of code in the form's BeforeUpdate event.

If whatever condition allows updating
Else
    Msgbox "You are not allowed to update at this time.", vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

 

[Benginner2212]

You can lock the controls using a function as suggested above. I can post a more sophisticated version that lets you have controls that are always locked or always unlocked and you only have to tag those two types. You don't need to tag all controls that you want to lock which becomes a pain once you have to go back and change a form. Essentially a control marked "lock" is always locked ( such as an autonumber) regardless of whether you are toggling to lock or unlock and a control marked "unlock" is always unlocked (like your search combos) regardless of whether you are toggling to lock or unlock. You don't want to forget to set the tag property. If there is no tag, a control gets locked if block is true or unlocked if block is false. Just ask.

But, better still is to allow the user to type whatever he wants and you stop the save by using the form's BeforeUpdate event.

You would need to better describe how you know who can update and under what conditions but essential it is just a couple of lines of code in the form's BeforeUpdate event.

If whatever condition allows updating
Else
    Msgbox "You are not allowed to update at this time.", vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

It would be nice not to have to tag so many things on my forms, especially if I need to make changes to the forms in the future.

 

[Uncle Gizmo]

It would be nice not to have to tag so many things on my forms, especially if I need to make changes to the forms in the future.

Your comment leads me to believe that you have created a form, copied that form and made minor alterations. You now have two "cookie cutter forms" possibly more than two.

This approach leads to the difficulty in making changes you allude to in your comment.

If that's the case, then a better approach is to stick with one form and use various VBA techniques to adapt that form to the different purposes thereby replacing your multiple cookie cutter forms with one VBA managed form.

 

[Pat Hartman]

It would be nice not to have to tag so many things on my forms, especially if I need to make changes to the forms in the future.

Well, using the "form's BeforeUpdate" solution certainly does that.

You didn't ask but here's my version of the lock/unlock procedure. You ONLY need to tag the controls which will be "different". i.e. always locked or always unlocked. All controls which will toggle, don't need to be tagged.

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

 

[Benginner2212]

Your comment leads me to believe that you have created a form, copied that form and made minor alterations. You now have two "cookie cutter forms" possibly more than two.

This approach leads to the difficulty in making changes you allude to in your comment.

If that's the case, then a better approach is to stick with one form and use various VBA techniques to adapt that form to the different purposes thereby replacing your multiple cookie cutter forms with one VBA managed form.

I only have one form for entering/editing the information for the cables that are being recording into the database. I am using OpenArgs adapt the form for the purpose that I need. I have another form for keeping track of equipment maintenance. The only similarities between the two forms is that both forms have fields that I would like to have locked to prevent records from being changed.

 

[Benginner2212]

Well, using the "form's BeforeUpdate" solution certainly does that.

You didn't ask but here's my version of the lock/unlock procedure. You ONLY need to tag the controls which will be "different". i.e. always locked or always unlocked. All controls which will toggle, don't need to be tagged.

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

I must be missing something. I am trying to call this function from the form's before update event and I am able to make changes to the information in the fields that are supposed to be locked.

 

[Pat Hartman]

The form's BeforeUpdate event is the place where you validate data. This is the LAST event that runs before a record is saved. If the data is not valid, you cancel the update to prevent the invalid data from being saved. If I have implemented security in an application, THIS is where I check it. the first thing the BeforeUpdate event does is to validate that the person doing the data entry is authorized to update the record. If he is not, then I set the cancel property to true AND I use Me.Undo to erase all typing in the form. If you understand how to use the BeforeUpdate event correctly, you have no need to lock controls ahead of time. Although I did post code that will do it if that makes you feel better.

If you want to lock controls, the event you would use is the Form's Current event. This event runs each time you move to a new record. So, each time you move to a new record, it is locked. This means you don't have to worry about locking the form in the Form's AfterUpdate event although that works also assuming you've got a button to unlock. The reason I use the current event to lock in this situation is because the user might press the unlock button but not actually perform an update. If he doesn't perform an update, then the call to lock the record again will never be executed because the form's AfterUpdate event will never run. So, all-in-all, the Current event is safer if your code ever toggles the locks.

I'm pretty sure that somewhere along the line I must have mentioned that I almost never lock controls ahead of time. I prefer to use the BeforeUpdate event to validate data and just not save invalid data. It is not wrong, to lock the forms using the code I provided, I just rarely do it. However, if you check the user's authorization in the Current event, you can use that event to decide whether to lock or not lock based on the user's authorization.

 

[Benginner2212]

I'm pretty sure that somewhere along the line I must have mentioned that I almost never lock controls ahead of time. I prefer to use the BeforeUpdate event to validate data and just not save invalid data. It is not wrong, to lock the forms using the code I provided, I just rarely do it. However, if you check the user's authorization in the Current event, you can use that event to decide whether to lock or not lock based on the user's authorization.

I am sorry, you did say previously that you use BeforeUpdate to validate the data. I am beginning to understand that using BeforeUpdate could/would be the best way to prevent bad data from being entered which would lessen my need to lock fields on the form.

My desire to lock certain fields on a form comes from an issue with pervious version of this database where my collogues and I ran into an issue where records in the database would be changed by accident because someone accidentally clicked on a combo box and changed a value. So I thought it would be a good idea to lock off the fields on the form so that after the record is saved so that we don't accidentally the information in the records.

 

[Pat Hartman]

The previous version probably didn't have any validation code. That is the dangerous part. But, locking the record in the current event doesn't actually solve the problem of bad data. The user could just press the "button" to unlock the form and lose focus and lean on the keyboard and if you have no validation code, the bad data is simply saved. You've gained nothing.

I might decide for whatever reason that for some particular application or just some particular form, I want the additional protection of locking the record. But I ALWAYS have validation code for every condition I can think of. Just about the only fields where validation isn't really possible is fields for free text but you can still ensure that something is entered if the field is required even though you may not be able to determine what might be a valid value for the field.