Filtering on sub form wont work...

[hardy1976]

Hi,

I have a main form which I have filtered using...

With Me.SearchCompanyCode
Filter = strFilter
FilterOn = True
End With

The next step is I want to filter the sub form.. however its just not having it no matter what I try.. any ideas?

strfilter2 = "Forms!company![Company (OrderSub)].Form!Orderid = " & b
Me.[Company (OrderSub)].Form.Filter = strfilter2
Me.[Company (OrderSub)].Form.FilterOn = True

 

[NickHa]

Try referring to the subform through its containing control. If your subform is in a control named sbfChild, use

Me.sbfChild.Form.Filter = strfilter2
Me.sbfChild.Form.FilterOn = True

This code would be in the parent wherever you need it.

 

[hardy1976]

Still no joy, brings up an empty record on the subform?!

 

[NickHa]

How is your subform related to the parent? It sounds as if your filter is contradicting that relationship.
I did wonder why you wanted to filter the subform, because the linkage forms an automatic filter in effect. Any additional filter you apply would be on the subset of records which are related to the parent record through the link.

 

[hardy1976]

Company table = CompanyID, name etc
Orders table = OrderID, CoID, orders etc...

Company to CoID (one to many)

For example I want to find details related to order 123.

The form has a main form detailing company information and a sub form with the related orders. I can find the company and orderid information using a dlookup, however cannot filter on that information. Does that make sense?

The form loads with a company, so I dont think I can go straight to the sub form and filter for the order. hmmmm do I have to change the recordsouce for the sub form.. guess that will work, however is a fudge in essence...

any ideas?

 

[NickHa]

In your subform control, what do you have as the Link Master Fields and Link Child Fields values? From your description, I would expect these to be CompanyID for the Master and CoID for the Child.
When you want to find details for order 123 for company 1, I assume you start with that company on the parent as its current record? That being so, then the subform will have all records for all orders for that company as its base record set? You therefore need to set the filter to pick out order 123 from that set.
I conclude that your filter should simply be "OrderID=123" for the subform, if my assumptions are correct. This means that the filter string should be set up as

strfilter2 = "OrderID=" & b

with the filter code as before.

 

[hardy1976]

In your subform control, what do you have as the Link Master Fields and Link Child Fields values? From your description, I would expect these to be CompanyID for the Master and CoID for the Child.

Correct.

When you want to find details for order 123 for company 1, I assume you start with that company on the parent as its current record?

No... As an example... I want to search for order123, however I do not know what company it is for.

 

[NickHa]

It seems to me that you are starting in the wrong place, then?
If your main form is concerned with companies and you are searching for orders irrespective of company, then it sounds like you should be using a form based on orders which then shows the company to which the order belongs.
If your application requires the structure as you have it, then you would have to determine the company to which the order number belongs first, then go to the relevant company record in your main form.
In this situation, I would put a combo box on the main form, bound to the orders table (i.e. independent of the company table). I would choose the order number and look-up the company to which it belongs, then move the company form's current record to that company. From there, it's as discussed previously.
This may be what your control SearchCompanyCode is for, in which case, you probably have what you need now?
Does this describe what you need, or am I still missing something?

 

[hardy1976]

I gave up with filter and have just used record source instead - cheat I know, however spent way to much time on a simple filter.

Thanks
H.