Solved Setting on click event of a control in VBA

[KitaYama]

Main form : FrmManu
Sub form : frmManu_Sub
I can set the following as the on click event of a label in sub form's property sheet:

=ReSortForm2(Forms('frmManu').[frmManu_Sub].[Form])

Works fine.
But when I want to set the exact structure in main form's open event, I receive an error:

Private Sub Form_Open(Cancel As Integer)

    Dim Expression As String

    Expression = "=ReSortForm2(Forms('frmManu').[frmManu_Sub].[Form])"
    Me.frmManu_Sub.Form.Controls("lblSortRec_ID").OnClick = Expression

End Sub

Error line is :
Me.frmManu_Sub.Form.Controls("lblSortRec_ID").OnClick = Expression

Error:
Run time error 2423
The expression you entered has an invalid . (dot) or ! operator or invalid parentheses.

If I change it to Me.frmManu_Sub.Form.Controls("lblSortRec_ID").OnClick = ""
the error doesn't show. Which proves the path to control is correct. And Access detects an error in the expression I'm trying to assign.

What am I doing wrong.
Thanks.

 

[Edgar_]

Maybe it does not like the syntax when you try to assign it from VBA. You could try with:
Expression = "=ReSortForm2(Forms.frmManu.frmManu_Sub.Form)"

 

[The_Doc_Man]

In the Form_Open event, you have not (yet) loaded the controls, so anything referring to them is a problem. Form_Load event can see and manipulate the form's controls. Form_Open event cannot. Note that the form's properties are available in Form_Open, so you could move or resize the form if needed. But anything attempting to touch the controls is premature.

At least, that is the way I understand it and it HAS worked for me as though that is correct.

 

[MarkK]

If you perform a task in ObjectA triggered by a member of ObjectA, then do all your configuration in ObjectA. Consulting a fragile chain of references to set a property of a control at the end of that same fragile chain is an unforced error.

 

[KitaYama]

In the Form_Open event, you have not (yet) loaded the controls,

