How to pass variable in "Call Function" that can be used in me.variable.locked = true

[PartsPig]

I have a need to potentially preform the same actions on multiple fields of a form based on if it is clicked or not. I have written the code below in an attempt to minimize the amount of code and make it easier to follow. The problem is that when I try to compile it I get a Compile error: Method or data member not found. Is it possible to pass a field name through a "call function" that can be used as a data member? When I run the code in a single Sub in line with the rest of the code it works fine. Just didn't want to have to duplicate the code everywhere else it's needed in case I need to update or modify it sometime later.

Private Sub Inv_Num_Click()
    myUnlock INV_Num
End Sub
.
.
.
Private Sub myUnlock(myField)
    Dim myResult As Boolean
    
    If Me.myField.Locked = True Then
        myResult = (MsgBox("Do you want to unlock the " & myField & " field?", vbYesNo, "Unlock field") = vbYes)
        If myResult = True Then
<more code>
End Sub

 

[Micron]

You haven't declared myField as anything so it's a variant. When used in code you're leaving it up to VBA to figure it out, so it's probably being interpreted as a string. When declaring parameters it's best to declare them explicitly unless maybe you understand the implication and know how to deal with it. However, that leaves interpretation up to others and they'll likely question your intent or worse, not know the implication when they copy or try to fix such code. Maybe like this:

Private Sub myUnlock(myField As String)
   myUnlock "INV_Num"
End Sub

Private Sub myUnlock(myField As String)
Dim myResult As Integer
Dim ctl As Control

Set ctl = Me.Controls(myField).Name
If ctl.Locked Then
    myResult = MsgBox("Do you want to unlock " & ctl.Name & " field?", vbYesNo, "Unlock field") = vbYes
    If myResult = vbYes Then
        'do stuff
    End If
End If
' more code

End Sub

Another way would be to pass the control itself rather than deal with its name. So in the calling code you'd declare myField As Control

 

[PartsPig]

You haven't declared myField as anything so it's a variant. When used in code you're leaving it up to VBA to figure it out, so it's probably being interpreted as a string. When declaring parameters it's best to declare them explicitly unless maybe you understand the implication and know how to deal with it. However, that leaves interpretation up to others and they'll likely question your intent or worse, not know the implication when they copy or try to fix such code. Maybe like this:

Private Sub myUnlock(myField As String)
   myUnlock "INV_Num"
End Sub

Private Sub myUnlock(myField As String)
Dim myResult As Integer
Dim ctl As Control

Set ctl = Me.Controls(myField).Name
If ctl.Locked Then
    myResult = MsgBox("Do you want to unlock " & ctl.Name & " field?", vbYesNo, "Unlock field") = vbYes
    If myResult = vbYes Then
        'do stuff
    End If
End If
' more code

End Sub

Another way would be to pass the control itself rather than deal with its name. So in the calling code you'd declare myField As Control

Well I progressed farther than before with this advice (it will now compile) however it is throwing an error on line "Set ctl = Me.Controls(myField).Name"

 

[Micron]

Any chance you could post a link to a file share where I can download the file? Your pics don't help, especially when the problem line that is likely highlighted is hidden by that error message. Or step through the code and ensure that the variables (esp. myField) have the correct values.

 

[PartsPig]

Any chance you could post a link to a file share where I can download the file? Your pics don't help, especially when the problem line that is likely highlighted is hidden by that error message. Or step through the code and ensure that the variables (esp. myField) have the correct values.

I grabbed another pic real quick which I hope shows you all you are looking for. I also put all the code in use for this below. Thanks so much for taking a look at this and trying to assist. Heading home as workday is over but look forward to what I may find in the morning.

Private Sub Inv_Num_Click()
    myUnlock "INV_Num"
End Sub
----------------------------------------------------
Sub myUnlock(myField As String)
    Dim myResult As Boolean
    Dim ctl As Control
    
    MsgBox "myField = " & myField

    Set ctl = Me.Controls(myField).Name
    If ctl.Locked Then
        myResult = (MsgBox("Do you want to unlock the " & ctl.Name & " field?", vbYesNo, "Unlock field") = vbYes)
        If myResult = vbYes Then
            ctl.Locked = False
            MsgBox "The " & ctl.Name & " field has been unlocked.", vbOKOnly, "Unlocked"
        Else
            MsgBox "The " & ctl.Name & " field has remained locked.", vbOKOnly, "Remained locked"
        End If
    Else
        MsgBox "The " & ctl.Name & " field is already unlocked.", vbOKOnly, "Already unlocked"
    End If
        
