Generic way to refer to subform control in subform criteria (1 Viewer)

bongbang

Registered User.
Local time
Today, 06:26
Joined
Dec 28, 2017
Messages
17
In Access 2016, I implemented a cascading combo box in a subform by specifying the row source's criteria as follows

Code:
[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.
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:26
Joined
Jul 9, 2003
Messages
16,245
I'm not sure your code Below is correct?

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

The Bang Here:- ]!My_Subform_Control] donsen’t look right?

Also, I don't think it truly represents the situation.

Microsoft creates a special Control
My reason for thinking this is when you add a subform to main form Microsoft creates a special control, an intermediary control between your main form and subform. The problem is, Access gives this control the same name as the form (the subform) that is housed within it!

It's just a normal Form!
When you see a main form with another form on it, it is natural to term it a sub-form. But really there's nothing special about the form itself, except for the fact that you can see it on top of your main form. But otherwise, it's just a normal form!

A hidden Control you cannot see
There's a hidden control you can't see, around the form. It looks like a frame, a border, which you can click on and if you click on it just right (a difficult skill to master) but if you manage to select it you will see that it is called a subform/subreport control. You will also notice it has the same name as the form that resides within it.

Microsoft - not at all helpful!
This "helpfulness" provided by Microsoft Access, is as you can see, not at all helpful! It is so misleading and so confusing there's no wonder people have trouble with subforms and subreports!

A Subform Window
I immediately rename this control to something which explains exactly what it is, in terms of what you are seeing. To me, it is a window which contains the subform. I call it a subform window and I give it the naming convention prefix subFrmWinFormName where "FormName" is the name of the form housed within it.

Now with this information, rewrite your code in this manner:-

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

For More, See My Blog on Subforms:- http://www.niftyaccess.com/sub-forms/

And my YouTube Playlist HERE:- https://www.youtube.com/playlist?list=PLhf4YcS5AjdojT2lrV5j1VqDfC1h3Yk1A
Basically a compilation of any of my videos that mention a Subform

Bangs and Dots
I'm never sure if I get them right however I use this Simple Rule:- If it's a name I've given it, use a bang if it's an access name then use a Dot. However I'm never %100 sure if that works throughout MS Access in all situations. For more information read this:-

Understanding Form Structures and Coding

http://c85.cemi.rssi.ru/access/books/A97ExSol/index12.htm
 
Last edited:

bongbang

Registered User.
Local time
Today, 06:26
Joined
Dec 28, 2017
Messages
17
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

Registered User.
Local time
Today, 09:26
Joined
Oct 19, 2012
Messages
828
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

Super Moderator
Staff member
Local time
Tomorrow, 00:26
Joined
Jan 20, 2009
Messages
12,849

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

Super Moderator
Staff member
Local time
Tomorrow, 00:26
Joined
Jan 20, 2009
Messages
12,849
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.:eek: It just makes no sense.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:26
Joined
May 7, 2009
Messages
19,175
6you probably should use Tempvars.
based you Dependent combo on tempvars:
Code:
SELECT field1, field2 FROM table2 WHERE someField=Tempvars!tvarParentCombo;

on the Independent combo, you set the Tempvars
on the AfterUpdate Event of this combo:
Code:
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:
Code:
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.
 
Last edited:

JHB

Have been here a while
Local time
Today, 14:26
Joined
Jun 17, 2012
Messages
7,732
Try the below:
Code:
[My_Subform].[Form]![My_Subform_Control]
 

bongbang

Registered User.
Local time
Today, 06:26
Joined
Dec 28, 2017
Messages
17
Try the below:
Code:
[My_Subform].[Form]![My_Subform_Control]

Doesn't work. Nor does the following:

Code:
[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

MVP / VIP
Local time
Today, 13:26
Joined
Jan 14, 2017
Messages
18,186
As I hinted back in post 2, it should just be

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

NOTE Do NOT enclose Forms or Form in square brackets
 

bongbang

Registered User.
Local time
Today, 06:26
Joined
Dec 28, 2017
Messages
17
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.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 13:26
Joined
Jan 14, 2017
Messages
18,186
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

Code:
Parent.MySubForm.Form.MySubFormControl

HTH but if not, good luck finding a solution
 

bongbang

Registered User.
Local time
Today, 06:26
Joined
Dec 28, 2017
Messages
17
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

Super Moderator
Staff member
Local time
Tomorrow, 00:26
Joined
Jan 20, 2009
Messages
12,849
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:

Code:
 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

Super Moderator
Staff member
Local time
Tomorrow, 00:26
Joined
Jan 20, 2009
Messages
12,849
If you don't want to specify the main form name from the subform, use 'Parent' instead

Code:
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

Have been here a while
Local time
Today, 14:26
Joined
Jun 17, 2012
Messages
7,732

Attachments

  • ComboboxTest.accdb
    476 KB · Views: 125

isladogs

MVP / VIP
Local time
Today, 13:26
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 09:26
Joined
Oct 19, 2012
Messages
828
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

Super Moderator
Staff member
Local time
Tomorrow, 00:26
Joined
Jan 20, 2009
Messages
12,849
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.
 

Users who are viewing this thread

Top Bottom