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

[WCStarks]

I have a Main form called Assets (Navigation sub-form). It has two sub-forms. The sub-form Asset_Trans (displayed as Asset Tracking on the form), contains a Site field, a combo-box which contains the name of a Site where the asset is located.



I need to filter the Assets in the main form, based on the Site control in the sub-form (covered up, in the image below, by the error message). I created a combo-box in the main form that allows selecting a Site from the Sites Table. Then I created an Event Procedure to filter the Assets by the selected Site.

I have been working with this code, but can't quite get it to work. The code is returning the target Site text, as can be seen in the error message in the image below. It needs, quotes around the value returned by the combo-box, but I haven't been able to figure out how to get them in. I suppose there is an obvious solution, but it escapes me. Anything I try, to put quotes around the result, just makes it worse.

After I get this working, I'll likely change out the filter combo-box with an Option Group, so I can cancel the filter as well.

Private Sub Filter_Site_Click()
    Me.Filter = "Me![Asset_Trans]!Form![Site] = " & [Filter_Site].Column(1)
    DoCmd.RunCommand acCmdApplyFilterSort
End Sub

[pbaldy]

The value is text, so you need delimiters around the value.

[John_G]

Filtering a main form using a field in a sub-form is not usually good practice; the records displayed in the sub-form should (usually) be only those which are related to the current record on the main form. If you set the form - subform properties correctly, this is automatic - you don't need code.

Looking for the moment only at your main form Assets and the sub-form Asset_Trans, what is the data the sub-form is meant to show? Since you stated that it is for Asset Tracking, that implies (to me) that it (and its underlying table) tracks the movement of Assets from site to site. Is that correct?

If I have that right, then the field linking the Asset form and the Asset_Trans subform should be the Asset_ID (Barcode in your case?); this field should also be in both tables.

It would help if you could post the diagram of your tables and their relationships.

[June7]

So in your other thread https://www.accessforums.net/showthread.php?t=72992 you got a solution to go to record. Now you want apply filter? Is Site a text field?

Private Sub Filter_Site_Click()
    Me.Filter = "[Site] = '" & [Filter_Site].Column(1) & "'"
    Me.FilterOn = True
End Sub

[WCStarks]

The Sub-form as the following Link Fields:
Link Master Fields: ID (Asset Table)
Link Child Fields: Asset_ID (Asset_Trans Table)

The Asset table is associated with the Site Table only via the Asset_Trans Table. So, I am thinking that I need to filter on the Site field in the sub-form to restrict the list of assets to just those at that site as selected for the filter, as indicated by the associated Asset_Trans records. Actually, controlling the main form from the sub-form seems a little confusing.

The Asset_Trans table tracks changes is status of the asset, which includes it functional status, assignment to a Custodian and its site location, all by date.

The BarCode field is only the barcode number from the Asset Inventory tag affixed to the asset.

Is that clarification enough?

[John_G]

If what you want is to see all the assets that are on a selected site (which makes sense), you might have to re-think your data structure, but I can't be sure. Your current form/subform definitely won't do that.

The data in the Asset_Trans table might allow you to get what you need, but it depends on what you have. If by using the data in that table you can determine the current location (site) of an asset, then you can get what you want using another form/subform arrangement, but this time with the site in the main form.

Can you post the structure of the two tables?

[WCStarks]

Yes. The Site field value is the name of the site. The Site field in the Asset_Trans table is a Combo-box, which allow selection of a site to associate with the Asset. So yes, it is text. I was unable to work out how to get the quotes to show up around the [Filter_Site] return value. I see you have done the quotes how I could not imagine to do it. I see you are using Me., when the Site control is in the sub-form, but the filter control is in the Main, Assets, form. So, don't I need to reference the sub-form explicitly from the main form, as I did above?

[WCStarks]

Here are the structures of the two tables. Of course, all the relationships are established in the Relationships screen.
The Asset_Trans is a many-to-one transaction table, which shows a potential historical changes in the status of an asset. Since an asset can be associated with multiple sites over time, the link to the Site cannot be in the Assets table. That relationship is handled by the Asset_Trans table.

I have described each of the fields which may need some explanation. I see I neglected to describe the Form field, which is also a combo-box to select the form of the asset.

I am thinking that if this filtering doesn't work, I might try it from the context of the Site. But, I cannot currently see all the details of the Assets from the Sites form. I can only see the Assets_Trans brief info. I would need to replicate the details of the Asset in a second sub-form in that context. However, I was hoping I could just filter on the Main Asset form, instead. So I was only editing the details in one place.

I will have to wait until morning to continue this conversation.

[June7]

Sorry, I rushed my response. Assuming code is behind main form, must reference subform through its container control. I always name container different from its form, like ctrAssets.

