Filter by subform??HELP 1

[Ryon]

Hello,
Please tell me this is possible. I have a mainform named "FRMCLIENTS", With a subform called "FRMLABS". The subform is a Tabular form with multiple entries per client.
The subform has a field "LabNum" which is link with one to many relationship with the client "KEY"(one side).

What I'm trying to do is Filter the mainform base on the "LABNUM" on the subform. EX: Give me only the clients who are using lab # 701.

Thanks ahead of time,
Ryan

 

[TonyJollans]

Hi Ryan,

I don't completely understand this. If you have a one-to-many relationship between LAB and CLIENT how does your subform have many records?

Anyway, you can't filter a mainform based on a value in a subform - it works the other way round, the subform contains data relating to a single mainform record. What I guess you want is all the other clients who use a particular lab which your 'main' client is using. Would it work to have a subform of the subform?

Enjoy,
Tony

 

[Ryon]

The CLIENTS are the "one" and the LABS are the "many".

What I need to do is filter all of the clients that use a certain Lab.
I may have come up with a workaround but I am not that experienced to make it happen.

My subform cosists of mutiple lab numbers like so

Lab Name AccountNum
301 Lab1 1111111
293 Lab2 2939848
206 Lab3 2893482

What if i could compile all of the lab numbers onto a textbox on the main form. like so

LB: 301,293,206 or
LB: "301" or "293" or "206"

I can create a field in my table and make this a bound
field andf this box on the main form to function correctly (I think), Is it possible to filter by that: Give me all the clients that have "301" in LB?

I know with all the programmers on this site and there combined knowledge, that this is very possible.

Please help,
Ryan

 

[TonyJollans]

Ryan,

I’m still confused, but now you’re asking a different question.

A simple one-to-many (Client to Lab) relationship would give, simplistically, a table structure like ..

Client Table:

ClientID as Number
ClientName as Text

Lab Table:

LabNum as Number
LabName as Text
LabAccountNum as Number
LabClientID as Number

Your main form would have a Source of ClientTable, one text box for ClientID and one text box for ClientName.

Your Subform would have a source of LabTable, and your tabular view of the lab data. The Link Master field would be ClientID and the Link Child field would be LabClientID.

In this scenario, each lab has one client so there isn’t a question of finding all the clients who use one lab.

Now if you want a text box on the main form which summarises the subform take a look at Thread705-518996 where I answered a similar question the other day.

Enjoy,
Tony

 

[Ryon]

Tony,

Here is how I have my table structure (and probably where I messed up when I explained it)
Client Table:

ClientID as Number
ClientName as Text

Lab Table:

LabNum as Number
LabName as Text
LabAccountNum as Number
LabClientID as Number

"LBKEY" as autonumber

And the lbkey is actually the "many" side

Sorry for the confusion and the help

Now, After all that, is there a way to do this? Linking the lab numbers to a text box and then filter?

Ryan

 

[TonyJollans]

Ryan,

Is the link between your mainform and your subform ClientID (Master) to LBKEY (Child)?

Anyway, your subform is subordinate to an individual record on your main form. You cannot filter the main form based on anything in the subform. You have a couple of options:

1. You can add a subform to your subform so you would see something like ..

Client 1
Lab 1
Client 11
Client 12
Lab 2
Client 13
Client 14
Lab 3
Client 15
Client 2
Lab 2
Client 11
Client 12
Lab 4
etc

or

2. You could use a variation of the code in Thread705-518996 to create a textbox on your mainform based on the same query that your current subform is, but you cannot then feed this back into your mainform selection (you would, in effect, have a circular reference).

Hope this helps,
Tony

 

[Ryon]

Thanks Tony,

Aparently what I am trying to do is not possible to get the results I am looking for. This used to be a function on the main form before the CLIENTS were able to use multiple labs, I had to split the table and create the relationship.

1. Your first suggestion would not allow me to view the sub-subform client info on the main form for editing

2. Your second one would give me the textbox, but if I understand correctly, I could not filter from that.

I will keep think about some other options, maybe I could come off of the MAIN LAB form and open the MAIN CLIENT form.....Ahh here's an Idea

What if used your second Idea, But went about it from the LAB MAINFORM, Have a textbox that contains all of the client "KEY" that use that Lab. From that point open the mainform, with the filter parameters pointing to that textbox..

Thanks Tony for letting me poke your brain so to speak, you have been very helpful. I'm going to try it and if it doesn't work you can bet I'll be back


Ryan