Solved Setting on click event of a control in VBA

[KitaYama]

The following is tested and works fine. I put the click on a textbox.

Thanks. I don't have Access here. I will test it as soon as I'm back to office.

 

[KitaYama]

I guess no one else bothered to read the original error message which was clear syntax issue.

I think @Edgar_ noticed that and got rid of the quotes in #2.

But to be honest, my question was more of why I can type the same syntax in property sheet, but not in VBA.
I was expecting to hear from @isladogs it's a bag or something, but it seems it's one of inconsistency between UI and VBA. (#8)

 

[MajP]

Actually I lied to you. You can write an expression in design view with single or double quotes and forcing the double quotes does not solve the problem. However the solution without quotes does work.

 

[MajP]

I cannot figure out the syntax using quotes. You could do something like this instead and pass this as strings.

expression = "= ResortFormNew('FrmManu','FrmManu_Sub')"

Public Function ResortFormNew(Mainfrm As String, SubfrmCtl As String)
  Dim frm As Access.Form
  Set frm = Forms(Mainfrm).Controls(SubfrmCtl).Form
  debug.print frm.Name
  'other code
End Function

 

[KitaYama]

You could do something like this instead and pass this as strings.

Yes, That was one of my first tries.
For now, I think the suggestion in #9 is the simplest. (running from sub form)

Expression = "=ReSortForm2([Form]"

This one passes the subform to the function and the function can work on it without knowing the parent form.

I'll check your version too when I'm back to office.

Million thanks for your help

 

[MajP]

For now, I think the suggestion in #9 is the simplest. (running from sub form)

Expression = "=ReSortForm2([Form]"

Yes they are basically two versions of the same solution addressing the cause of the problem. They allow you to pas form reference from the expression assigned at runtime that does not include the " or '.

either
expression = "= ResortForm([Form])"
expression = "= ResortForm(Forms![FrmManu]![FrmManu_Sub].Form)"

passes a reference to the subform without " or '.
I am not sure why the syntax with ' or " does not work.