Error with .OnClick

[AUGuy]

I've got a control variable that points to different form labels depending on several factors. I use this variable earlier in the code to change the caption of the label with no problems whatsoever. However when i use this same variable to try to change the .OnClick property, I get an error telling me Expected Function or Variable. .OnClick code listed below, feel free to tell me how this is wrong

ctlCurMenuBox.OnClick = DoCmd.OpenForm(formname)

BTW, the variable (ctlCurMenuBox) at this point is set to Me.lbl_MenuBox1
Thanks for looking,
Guy

 

[DCrake]

ctlCurMenuBox.OnClick = DoCmd.OpenForm(formname)

Does not belong on the same line

Private sub ctlCurMenuBox_Click()

   DoCmd.OpenForm "FormName"

End Sub

 

[boblarson]

Does not belong on the same line

Private sub ctlCurMenuBox_Click()
 
   DoCmd.OpenForm "FormName"
 
End Sub

That isn't what they are trying to do David. They are trying to DYNAMICALLY add a click event instead of having to have to go put it in for each.

ChrisO did up a sample and I did a presentation on it in Portland and Seattle quite a while ago. I haven't used it in a while so I'll have to go search for the thread to make sure I can refresh on the correct syntax, unless someone beats me to the punch (which would be fine).

 

[AUGuy]

Is there any way to do it without having to create a separate sub for each label? Depending on the case, the labels can have different onclick needs, thats why a solution i can integrate into a select/case would fit my needs

 

[DCrake]

Something that I have never had the need to do so that's maybe why I misinterpreted the question.

In what senerio would you need this functionality?

 

[boblarson]

Okay, I got the refresher now.

So, AUGuy -

You want to create a public function in a standard module like this:

Function OpenMyForm(strFormName As String)
    DoCmd.OpenForm strFormName
End Function

Then you set the control's On Click event property to:

ctlCurMenuBox.OnClick = "=OpenMyForm('" & [FormName] & "')"

That is, if [FormName] exists as a field on your current form. You can also pass an explicit name:

ctlCurMenuBox.OnClick = "=OpenMyForm('MyFormNameHere')"

 

[boblarson]

In what senerio would you need this functionality?

Say you have 50 controls and don't want to have to go set the event for each of them. You can set them ALL it in the form's On Open event.

 

[AUGuy]

Okay, I got the refresher now.

So, AUGuy -

You want to create a public function in a standard module like this:

Function OpenMyForm(strFormName As String)
    DoCmd.OpenForm strFormName
End Function

Then you set the control's On Click event property to:

ctlCurMenuBox.OnClick = "=OpenMyForm('" & [FormName] & "')"

That is, if [FormName] exists as a field on your current form. You can also pass an explicit name:

ctlCurMenuBox.OnClick = "=OpenMyForm('MyFormNameHere')"

Thanks, Bob. Believe it or not i actually started with this option but I, of course, had the syntax for calling the function within the .OnClick statement wrong. I really need to find a way to learn all these syntax situations!

 

[AUGuy]

When i put in the code you listed bob, it worked the first time. I saved everything, exit out, then entered the db to see if it woudl all work together. Now when i click the label, i get the error 'The expression On Click you entered as teh event property setting produced the following error: The expression you entered has a function name that Microsoft access can't find.'

the code that calls the function is:

[B]ctlCurMenuBox.OnClick = "=FormOpen('" & strMenuClick(Counter) & "')"[/B]

The Function itself is:

[B]Function FormOpen(strFormName as String)
 DoCmd.OpenForm strFormName
End Function
[/B]

 

[vbaInet]

It is referring to strMenuClick() function. Plus Counter must exist as a field in the record source of the form, the name of a control or as a global variable.

 

[AUGuy]

It is referring to strMenuClick() function. Plus Counter must exist as a field in the record source of the form, the name of a control or as a global variable.

I'm afraid I dont fully understand...
the onclick is set while the counter variable is active, Counter(2) for instance would simply set it to lbl_MenuBox3

Which is what it did perfectly before i closed/reopened the database. I would think that if it wouldn't work that it wouldn't work at all. But in this case it did, until i closed/opened everything again

 

[boblarson]

Post the part of the code where you build the strMenuClick array (and probably the rest around it too).

 

[boblarson]

And what event is this code on?

 

[vbaInet]

Try this:

ctlCurMenuBox.OnClick = "=FormOpen(" & Chr(34) & strMenuClick(Counter) & Chr(34) & ")"

This will work if strMenuClick() is an array, counter is a variable and FormOpen() is a function.

 

[AUGuy]

Private Sub Form_Load()


