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

Lock, Unlock Controls A problem that crops up from time to time is the need to handle a Group of Controls differently from the rest of the Controls on a Form.Usually this is the need to Lock the Controls so that Data cannot be changed, and conversely Unlock them. However it may be the need

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

 

[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.

 

[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.

 

[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.

 

[Benginner2212]

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.

That is an issue that has been in the back of my mind. And I have added some validation checks in to my database, but the validation checks are mostly to make sure that a field in the record isn't left blank. I don't have the validation checks in the BeforeUpdate Event and after this conversation I will be trying to change that.

I know that there a fields on my records that data entered will never change. For example, the category and number that is assigned to a cable won't change and the type of cable that is installed most likely won't change. But I am running into a issue where my co-workers wanted to leave a cable in an equipment rack in the hopes of being able to reuse the cable instead of physically removing the cable from the racks. So now I have to figure out how to handle what happens if someone wants to reuse the cable number. Hopefully that would be as simple as allowing a user to change the other information besides the category, number and type of cable used.

At this point, I am guessing that I need to figure out to revert any changes that were made to the fields that should never changed after the record is created and put that code in the BeforeUpdate event on the form as the primary safeguard against recording bad data.

 

[Benginner2212]

The form's BeforeUpdate event is the LAST event that runs before a record gets saved. Think of it as the flapper at the bottom of a funnel. If the funnel is closed (cancel = true), the record can not pass through and be saved. If you don't cancel the update, the record proceeds to be saved. Although there are occasional reasons for using the control's BeforeUpdate or Change event for validation, the primary validation should be in the form's BeforeUpdate event. Also, NO control event can be used to validate whether a control contains data or not since if the focus never enters a control, none of its events would ever run.

Fields that NEVER change should be locked. Or, if you don't lock them, for that particular validation, you could use the Change event. If the record is new, allow entry, otherwise use:
Me.ControlName.Undo to back out the change and display a message.

Personally, I prefer to not have to dig around to see all the validation logic so I just put it all in the BeforeUpdate event. In some cases, I might have additional tests in the control's BeforeUpdate event if for example, I want to prevent a duplicate and give the user an immediate warning so he doesn't finish entering all the data only to find he can't save the record.

You might want to look at these two videos.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Okay, I was able to watch the two videos that you recommended and I can see what you are saying about having the validation code in the the Before_Update Event and I am working on getting the validation code on my forms moved over to the Before_Update Event on the form. The question that I have at this point is what is the difference between error checking and validation. In my thinking, error checking and validation are the same thing.

Right now, the code that I have in my before_update event is

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim deleteRequest As Integer
Dim errMessage, errTitle As String
errMessage = "Fill In Missing Information"
errTitle = "Critical Information Missing"


        On Error GoTo SaveError
            If Me.cboCableCategory = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboCableCategory.SetFocus
            ElseIf IsNull(Me.CableNumber) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.CableNumber.SetFocus
            ElseIf IsNull(Me.cableType) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cableType.SetFocus
            ElseIf Me.cboCableSource = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboCableSource.SetFocus
            ElseIf IsNull(Me.srcDescription) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.srcDescription.SetFocus
            ElseIf IsNull(drawingNumber) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.DrawingNum.SetFocus
            ElseIf Me.cableDestination_PK = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cableDrawingUpdate_PK.SetFocus
            ElseIf Me.cboDestination = 0 Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.cboDestination.SetFocus
            ElseIf IsNull(Me.dstDescription) Then
                MsgBox errMessage, vbOKOnly, errTitle
                Me.dstDescription.SetFocus
            Else
                Me.ActiveCable.Value = True
                Me.cableRecordLocked.Value = True
'                DoCmd.RunCommand acCmdSaveRecord
                MsgBox "Record Saved", vbOKOnly, "Record Saved"
'                DoCmd.GoToRecord acDataForm, "frmCableInformation", acNewRec
            End If
               
 
       
HandelSaveError:
    Exit Sub
SaveError:
    MsgBox Err.Description
    Resume HandelSaveError
   
End Sub

Which all that does for me is check to make sure that none of the fields are left blank, but beyond that it wouldn't stop a user from entering bad data into the form. The two fields that a never going to change after a record are created are Me.cboCableCategory and Me.cableNumber.

Currently when a record is saved I update the value of a check box and then on the forms current event, I have an if loop that checks the value of the box and if the check box's value is true, a function is called that locks all of the certain fields on the form. If a use wants to edit the record, they click an edit button an that unlocks all of the fields of the form.

Would a better solution be to call a function when the before_update event runs that just locks the fields that I know aren't going to change, never give the user a way to unlock those fields and then use the change_event to check if the user intended to change the other fields?

 

[Benginner2212]

That code won't do what you want it to do which is to stop the bad data from being saved. It gives you error messages but the bad data still gets saved. try it.

You have to use
Cancel = True
to stop access from saving a record with invalid data.

Error checking and validation are the same thing from opposite perspectives. You check for errors or you check for valid values

Yes, I can see how my current code wouldn't stop bad data from being saved. I keep trying to think of how I would check for bad data and what situations where where bad data would most likely be entered into the form. I think on text boxes that require a description would be pretty easy. I would check to make sure that the data was entered was longer than 3 or four characters long before the form is saved. I am not too worried about the combo boxes on the form because Access kicks back an error if a use tries to enter a value that isn't already an option on the combo box.

The situation when I am not sure how to handle and could use some advice is when the user enters the number that is going to be associated with the cable we are installing. Currently we assign a cable to a category that consists of a 3 letter prefix followed by a 4 digit number. However in the past we have had outside contractors that have installed systems and the contractors method for labeling cable was string of 7 alpha/numeric characters so I am a little worried about what will happen to the old records if I create a validation rule that is based on how we assign cables now.

 

[Benginner2212]

Thank you for the examples. They have been really helpful and I am getting a better handle on how to check for bad data. I would like to ask a couple of questions about your code to make sure that I am understanding what is going on

First the line:

If IsNull(Me.cboDeptName.OldValue) Then                     
    Else

will always be false after a record is saved. Because there is no code for the condition first part of the loop access won't touch anything on the record and move on to the second condition

Then the lines:

If Me.cboDeptName = Me.cboDeptName.OldValue Then             
    Else
            MsgBox "Dept Name may not be changed after record is saved.", vbOKOnly
            Cancel = True
            Me.Undo
            Me.cboDeptName.SetFocus
            Exit Sub
        End If

Since the first condition will never be true there is no code for that condition access executes the code for the other condition. This is where the update to the form is canceled and then Me.Undo is reverting any changes that were made to the form?