If I can set it ="" and there's no error, then I assume the problem is not the form being loaded or not.
And to my knowledge (if I'm not wrong), sub form is loaded prior to main form.
When I run a code in main form's on open event, I have a feeling the sub form is fully loaded and accessing its controls should not raise errors.

So far, @Edgar_ 's suggested code works fine, But still I have no idea why something that works in property sheet, should throw an error when used in vba.

Thanks again.

 

[KitaYama]

If you perform a task in ObjectA triggered by a member of ObjectA, then do all your configuration in ObjectA. Consulting a fragile chain of references to set a property of a control at the end of that same fragile chain is an unforced error.

I simply am trying to set a function to all label's of a subform, when the main form opens.
If I understand your advice, and you're asking me to run the code in sub form's on open event (and not from main form's), then I think in my situation I'll face more serious problems.

Thanks.

 

[Josef P.]

I would raise an event in the subform and react to this event in the main form.
Then nothing has to be set dynamically if the subform is used for several main forms.

Example:

Code in subform:

Public Event ReSort(ByVal ClickedControl As Control)

Private Function RaiseReSort()
   RaiseEvent ReSort(Me.ActiveControl)
End Function

.. use =RaiseReSort() as value for property OnClick of label controls

Code in main form:

Private WithEvents m_SubForm As Form_YourFormUsedInSubform

Private Sub Form_Load()
   Set m_SubForm = Me.YourSubFormControl.Form
End Sub

Private Sub m_SubForm_ReSort(ByVal ClickedControl As Control)
   MsgBox ClickedControl.Name
End Sub

 

[Edgar_]

But still I have no idea why something that works in property sheet, should throw an error when used in vba.

I’d be lying if I said I understand why things are so inconsistent. I can come up with a bunch of theories, but here's what I do know: there's a difference between how references work in Expressions and Queries. VBA is mostly stable in this regard, I can use several styles for my references with it. It's the expressions in GUI fields where the problems arise.

Because of this inconsistency between UI and VBA, I always use dot syntax for my references. When I need IntelliSense in queries or the expression builder, I use bangs. Once I have the names right, I switch back to dots to keep things consistent in Access and across other Office products.

This approach has worked well for me. Dot and bang notation seem stable, but references inside quotes often aren’t,

Edit:
This should work =ReSortForm2(Forms('frmManu'))
But it would throw an error if I try to chain child objects to the reference.

 

[MarkK]

Here's code having he same outcome, but on the suform...

Private Sub Form_Open(Cancel As Integer)
    Me.lblSortRec_ID.OnClick = "=ReSortForm2([Form])"
End Sub

Or simpler still, and the way Access is designed to be used...

Private Sub lblSortRec_ID_OnClick()
    ReSortFrom2 Me
End Sub

Choose wisely,

 

[MarkK]

I would raise an event in the subform and react to this event in the main form.

The click event of a subform control calls a public method in a standard module passing in a reference to the subform. How is involving the main form an advantage in any way?

 

[Josef P.]

I interpreted the original code as meaning that a certain function should be set in the main form - perhaps because the form is used several times in different main forms.

If only ReSortFrom2 is to be used, I think your suggestion in #9 is the right way to go.

/edit:
The form reference from #9 can of course also be transferred to any other function configured by main form:

Private Sub Form_Open(Cancel As Integer) ' <- Code in main form

    Dim Expression As String

    Expression = "=ReSortForm2([Form])"
    Me.frmManu_Sub.Form.Controls("lblSortRec_ID").OnClick = Expression

End Sub

I don't think it's an elegant implementation to set a subform property from the main form to get the expected behavior for different main forms.
Strictly speaking, it is not a violation of encapsulation because the OnClick property is public, but I would still avoid it.

 

[MarkK]

: )

 

[KitaYama]

Here's code having he same outcome, but on the suform...

Private Sub Form_Open(Cancel As Integer)
    Me.lblSortRec_ID.OnClick = "=ReSortForm2([Form])"
End Sub

Or simpler still, and the way Access is designed to be used...

Private Sub lblSortRec_ID_OnClick()
    ReSortFrom2 Me
End Sub

Choose wisely,

The same sub form is used in different main forms.
If I want to run the code in sub form, I have to check if the form is used as a sub form and add the parent to expression too.
I simply thought running the code in main form would be easier, because the main form, always has the same sub form, but the sub form is used in different main forms, hence has different parents.

thanks.

I'm experimenting with @Josef P.'s solution, but looking at it so far, I have two problems:

.. use =RaiseReSort() as value for property OnClick of label controls

1- It seems I have to add =RaiseResort() manually to all labels, (which I tried to use code instead)
2- Me.ActiveControl would throw error, because labels can not have focus.

While @Edgar_'s solution works fine, as a learning material I'll try to see if I can use suggested code by @Josef P.

Thanks for all the help.

 

[Josef P.]

Me.ActiveControl would throw error, because labels can not have focus.

That's right, I hadn't thought of that.
If you need the control (it would not be necessary in the code from #1):

Code in subform:

Public Event ReSort(ByVal ClickedControl As Control)

Private Function RaiseReSort(ByVal ClickedControl As Control)
   RaiseEvent ReSort(ClickedControl)
End Function

With control property OnClick: =RaiseReSort([lblSortRec_ID]) .. or the [Event Procedure] variant (see #9).

If the clicked label is not required:

Public Event ReSort()

Private Function RaiseReSort()
   RaiseEvent ReSort
End Function

 

[KitaYama]

That's right, I hadn't thought of that.
If you need the control (it would not be necessary in the code from #1):

Code in subform:

Public Event ReSort(ByVal ClickedControl As Control)

Private Function RaiseReSort(ByVal ClickedControl As Control)
   RaiseEvent ReSort(ClickedControl)
End Function

With control property OnClick: =RaiseReSort([lblSortRec_ID]) .. or the [Event Procedure] variant (see #9).

If the clicked label is not required:

Public Event ReSort()

Private Function RaiseReSort()
   RaiseEvent ReSort
End Function

Still 2 problems.

1- I have to add =RaiseReSort([LabelNameHere]) to all labels manually. All I'm trying to do is do it dynamically. I have 289 forms and can not go through all of them and correct them.
2- Your code raised error Object requiered (no error number) on this line :RaiseEvent ReSort(ClickedControl)

 

[Josef P.]

I have to add =RaiseReSort([LabelNameHere]) to all labels manually.

Do you need the labels for later processing?
In the code you showed in #1, this was not the case.

You could also customize this property using VBA code in the design view of the form.
There is also the option of activating event handling at runtime.
If you describe what you actually want to do, there may be a completely different solution.

 

[KitaYama]

Do you need the labels for later processing?
In the code you showed in #1, this was not the case.

I didn't explain it there, because my main concern and question was the difference between adding an expression in property sheet and in vba.
My question was : Why something that works in property sheet, I'm not able to add it via code.
I think I was clear on my question.

A little about my situation:
I have a function that receives a form as a parameter, sorts the result of the form based on the label being clicked and do some other jobs.
All labels' name that can be used for sorting, starts with lblSort.....
For example, lblSortDrawingNo, lblSortProductNo, lblSortQuantity, etc.

We also have a function that will run when a form is opened. It uses a loop to add the function to on click of the labels:

Public Sub SetSortLables(frm As Access.Form)

    Dim ctrl As Control
    Dim Expression As String

    For Each ctrl In frm.Controls
        If ctrl.ControlType = acLabel Or ctrl.ControlType = acCommandButton Then
            Select Case Left(ctrl.Name, 7)
                Case "lblSort", "cmdSort", "btnSort"
                    Expression = "=ReSortForm (Forms." & frm.Name & ",'" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"
                    ctrl.OnClick = Expression
            End Select
        End If
    Next

End Sub

This works fine.
I was trying to add the same functionality to the sub forms, but the expression I'm trying to create, should contain the name of the main form and sub form. That was where I noticed what I can write in property sheet, could not be used in vba.

So I tried to understand what's the difference.

Thanks for your help and sorry for wasting your time.

 

[Josef P.]

Then the suggestion from #9 fits in my opinion:

Public Sub SetSortLables(frm As Access.Form)

    Dim ctrl As Control
    Dim Expression As String

    For Each ctrl In frm.Controls
        If ctrl.ControlType = acLabel Or ctrl.ControlType = acCommandButton Then
            Select Case Left(ctrl.Name, 7)
                Case "lblSort", "cmdSort", "btnSort"
                    'Expression = "=ReSortForm (Forms." & frm.Name & ", '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"
                    ' should be the same:
                    Expression = "=ReSortForm ([Form], '" & Right(ctrl.Name, Len(ctrl.Name) - 7) & "')"
                    ctrl.OnClick = Expression
            End Select
        End If
    Next

End Sub

 

[CJ_London]

Not mentioned and perhaps does not apply in this case but labels bound to a control do not have events

 

[MajP]

The problem has to do with the parentheses in an expression. The following is tested and works fine without any single or double parentheses. I put the click on a textbox.

Private Sub Form_Load()
  Dim expression As String

'Works without quotes
' expression = "= ResortForm(Forms![FrmManu]![FrmManu_Sub].Form)"

'Does Not work using single quotes
' expression = "= ResortForm(Forms('FrmManu')![FrmManu_Sub].Form)"

' Does not work using double quotes
  expression = "= ResortForm(Forms(" & Chr(34) & "FrmManu" & Chr(34) & ")![FrmManu_Sub].Form)"
  Debug.Print expression

  Me.frmManu_Sub.Form.Controls("txtSorterRec_ID").OnClick = expression
End Sub