Generic way to refer to subform control in subform criteria

[bongbang]

In Access 2016, I implemented a cascading combo box in a subform by specifying the row source's criteria as follows

[Forms]![My_Main_Form].[Form]![My_Subform].[Form]![My_Subform_Control]

This works well for one main form, but doesn't work when the subform is reused by other main forms. Is there a way to refer to the subform control without specifying the main form so that the subform can be reused?

I've tried using "Me" and "[Forms]![My_Subform]" but it doesn't work. Apparently, what normally works in a regular VBA script doesn't work as a criteria's expression.

Thank you.

 

[isladogs]

You're overcomplicating this.
You shouldn't have two lots of 'Form.'

Also use Parent to refer to main form from subform.

See this link http://allenbrowne.com/casu-04.html

 

[bongbang]

UG,

You're right that it didn't look right, but that's just a typo and not the source of my problem. I've since fixed the original post.

As I said, that code (without the typo) works. It doesn't work only when the subform is reused in a different main form, and you can see why that's the case. Since the main form's name is specified in the expression, the code only works as intended when the subform is inside the named main form.

Normally, I should be able to replace "[Forms]![My_Main_Form].[Form]" w/ "Me" and avoid specifying the main form's name, but that doesn't work in this case. Apparently, the full path from [Forms] is required in the expression, since all the solutions I've seen use that syntax. (Search "How do I create simple Cascading Combo Boxes in Access" for example. I can't post a link as a newbie to the forum.)

 

[Solo712]

UG,

You're right that it didn't look right, but that's just a typo and not the source of my problem. I've since fixed the original post.

As I said, that code (without the typo) works. It doesn't work only when the subform is reused in a different main form, and you can see why that's the case. Since the main form's name is specified in the expression, the code only works as intended when the subform is inside the named main form.

Normally, I should be able to replace "[Forms]![My_Main_Form].[Form]" w/ "Me" and avoid specifying the main form's name, but that doesn't work in this case. Apparently, the full path from [Forms] is required in the expression, since all the solutions I've seen use that syntax. (Search "How do I create simple Cascading Combo Boxes in Access" for example. I can't post a link as a newbie to the forum.)

bongbang,
you should be aware that the reference notation changes depending on the position w. respect to main form/subform. For a very very good summary of the correct positional references see: http://access.mvps.org/access/forms/frm0031.htm

Best,
Jiri

 

[Galaxiom]

Understanding Form Structures and Coding
http://c85.cemi.rssi.ru/access/books/A97ExSol/index12.htm

Everyone should ignore the explanation of dots and bang in that article. It is one of many contributions of misinformation adding to the confusion about the differences.

The bang is a Late Bound reference to the Default Member of an object. In the case of a form that is first to its Controls Collection then to its Recordset.

The dot is an Early Bound Reference to a Member of an object. The controls, properties and recordset are all members which is why they work with the dot.

In the case of Forms collection, the items in the collection are only loaded during runtime so the bang is technically more appropriate thought the dot works.

Otherwise it is generally better to use the Early Bound reference because any errors will be detected during the Compile instead of waiting to raise errors at runtime.

 

[Galaxiom]

Further to my previous post:

The following statement (b) is easily proven incorrect:

Use the bang when the object name (form name, control name, etc.) is known in advance, as in Forms!frmCust, for two reasons: (a) it is the shortest structure, thus requiring the least keystrokes; and (b) Access VBA compiles the absolute relationship between the objects and executes the code faster than other syntax variations.

The bang is Late Bound, hence the subsequent term is not even considered during compilation, let alone "forming an absolute relationship". An invalid object name after a bang will not cause a compile error. Try it for yourself.

I also strongly disagree with the advice about referring to forms via their code module. Such references can easily cause a hidden instance of the form to load if none is already present in the Forms collection.

They actually argue that referring to a Member of a form by what is effectively an unnamed instance of the object is the correct way. It just makes no sense.

 

[arnelgp]

6you probably should use Tempvars.
based you Dependent combo on tempvars:

SELECT field1, field2 FROM table2 WHERE someField=Tempvars!tvarParentCombo;

on the Independent combo, you set the Tempvars
on the AfterUpdate Event of this combo:

Private Sub Independent_AfterUpdate()
    If IsNull(TempVars("tvarParentCombo")) Then
        TempVars.Add "tvarParentCombo", 0
    End If
    TempVars("tvarParentCombo").Value = Me.IndependentCombo.Value
    Me.DependentCombo = Null
    Me.DependentCombo.Requery
End Sub

this way you don't need to worry about Reference to [Form].

when you are done with Tempvar you can delete it
when the form closes:

Private Sub Form_Close(Cancel As Integer)
On Error Resume Next
Tempvars.Remove "tvarParentCombo"
End Sub

Add:

since there are other forms consuming your combo,
use different Tempvars Variable names
on each instance of your combo. As you know,
tempvars are Public variables.

 

[JHB]

Try the below:

[My_Subform].[Form]![My_Subform_Control]

 

[bongbang]

Try the below:

[My_Subform].[Form]![My_Subform_Control]

Doesn't work. Nor does the following:

