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

 

[PartsPig]

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

Micron,

I really like the idea of using the double click to trigger the unlock event. I have incorporated it throughout my DB form and found it also addresses unlocking checkboxes where the single click method did not. I was so happy to see that! Thanks for that suggestion!

I had already changed the msgbox as the popups once the field was unlocked were very annoying.

I left everything else as it is since it is working and I don't want to screw it up. LOL. I will however go through the proposed changes and likely incorporate them sometime soon. Please keep an eye out for future questions from PartsPig. I would love your input.

 

[PartsPig]

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 operator performs logical negation on an expression you can read more in VBA helpfile.



Glad update does what you want

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

Dave

Dave,

I am very much a proponent of the DRY method but have been out of the game so long I feel like a beginner again. I ended up changing the msgbox to do nothing if the field was already unlocked as the popups were very annoying, especially when having to correct multiple entries. Please keep an eye out for future questions from PartsPig. I would love your input.

 

[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

I just updated & reposted OPs published code but I am fully aware of the point you make, all my modules have Option Explicit declared & in this case, code compiled ok for me

Dave

 

[dmt32]

Dave,

I am very much a proponent of the DRY method but have been out of the game so long I feel like a beginner again.

If you see code being repeated across a project that largely does the same thing, then always worth thinking about ways to reduce it.

I ended up changing the msgbox to do nothing if the field was already unlocked as the popups were very annoying, especially when having to correct multiple entries.

Too many message boxes can be annoying that's why in well developed applications, users are given option to surppress them.

Good luck with project

Dave

 

[PartsPig]

Option to supress yo

If you see code being repeated across a project that largely does the same thing, then always worth thinking about ways to reduce it.

Too many message boxes can be annoying that's why in well developed applications, users are given option to surppress them.

Good luck with project

Dave

Option to suppress you say, Hmmmmm. I like that but it will be for later during the "let's make it pretty" stage - LOL.