Solved Run Time Error 438 when calling a sub from another form (1 Viewer)

[allanc]

Hello Friends,

I am receiving the following error when trying to call a public sub from another form.

The code is as below:

Forms![Main_Form]![Sub_Form].PublicSubCreated_Click

Wondering what possibly went wrong here? I had no problem calling another function/sub within one form but seems like having troubles correctly referencing another form here.

Thanks!

p.s: new interface looks great!

 

[Micron]

was the other form a subform? Try
Forms![Main_Form]!SUBFORM_CONTROL_NAME.Form.PublicSubCreated_Click where SUBFORM_CONTROL_NAME is the name of the subform control that contains the subform. It may or may not be named the same as the subform itself.

 

[allanc]

was the other form a subform? Try
Forms![Main_Form]!SUBFORM_CONTROL_NAME.Form.PublicSubCreated_Click where SUBFORM_CONTROL_NAME is the name of the subform control that contains the subform. It may or may not be named the same as the subform itself.

Wow Thanks so much!

So I was missing the "Form" in between...

I had no problems referencing a value from another sub form using:

Forms!MainForm![SUB FORM]!ANumber.Value

Not sure why the syntax is different there - is there anything on the internet that I can read to learn more about these kind of things?

I also had hard time understanding the quotation marks like below:

' " & VariableA & " '

I use these quotation marks to make codes work but do not understand when it's necessary.

Once again, thank you for solving my problems! Have a great evening!

 

[Micron]

I also had hard time understanding the quotation marks

Any text or date/time type data needs to be delimited (quotes for text or octothorpes for dates) to be usable in sql or expressions. The difficulty often lies in concatenation, which is why it's always a good idea to assign sql (and sometimes expressions) to variables and/or Debug.Print them out for examination. Since a string must start and end with " as in "string here" any reference (e.g. field or variable name) that comes between the beginning and end must be delimited if it is not a number. Using single quotes in the case of text makes this far easier. So if str1 and str2 are string variables then it must be evaluated as "...WHERE field1 = "pet" and field2 = "dog"" but the in-between quotes (and double at the end are incorrect)
Using single quotes, you try to have it interpreted as
"...WHERE field1 = 'pet' AND field2 = 'dog' " (extra spaces for clarity only - Access will remove them)
However, each part must begin and end in double quotes as I said, so you must write
"...WHERE field1 = ' " & str1 & " ' AND field2 = ' " & str2 & " ' "
Looking at each portion, you have
...WHERE field1 = '
pet
' AND field2 = '
dog
'
Concatenate them and you get
...WHERE field1 = 'pet' AND field2 = 'dog'. You can prove that by copying/pasting the paragraph, go to end of each line in turn and delete the space at the end of each line. It should all fall into place.
Hope that helps with the delimiting part. Same approach for dates but as I said, using # rather than '
You can use double quotes entirely but it gets real messy and since I don't I'm not about to attempt to cover it here.

I had no problems referencing a value from another sub form using Forms!MainForm![SUB FORM]!ANumber.Value

I would go back and look at that again as it makes no sense to me. The 3rd parameter refers to the subform control. The only things I can foresee retrieving at that level would be properties of that control. To dig down to the subform itself requires invoking the .Form part AFAIK, then whatever you want to know about the subform itself. Most of what you'll find in research is examples where someone had a problem. I've cobbled together some info about the subject, which may help.

referencing CONTROLS on subform:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

Can also use what I call collections based syntax (if anyone knows the proper term, please let me know)
e.g. to get the record count of a subform:
Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount

NAVIGATION FORMS
[Forms]![NavigationForm]![NavigationSubform].[Form]![controlName]
(you cannot use a nav button for triggering code for this - it causes referenced form to unload)
Most nav forms are named NavigationForm by default and users don't bother to change the name or know they can.
[NavigationSubform] is akin to .Form (see 1st example above) thus should be used verbatim.

 

[moke123]

I'll second Microns advice to debug.print your expression before even trying to execute it. I always use double quotes and agree it gets messy on occasion but its much simpler to construct when you can see what it actually resolves to. Be aware that there is one important exception to using single quotes. If the text field in question is likely to have an apostrophe in it you must use double quotes. This is usually for names like O'Mally or O'Brien, etc.

 

