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

[WCStarks]

Hi June7,
I made that change, but the textbox still shows the #Name? Just to confirm, the textbox is in the Site Navigation sub-form. I copied the code from my textbox to here, which matches what you gave me:
=[NavigationSubform].[Form].[Asset_ID]

Because I didn't understand how the NavigationSubform, in this statement, would know it was referencing the Assets_Trans_Sites sub-form, I even tried:
=[NavigationSubform].[Assets_Trans_Sites].[Form].[Asset_ID]
and that didn't work either.

Any other ideas? I am at a loss. Is there a reference about how to use the Navigation sub-form references? I have a nice reference for the standard form references. I am pretty well committed to continuing with these Navigation forms, by now. Had I known, what I know now, I would likely have gone the other route.

[June7]

I don't know of any guide for how to reference Navigation form. I learned by trial and error and viewing examples.

Which tab is active when this code runs? If it isn't Site then code will not work. As I said, only one tab form is active and available to reference at a time. This is limitation of Navigation form. I have never used.

The only real advantage for using is that since only one tab form is loaded at a time, the form loads faster when first opened. When you change tab, subforms are unloaded and loaded.

[John_G]

Because Asset_Trans_Sites is a subform, it is not a part of the current forms collection, so you cannot refer to it by name in that context. You have to use the name of the main form control that contains the subform, like this:

=[Sites].[container control name].[Form]![Asset_ID]

Replace container control name with the actual name of the container control on your main form.

[WCStarks]

Yes. The Sites tab is the one that provides this interface. So, in your test, you replicated an extra level Main form, like my [Manage SCATeam] form which contains the Navigation forms?

The tree is as follows:
[Manage SCATeam] main form
      Sites Navigation single form
                    Assets_Trans_Sites (Master) continuous sub-form
                    Site (Child) single sub-form

[WCStarks]

I still do not know what "Container Control" is.

[John_G]

I have some questions regarding the Asset_Trans table:

Why do you have Asset_ID and Asset, both as numbers,
and
Why do you have Site_ID and Site, both as numbers,
and
Why do you have Member_ID and Custodian, both as numbers?

Any one transaction will refer to only one asset, one site and one Custodian, so they should only occur once.

What data field(s) in the table do you use or can you use to determine which site an asset is currently at? Any one asset can have many records referring to it in the Asset_Trans table, so how do you determine which one is the latest one? Looking at your screenshot in post #12 - how can you be sure that the list of assets shows only the ones that are currently on that site, and not ones that were there at some time in the past but are now elsewhere?

[John_G]

I still do not know what "Container Control" is.

When designing the main form, you added a subform to it. You did that by selecting subform/subreport from the group of available controls. I (among others) sometimes refer to a control like that as a container control, because it "contains" another form or report. You will see in the properties list for it that MS Access calls it a "subform/subreport", and it has a control name like all the others do.

Sorry for the confusion - it just the terminology I sometimes use.

[WCStarks]

1) The Assets_Trans table provides an historical cross-reference between Assets and the two other tables (Members) and (Sites).
2) Asset_ID is the linking field to an Asset in the Assets table
3) Site_ID is the linking field to a Site. It has a value when the asset is assigned to a site.
4) Site is a combo-box which allows me to select a site to associate with this Assets_Trans record
5) Custodian is a combo-box which allows me to select a Member to be assigned as the Custodian of this asset.
6) All Assets_Trans records also include a status (Trans_Type) a combo-box to indicate what the status is for the Asset associated with this Assets_Trans record. They also include a date of the status change.
7) The Assets_Trans records are ordered by date DESC, so the most current status, custodian, and Site assignments will float to the top. So going to the first record will always retrieve the current Assets_Trans record for that Asset.
8) The Asset_Trans doesn't just show one record per asset. It shows a history of transactions for that Asset.
9) On the next status/assignment change, all three values are updated, even if any one of them did not change. Perhaps that may be slightly redundant, but it seemed acceptable to allow using just one transaction table.

10) When I select a Custodian or a Site with the respective combo-boxes, I have an On Click event for each, which stores the record ID of the selected records into their respective Member_ID and Site_ID fields.

11) "Asset" is probably a confusing term for that field. I have evolved to labeling it as BarCode on most of the forms. It is actually the BarCode number of the Asset Tag affixed to the Asset.
12) That barcode number is part of the Asset record and is brought into the Assets_Trans record for display. I did that early on. I really didn't need to do that, since the forms and reports can get the value from the Asset record when needed. I will be getting rid of it. The Barcode value is used as a logical ID for the asset. As yet, not all assets have been assigned an Asset Tag. I need to get this feature working to be able to conveniently browse the Assets (Asset_Trans table) for a site, and have the Asset details displayed in the form below, to view and edit, before I get after assigning more tags.
13) All Assets_Trans records are assigned in a sub-form on the Assets Navigation form.
14) I have a Tab page form under Members, which shows all the assets (via the Assets_Trans table augmented with details from the Assets records in forms and reports), he is Custodian of, just as under the Sites Navigation form, I can see all the assets assigned (deployed) at a site, via the Assets_Trans.

I hope this helps.

[WCStarks]

If I don't have the wizard create a parent and child form, I usually drag and drop sub-forms/sub-reports onto a "container" form/report. However, I am finding that letting the wizard do it, makes the forms and reports more difficult to modify later. So, I have decided, in the future, I will assign the sub-forms/sub-reports myself, without using the wizard. It seems like I have more control over them when I do them myself.

[June7]

The subform/subreport container control is what you used to install the non-tab sibling subform. Whether by selecting control from design tab and manually setting SourceObject or by drag and drop form or report, a container control results.

The Navigation form wizard creates a main form and installs the tabs with a subform named NavigationSubform. The tabs and subform are in a grouped layout and there is hidden code that manages display of forms when tabs are clicked as determined by the NavigationTargetName property of the tab.

I seldom use any wizards.

[WCStarks]

I originally created a normal form called [Manage SCATeam] and then, in it, I created the Navigation form, with its various sub-forms, making it a container form for the Navigation Form. Did your test take into account this full context? Could this be why my event procedure does not work? If I understood your last post correctly, below represents the current context of this issue.

 [Manage SCATeam] main form
    Navigation Main Form
       Sites Main Navigation Sub-form (single)
            Assets_Trans_Site (Master) continuous sub-form
            Site (Child) single sub-form

[June7]

Doesn't matter. Only one tab form on Navigation form is loaded at a time.

[WCStarks]

I am still trying to figure out why your test works, and my use of the same expression does not. So, when we say:
=[NavigationSubform].[Form].[Asset_ID],
Access knows that [NavigationSubform] is referring to the [Sites] Navigation sub-form, since only one is open at a time. However, there are two sub-forms ([Assets_Trans_Sites] and [Site]) contained within it. In the above expression, how does Access know which of the two sub-forms we want to get the [Asset_ID] from? As it happens, in this instance, [Asset_ID] is only found in the [Asset_Trans_Sites] sub-form.

Perhaps, I should confirm that the textbox, [TxtID], that the above expression is in, is on the [Sites] Navigation sub-form.

[June7]

Mine works because I reference a control or field on the form that is loaded when the code runs. I didn't test referencing normal subform. To reference a normal subform on Navigation form, reference subform container control. I always name container different from the object it holds.

=NavigationSubform.Form.ctrSite!SiteID

To reference a control such as textbox (again, I name control different from field).

=NavigationSubform.Form.ctrSite.Form.tbxSiteID

[WCStarks]

I tried this again, but to no avail. Attached is the Expression builder showing the expression and the related forms in the Expression Elements window. I don't know what to do now.