[Forms]![My_Subform].[Form]![My_Subform_Control]

I guess I'll have to write a VBA script, which is not the end of the world. I just thought using query criteria was an elegant solution.

 

[isladogs]

As I hinted back in post 2, it should just be

Forms![My_Main_Form].[My_Subform].Form.[My_Subform_Control]

NOTE Do NOT enclose Forms or Form in square brackets

 

[bongbang]

Ridders,
That's hardly different from what I had originally. Since it also specifies the main form by name, it will also suffer from the same drawback, which is the point of this thread.

By the way, brackets are actually okay. I wouldn't put them on were I to write the thing entirely by myself, but Access auto-completes that way.

 

[isladogs]

Going back to post 1, you incorrectly used Form twice in your code and both times in square brackets. That's why I made the comment.

If you don't want to specify the main form name from the subform, use 'Parent' instead

Parent.MySubForm.Form.MySubFormControl

HTH but if not, good luck finding a solution

 

[bongbang]

My original code is not "incorrect" with regard to the use of "Form" or brackets. It is the abstracted result of Access's auto-completion and it works.

(It did contain a small typo that occurred UG noticed and that occurred as a result of my own abstraction. The one missing matching bracket has since been added back.)

Your suggestion of using "Me.Parent" here would not work here as a query criteria . The "[Forms]![My_Main_Form].[Form]!" in my code is there to help the Access query find the subform. Using "Me" would assume that it knows the subform already. If that were the case, I wouldn't have needed to refer to the main form to begin with, since the referenced control is in the subform.

The solution here is modify recordsource with each update using VBA instead of referring to the control in the query criteria. In a VBA sub run from the subform, "Me" would work as expected.

 

[Galaxiom]

Going back to post 1, you incorrectly used Form twice in your code and both times in square brackets. That's why I made the comment.

Including Form in the unexpected location is (for want of a better word) a vestigial syntax. There is absolutely nothing wrong with it.

The square brackets are fine too. They are generic delimiters and can go around anything, including Properties. They are not "object delimiters".

Just because a particular syntax is not included in the best known online guides, it doesn't mean it is invalid. Best test for yourself before claiming someone has used an invalid syntax.

For example, Form can optionally be left out references to controls on subforms, provided the bang is used to call the default member of the subformcontrol (ie Form). This syntax works:

 Forms!mainformname.subformcontrolname!controlname

You won't see that in the usually quoted sources.

Always be sceptical of "accepted wisdom" and never assume anything.

 

[Galaxiom]

If you don't want to specify the main form name from the subform, use 'Parent' instead

Parent.MySubForm.Form.MySubFormControl

When an SQL statement string is used as a RowSource, Access processes it in the Application context, thus requiring objects to be referred to via the Forms Collection. Parent is only scoped to the Form, so the Application cannot interpret it.

Different environments encountered by the subform can be dealt with using VBA in a number of ways. Arnelgp has shown the basic concept implemented using TempVars.

Another variant uses a saved parameterised query as the RowSource and code to let the parameter value instead of the TempVar.

A third is to build the SQL RowSource on demand.

Efficiency rarely counts for much in a RowSource but the parametrised query would easily be the most efficient because its has a prebuilt query plan and very simple code. Puts a bit more clutter in the navigation pane though.

 

[JHB]

Doesn't work.

By me it does, test database attached, (did you remember to change the names to fit yours)?

 

[isladogs]

First of all, apologies for any mistakes in my previous post
At the risk of getting more flak for replying ...

Your suggestion of using "Me.Parent" here would not work here as a query criteria . The "[Forms]![My_Main_Form].[Form]!" in my code is there to help the Access query find the subform. Using "Me" would assume that it knows the subform already. If that were the case, I wouldn't have needed to refer to the main form to begin with, since the referenced control is in the subform.

Of course it wouldn't work in a query.
It wasn't intended for that purpose
I believed you wanted a way of referring to main form controls from a subform where the subform was being used in more than one main form.

The quote below was one of your comments I was trying to address (albeit not very well it seems!!)

As I said, that code (without the typo) works. It doesn't work only when the subform is reused in a different main form, and you can see why that's the case. Since the main form's name is specified in the expression, the code only works as intended when the subform is inside the named main form.

 

[Solo712]

At the risk of appearing cynical, why has no-one asked about the relationship between the "main forms" and the "re-used" subforms? If the OP author was copying something from some website, it may very well be that he applied the method of cascading cbos for a subform (where in the model it was done on a main form) thinking that he had to preserve the reference to the main form in the subforms which he evidently does not have to unless the variant "main forms" actually modify the top of the cascading combos in the subform. If that is the case it can be handled by the way Arnel suggested. But it is not a given. So let me ask : do the "main forms" restrict the combos in the subforms ? If not, then create the repeating subform as if it was a main form, create the AfterUpdate VBA code for the cascading combos, test it and then insert it into whatever (actual main) form you want. There should be no problem with that.

Best,
Jiri

 

[Galaxiom]

At the risk of appearing cynical, why has no-one asked about the relationship between the "main forms" and the "re-used" subforms?

Excellent point. Sometimes the need to step back a little and see the context isn't immediately obvious.