[Micron]

If the text field in question is likely to have an apostrophe in it you must use double quotes.

Good point; I forgot about that condition. Interesting that you prefer something like
"""...WHERE field1 = """ & sql1 & """ AND field2 = """ & sql2 & """"
but I guess if you want a one size fits all approach that would be the way to go.

 

[moke123]

It took a long time before I could get it right on the first attempt. Its still hard which is why Debug.print is so important. I'm always surprised when people dont use it.

 

[The_Doc_Man]

@allanc - another factor is this:

It is possible for you to explicitly call routine SubA in the class module of the (hypothetical) form FrmA. If you remember to declare SubA as Public, you can even make that call from a different form called FrmB. But if SubA uses the "Me." construct anywhere inside of itself, that reference won't work correctly in the external call. It fails because whatever you can reference in SubA locally via Me.xxxx will be part of FrmA. Calling SubA from FrmB, the controls of FrmA (that would be implied by a "Me." reference) are not in scope for FrmB.

Calling a subroutine defined in a sub-form IS calling externally because that sub-form was developed as a separate and independent form. You can use it with a parent form only because there is such a thing as a sub-form control that supports such linkage. But that control DOESN'T fix the problems of bad use of the ME. references.

The error you mention COULD be caused by a bad cross-form "Me." reference in that (to Access) it would look like you were trying to use Me.xxxx, and since .xxxx is not in scope on that external call, it appears to be an unsupported property.

Therefore, check whether SubA is using a Me. reference or an unqualified (unprefixed) reference to a control on the form.

 

[allanc]

Any text or date/time type data needs to be delimited (quotes for text or octothorpes for dates) to be usable in sql or expressions. The difficulty often lies in concatenation, which is why it's always a good idea to assign sql (and sometimes expressions) to variables and/or Debug.Print them out for examination. Since a string must start and end with " as in "string here" any reference (e.g. field or variable name) that comes between the beginning and end must be delimited if it is not a number. Using single quotes in the case of text makes this far easier. So if str1 and str2 are string variables then it must be evaluated as "...WHERE field1 = "pet" and field2 = "dog"" but the in-between quotes (and double at the end are incorrect)
Using single quotes, you try to have it interpreted as
"...WHERE field1 = 'pet' AND field2 = 'dog' " (extra spaces for clarity only - Access will remove them)
However, each part must begin and end in double quotes as I said, so you must write
"...WHERE field1 = ' " & str1 & " ' AND field2 = ' " & str2 & " ' "
Looking at each portion, you have
...WHERE field1 = '
pet
' AND field2 = '
dog
'
Concatenate them and you get
...WHERE field1 = 'pet' AND field2 = 'dog'. You can prove that by copying/pasting the paragraph, go to end of each line in turn and delete the space at the end of each line. It should all fall into place.
Hope that helps with the delimiting part. Same approach for dates but as I said, using # rather than '
You can use double quotes entirely but it gets real messy and since I don't I'm not about to attempt to cover it here.
I would go back and look at that again as it makes no sense to me. The 3rd parameter refers to the subform control. The only things I can foresee retrieving at that level would be properties of that control. To dig down to the subform itself requires invoking the .Form part AFAIK, then whatever you want to know about the subform itself. Most of what you'll find in research is examples where someone had a problem. I've cobbled together some info about the subject, which may help.

referencing CONTROLS on subform:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

Can also use what I call collections based syntax (if anyone knows the proper term, please let me know)
e.g. to get the record count of a subform:
Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount

NAVIGATION FORMS
[Forms]![NavigationForm]![NavigationSubform].[Form]![controlName]
(you cannot use a nav button for triggering code for this - it causes referenced form to unload)
Most nav forms are named NavigationForm by default and users don't bother to change the name or know they can.
[NavigationSubform] is akin to .Form (see 1st example above) thus should be used verbatim.

Good morning Friend,

Thank you for your help!! I really appreciate the time and effort you put here. I now have a much better understanding on these quotations marks in SQL queries. I will try debug.print later today - this is something I have seen but never thought about using.



The code I used to reference a value from another sub form is like this.

-> [Main_Form] is the main form
-> [Sub_Form] is a sub form under [Main_Form]

Forms![Main_Form]![Sub_Form]!VariableA.Value = Forms![Main_Form2]![Sub_Form2]!VariableB.Value

I am maintaining a rating tool to calculate premium. The code above is used to retrieve rating information that's saved on a table(Sub_Form2).

Once again thank you for your time.

 

[allanc]

@allanc - another factor is this:

It is possible for you to explicitly call routine SubA in the class module of the (hypothetical) form FrmA. If you remember to declare SubA as Public, you can even make that call from a different form called FrmB. But if SubA uses the "Me." construct anywhere inside of itself, that reference won't work correctly in the external call. It fails because whatever you can reference in SubA locally via Me.xxxx will be part of FrmA. Calling SubA from FrmB, the controls of FrmA (that would be implied by a "Me." reference) are not in scope for FrmB.

Calling a subroutine defined in a sub-form IS calling externally because that sub-form was developed as a separate and independent form. You can use it with a parent form only because there is such a thing as a sub-form control that supports such linkage. But that control DOESN'T fix the problems of bad use of the ME. references.

The error you mention COULD be caused by a bad cross-form "Me." reference in that (to Access) it would look like you were trying to use Me.xxxx, and since .xxxx is not in scope on that external call, it appears to be an unsupported property.

Therefore, check whether SubA is using a Me. reference or an unqualified (unprefixed) reference to a control on the form.

Good morning,

Thank you for your input here. The sub I was trying to call didn't have any "Me." but it's good to keep that in mind for future.

Allan

 

[allanc]

Hmm out of top question - Did they remove "mark it as solved" and the button to thank a person?

 

[Micron]

But if SubA uses the "Me." construct anywhere inside of itself, that reference won't work correctly in the external call.

Did you test that recently or maybe it's a version thing. Works for me.

Public Sub Command0_Click()
MsgBox Me.Name
End Sub

 

[The_Doc_Man]

It's a matter of scoping. Put two different controls on Form2 and Form3 and see if both of them can be referenced using Me.xxx syntax. Me.Name is present for every form (and report, for that matter) but controls specific to one form shouldn't be capable of being referenced that way from another form.

I know you can't do it if you call a sub using Me. from inside a general module because in that context, Me. has no proper referent.

Unless MS in their infinite wisdom changed THAT rule and didn't tell ME when they did it. Typical disrespect.

 

[Micron]

Put two different controls on Form2 and Form3 and see if both of them can be referenced using Me.xxx syntax.

Not sure if this is what you're suggesting but it doesn't seem to make any difference.
Called form:

Public Sub Combo3_AfterUpdate()
MsgBox Me.Combo3
End Sub

Calling form calls form2 combo afterupdate even though the value was already selected:

Private Sub Combo2_AfterUpdate()
Forms!form2.Combo3_AfterUpdate
End Sub

 

[The_Doc_Man]

From my understanding of the scoping rules, that seems like it shouldn't happen.

 

[Micron]

Then I guess the rules changed somewhere along the line; i.e. maybe it is possible now in some situations but was not before. I have version 2003 on an old laptop and can try it there if you're interested.

Not only did I recall that it was possible because I've been in such discussions before (thus I had to test to be sure I was understanding your claim) I could not imagine why Me.something would not work in the called sub. After all, Me is native to the class where the event being called, as long as that class is a form or report I suppose. I agree completely that Me has no context in a standard module, and though I've never tried, it's probably not allowed to be used in a custom class either. I'd suspect it's reserved in some way but that's just a guess.

 

[The_Doc_Man]

By any chance was the experiment you performed one in which one form was the sub of another form? Or did you try your call across two parent-level forms?

 

[Micron]

2 forms. See image - form2 and form2.
Value in combo on 2 is 3. Value on form3 is 1. AfterUpdate of form3 combo calls form2.AfterUpdate and message box reports the value of form2, not form3. Code above pics attempts to show that.