How to filter a main form from a value in a subform based on a combo control in the main form

[June7]

Probably have to provide db for analysis.



I never use expression builder.

[WCStarks]

Here is the sanitized copy of the database. The issue deals with the Sites Navigation sub-form. The text field [TxtID], in question, with the Data Source expression, is in the header of the Sites Navigation sub-form.

The objective is to have the current record in the [Assets_Trans_Sites] sub-form, bring up the corresponding Asset record in the [Site_Asset] detail form below, for viewing and editing, starting with the first [Asset_Trans-Sites], (displayed as Assets) record in the middle form, and changing as I browse through the [Assets_Trans_Sites] form records.

To get something to work with, search for a Site in the Assets Navigation sub-form header, GoTo combo-box, for "Lake Mnt-FS". That will bring up a list of "Assets" obtained from the [Assets_Trans_Sites] form, which includes some details from the Assets table records.

FYI: I have two other similar transaction sub-forms, with differing content: [Assets_Trans_Assets] and [Assets_Trans_Mem], used in their respective contexts.

When you open the db, it will default to the Members, ([SCATeam]) Navigation Sub-form.
SCATeam_Cleaned.zip

[June7]

Assets tab GoTo does not list sites, it lists assets. Filter_Site lists sites. Its click event triggers input popup because it incorrectly references Assets_Trans.Site. The code is trying to set filter on Site field (Me.Filter = …). Site field is not in table Assets which is the RecordSource. So what is purpose of this combobox? Perhaps code should be:

Private Sub Filter_Site_Click()
    Me.Assets_Trans_Assets.Form.FilterOn = False
    Me.Assets_Trans_Assets.Form.Filter = "[Site_ID] = " & Me.Filter_Site
    Me.Assets_Trans_Assets.Form.FilterOn = True
End Sub

The code runs and can see filter of the subform is set. However, Assets_Trans_Assets subform container Master/Child links restrict records to the Asset of its parent form. So setting filter on Site really doesn't make sense.

If Assets and Assets_Trans have a 1-to-1 relationship, why split into two tables?

You need to run Debug>Compile and fix other errors.

However, none of this seems to address your original question of "How to filter a main form from a value in a subform based on a combo control in the main form", which is itself a confusing statement.

[WCStarks]

I am not sure why you went to the Assets tab, as the issue relates to the Sites tab. Yes, the GoTo box on the Assets tab allows selection of Assets. However, the GoTo box on the Sites tab allows selection of Sites.
But since your are there, I'll try to address your concerns related to the Assets Tab.
Assets Tab:
The Asset_Group form on the Assets Navigation Tab, filters assets grouped with the current asset tracking record, based on the text field Group. The Master and Child fields of the Asset_Groups sub-form are both set to [Group], which automatically filters the the assets for the Asset_Groups sub-form. When I click on the BarCode field in the Asset_Groups sub-form, it moves that asset to the Assets Navigation sub-form, to view and edit all the details of that Asset.

The Site Filter on the Assets Navigation Form doesn't work. Feedback I got earlier, indicated it cannot work this way, so I had abandoned this approach, but had neglected to delete this feature in the meantime. That is why I switched to work with the assets by site, from the Sites Navigation sub-form instead. It makes more sense to do it there anyway, plus I can see all the assets assigned to the site in the Assets_Trans_Sites continuous sub-form, which is nicer, than just being able to see one at at time under the Assets Tab.

Assets_Trans table is in a many-to-one relationship with the Assets table. It currently appears to be in a 1-1 relationship, only because I initially have just one Assets_Trans record for each Asset. That will change over time, as the status of assets change. I intend for the Assets_Trans table to keep a history of changes to asset statuses. The assets need a lot of work yet, to get them properly inventoried and documented. I hope that clarifies your concerns with the Assets Navigation sub-form.

Please, now, go to the Sites Navigation tab and look at the issue with the [TxtID] data source expression. To get a list of Assets in the middle screen to play with, select the "Lake Mnt-FS" site from the GoTo combo-box. Please review my previous post to understand what I would like to have happen.
Thank you for taking your time to help me.

[June7]

I went to Assets tab because instructions in post 32 said to (emphasis added):

To get something to work with, search for a Site in the Assets Navigation sub-form header

The Assets form on the Sites Navigation tab is installed via a normal subform container.
Must reference the subform container control name: =[Assets_Trans Subform]![Asset_ID]

Since the textbox is on the Sites Navigation tab at same level as the subform container, full Navigation form path is not needed.

I use a schema like ctrAssets to name subform container controls.

[WCStarks]

Oops! Sorry for the unintended miss-direction in my previous instructions. I Entered the expression as indicated and it works wonderfully. Thank you so very much.

[WCStarks]

Again, thanks for your help.
I have two questions related to this discussion.
1) What did I say to miss-lead you into a solution involving a full Navigation path.
2) How did you come up with Assets_Trans Subform, as the syntax for the name of the subform container control? I have tried to find it, but I guess I do not know where to look. As far as I know, the container name is Assets_Trans_Sites. The only Assets_Trans I know of, is the Table with that name.

[WCStarks]

Never mind my 2nd question in my post this morning, about the container control name. I just found it. One must get the boarder of the container highlighted, in order to see the container name. Now, I understand what you mean by the Container control. To me, I had just thought it was the form itself. I had no idea they were two different objects, which is why I was confused when you talked about the container control name.

[June7]

As for 1), my misunderstanding of where the textbox was positioned, not necessarily due to anything you said.

[WCStarks]

While this turned into a protracted conversation, in the end, it was an import learning conversation for me. I learned a lot from it, that will be very helpful in the future. So, thanks for your patience.