Private Sub Filter_Site_Click()
    Me.FilterOn = False
    Me.Filter = "[Site] = '" & Me.ctrAssets.Form.Filter_Site.Column(1) & "'"
    Me.FilterOn = True
End Sub

[WCStarks]

I tried the code as you suggested, but it doesn't work. It doesn't know what the Site control is.
The Site control is in the sub-form (Assets_Trans),
The Filter_Site control is on the Main form, as is the Event Procedure for its On Click property.
So, to reference the Site control in the sub-form, from the main form, according to http://access.mvps.org/access/forms/frm0031.htm, wouldn't need to do something like:

Me.Filter = "Me![Assets_Trans].Form.[Site] = '" & Me.[Filter_Site].Column(1) & "'"

This doesn't actually work, by the way, as Access doesn't know what Me![Assets_Trans].Form.[Site] is. It asks me to enter a parameter for it.
I don't know what you mean by the "its Container control" to reference the sub_form

[June7]

Just re-read post title: "How to filter a main form from a value in a subform based on a combo control in the main form". This makes no sense. If you want to filter the main form with value from combobox on the main form, then subform is not involved in building filter criteria.

If you want to filter the main form then why are you referencing Site field of subform - must reference Site field of main form, assuming main form has such a field.

A subform or subreport is created by placing a Subform/Subreport Container Control on a form/report. That control has SourceObject property which references a form or report. I always name container control different from the form or report it holds.

So if you want the parameter to come from subfirm field, the correct syntax is:

Me.Filter = "Site='" & Me.ctrAsset!Site & "'"

In which case the combobox is not involved. If you want parameter to come from the combobox which is on main form, correct syntax is:

Me.Filter = "Site='" & Me.Filter_Site.Column(1) & "'"

Regardless of source of parameter, to filter main form the filter criteria must reference field of main form.

[WCStarks]

Thank you for your detailed response. So, what you are telling me is that what I originally wanted to do is not feasible. This morning, having decided that was probably the case, I came up with an alternative approach. If you look at my first image attachment, you will see that the database has another Navigation tab form for "Sites".

It has the main form called Sites, which, originally had one sub-form, displayed as Assets. It is named Assets_Trans_Sites, which source is the same Assets_Trans table mentioned under the original posting, augmented with some information from the associated Assets table.

This morning, I added another sub-form to the Sites main form, below the continuous, "Assets" form which lists all the assets at each site. This new form is a single form, which should show all the details of an Asset.

What I need to do then, is have the new "Asset" form update as I browse from one record to the next in the continuous "Assets" form. Then I can view and edit the details of the Asset. The actual form names are different that the displayed names of the forms, but to keep it simple, we can just refer to their display names. I can adjust the names later.

Context:
Manage_SCATeam form is the top form which holds a number of Navigation forms, one of which is Sites.
The Sites Navigation form now has two sibling, child forms:
Assets, which is a continuous form
Asset, which is a single form

When the forms first open, the "Asset" form should show the Asset associated with the first row in the continuous form, then, as I browse up and down in the continuous form, perhaps by using the arrow keys, the "Asset" form will update to show all the details of the actual Asset which I can view and edit. As you can see, currently the "Asset" form shows a completely unrelated asset.

Perhaps the On Current property of the continuous form will do the trick. My problem is learning to deal with these form references. In this case, we are dealing with references between two sibling sub-forms. I hope this is not too confusing. I appreciate your patience is trying to help me, June7.

[June7]

To synchronize sibling subforms refer to http://www.fmsinc.com/microsoftacces...edsubforms.asp

You need to be aware nature of Navigation form means only one of the tabbed forms is active and available at a time. This is why one tabbed form cannot directly refer to another tabbed form. This must be considered when managing the sibling non-tab subform.

[WCStarks]

Below, is the image of the Query Builder for the Continuous form, Assets_Trans_Sites, which shows the relationships between the three tables.
In the image I sent you earlier that showed the 3 forms in form view, shows these three forms:
The Sites single Main form, sourced on the Sites table.
The Assets_Trans_Sites continuous Main sub-form has these three tables as sources, but is based on the Assets_Trans table.
The Site_Asset single sub-form is the bottom form based on the Assets table.

I did as suggested in the reference you sent me:
I created a text box in the Sites main form called TxtID.
I made its control source as: =[Assets_Trans_Sites].[Form]![Asset_ID]
I set the Site_Asset sub-form Master Field property to TxtID and its Child Field property to ID, which it had defaulted to.

But when I go to Form View, the new text box shows #Name? What have I done wrong?

[June7]

I lost sight of fact you are using Navigation form with my response in post 11.

Expression in textbox will have to reference NavigationSubform, assuming you have not renamed it from that default name:

=[NavigationSubform].Form.Asset_ID