End Sub

 

[dmt32]

Hi
as already suggested, better to pass the control object to your procedure

Not tested but see if this update to your code will do what you want

Private Sub Inv_Num_Click()
    myUnlock Inv_Num
End Sub
'----------------------------------------------------
Sub myUnlock(ByVal myField As Object)

    Dim IsLocked    As Boolean, UnlockIt As Boolean
    Dim ctl         As Control
   
    Set ctl = myField
    IsLocked = ctl.Locked
   
    If IsLocked Then UnlockIt = MsgBox("Do you want To unlock the " & ctl.Name & " field?", 36, "Unlock field") = vbYes
    MsgBox "The " & ctl.Name & " field has " & IIf(UnlockIt, "been unlocked.", _
                                               IIf(Not IsLocked, "already unlocked.", "remained locked.")), 64, "locked Status"
   
    ctl.Locked = Not UnlockIt
   
End Sub

Hopefully, I have got the logic correct

Dave

 

[PartsPig]

Hi
as already suggested, better to pass the control object to your procedure

Not tested but see if this update to your code will do what you want

Private Sub Inv_Num_Click()
    myUnlock Inv_Num
End Sub
'----------------------------------------------------
Sub myUnlock(ByVal myField As Object)

    Dim IsLocked    As Boolean, UnlockIt As Boolean
    Dim ctl         As Control
 
    Set ctl = myField
    IsLocked = ctl.Locked
 
    If IsLocked Then UnlockIt = MsgBox("Do you want To unlock the " & ctl.Name & " field?", 36, "Unlock field") = vbYes
    MsgBox "The " & ctl.Name & " field has " & IIf(UnlockIt, "been unlocked.", _
                                               IIf(Not IsLocked, "already unlocked.", "remained locked.")), 64, "locked Status"
 
    ctl.Locked = Not UnlockIt
 
End Sub

Hopefully, I have got the logic correct

Dave

I liked how you consolidated and cleaned up the code for the msgbox. I'll definitely be trying that to see if I can understand what's happening there (the NOT statements confuse me from time to time lol). Looks like passing a control variable the way you have it works as well. THANKS!

 

[dmt32]

I liked how you consolidated and cleaned up the code for the msgbox. I'll definitely be trying that to see if I can understand what's happening there

Your code is a typical approach & nothing wrong with it, all I try to do is follow what is known as DRY coding (Do not Repeat Yourself) to make code as compact as possible whilst maintaining readability.

(the NOT statements confuse me from time to time lol).

The Not operator performs logical negation on an expression you can read more in VBA helpfile.

Looks like passing a control variable the way you have it works as well. THANKS!

Glad update does what you want

I thank-you for your kind comments – very much appreciated.

Dave

 

[Micron]

@dmt32 - I'd say that you're not using Option Explicit; at least not with that sub. If you were you'd raise an error in the calling sub because the parameter isn't declared. Here is another approach using a little trick (I had to change the control name reference and chose to use dbl click event for convenience).

Private Sub TextBox4_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
myUnlock Me.Controls("textbox4")
    
End Sub
'----------------------------------------------------
Sub myUnlock(ByVal ctl As Control) 'as opposed to Object
Dim result As Integer

result = MsgBox("Unlock this field?", vbYesNo) - 7
ctl.Locked = Not CBool(result)

End Sub

By declaring the msgbox result as an integer and subtracting 7 you get either -1 (integer value for True) or 0 (integer value for False). I may have simplified the message too much, but figure if the answer is no and it is already locked then no harm in locking it again but it relies on intuition. I suppose one could use a yes/no/cancel message but that would require code to deal with another option.

 

[PartsPig]

Now that I have incorporated the above method into my DB I noticed it doesn't work on checkbox fields. Is there something special about using the "click" event for check boxes that could be interfering? When clicking on the checkbox it isn't even triggering the click event code nor is it changing the value from checked to unchecked or vise versa.