Dim strMenuPerm(4) As String 
Dim strMenuBox(4) As Control 
Dim strMenuName(4) As String 
Dim strMenuClick(4) As String 
Dim strUserID As String 
Dim strUsername As String 
Dim intDeptID As Integer 
Dim strDeptDesc As String 
Dim intAccessLvl As Integer 
Dim strAccessDesc As String 
Dim intMenuCount As Integer 
Dim ctlCurMenuBox As Control 
Dim strCurMenuName As String 
Dim ctlFormOpen As Control

    
    
    strUserID = Environ("username") 
    strUsername = DLookup("[UserName]", "tbl_AuthUserList", "[UserID] = '" & strUserID & "'") 
    intDeptID = DLookup("[UserDept]", "tbl_AuthUserList", "[UserID] = '" & strUserID & "'") 
    strDeptDesc = DLookup("[DeptName]", "tbl_DeptList", "[DeptIDNum] = " & intDeptID) 
    intAccessLvl = DLookup("[AccessLevel]", "tbl_AuthUserList", "[UserID] = '" & strUserID & "'") 
    strAccessDesc = DLookup("[AccessLvlDesc]", "tbl_AccessLvl", "[AccessLvlID] = " & intAccessLvl) 
    strMenuPerm(0) = DLookup("[Menu1]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'") 
    strMenuPerm(1) = DLookup("[Menu2]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'") 
    strMenuPerm(2) = DLookup("[Menu3]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'") 
    strMenuPerm(3) = DLookup("[Menu4]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'") 
    strMenuPerm(4) = DLookup("[Menu5]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'") 
    strMenuName(0) = "Menu 1" 
    strMenuName(1) = "Menu 2" 
    strMenuName(2) = "Menu 3" 
    strMenuName(3) = "Menu 4" 
    strMenuName(4) = "Menu 5" 
    strMenuClick(0) = "frm_Form1" 
    strMenuClick(1) = "frm_Form2" 
    strMenuClick(2) = "frm_Form3" 
    strMenuClick(3) = "frm_Form4" 
    strMenuClick(4) = "frm_Form5" 
    intMenuCount = 0 
For Counter = LBound(strMenuPerm) To UBound(strMenuPerm) 
    Select Case intMenuCount 
        Case 0 
            Set ctlCurMenuBox = Me.lbl_MenuBox1 
        Case 1 
            Set ctlCurMenuBox = Me.lbl_MenuBox2 
        Case 2 
            Set ctlCurMenuBox = Me.lbl_MenuBox3 
        Case 3 
            Set ctlCurMenuBox = Me.lbl_MenuBox4 
        Case 4 
            Set ctlCurMenuBox = Me.lbl_MenuBox5 
    End Select 
    Select Case strMenuPerm(Counter) 
        Case 0 
          
        Case 1 
          ctlCurMenuBox.Caption = strMenuName(Counter) 
          ctlCurMenuBox.OnClick = "=FormOpen('" & strMenuClick(Counter) & "')" 
          intMenuCount = intMenuCount + 1 
    End Select 
    
Next 
End Sub

 

[boblarson]

I would give vbaInet's solution with Chr(34) a try. It might work. I would normally go that direction too.

Also, as far as your code goes - a few tips

1. Don't use DLookups for the items where you have more than one to get. So, use a recordset for getting the three pieces of information from tbl_AuthUserList and tbl_MenuPerm. It will be faster.

2. I hope you are going to put in something in the Case 0 of Select Case strMenuPerm(Counter) or else just get rid of it. You can put a Case Else and put a message box to notify you if an unhandled value comes through. But otherwise don't bother putting in the other case statements if they aren't going to do anything. It isn't required to have them.

 

[vbaInet]

Lots of DLookups(). Here's one way for strMenuPerm. Declare it as VARIANT and replace those 5 lines with this:

strMenuPerm = Split(DLookup("[Menu1] & '|' & [Menu2] & '|' & [Menu3] & '|' & [Menu4] & '|' & [Menu5]", "tbl_MenuPerm", "[UserID] = '" & strUserID & "'"), "|")

Or use a recordset - might run faster.

Did you see my last post?

 

[AUGuy]

Try this:

ctlCurMenuBox.OnClick = "=FormOpen(" & Chr(34) & strMenuClick(Counter) & Chr(34) & ")"

This will work if strMenuClick() is an array, counter is a variable and FormOpen() is a function.

Inet, unfortunately I get the same error with this code as with the other.

 

[boblarson]

Is the FormOpen function in a Standard Module or a Form Module? It needs to be in a STANDARD module with a name for the module different than the name of the function.

 

[AUGuy]

Is the FormOpen function in a Standard Module or a Form Module? It needs to be in a STANDARD module with a name for the module different than the name of the function.

Thanks to my lesson from you yesterday, it is a standard module. HOWEVER, it is named the same thing as the function I'm guessing thats a bobo-nono

Yep that did it. Also explains why it broke after i saved/exited because i named the module FormOpen