selecting Sub-Form data from two inputs

[techexpressinc]

I posted this in the query area no replys...

I need my data to fill the sub-form come from one of two possible inputs based on plan-id. I am not clear on how to select the data to fill the sub-form.

There is the main form with data from DB-A, then there is a sub-form that needs filled with data from DB-B or DB-C, based on the key planid that is on DB-A.

How would get the sub-form's data to come from DB-B or DB-C. I could do it field by field; like this:

Inputs:
1) DB-A (main form DB)
2) DB-B (query brings in all fields with PLANid in a where parameter)
3) DB-C (query brings in all fields with PLANid in a where parameter)
Output:
1) Formusedfields: data going to the sub-form

I could see this working on the sub-form each screen field:
FormusedEmployer-Name:IIF(DB-A.MAIN[Planid]=qDB-B[PlanID],qDB-B[Employer-Name,qDB-C[Employer-Name])​

But i want a selection up front of DB-B or DB-C, not If-s per field.
Any good approach ideas??

Thx
Russ

 

[Rank Am]

Sounds like the underlying data relationships and tables structure has not been set up very well

If both tables are linked in one to many relationships with DB-A by PLANid then is there any reason that the data in DB-B and DB-C are not in one table? or linked by a one to one relationship where the non common data fields are kept in C.
All you would need to do is create a select query of DB-B and DB-C for the subforms recordsource, with a parameter set to the main forms PLANid field.

As is you could use a Union query as the basis for the subform SELECT X, Y, Z from DB-B UNION SELECT A, B, C from DB-C; but this wouldn't be updatable.
Another option would be to set up two forms with the subforms recordsource based upon queries DBA-B and DBA-C but then the users would need to know which to open up based upon PLANid they were going to select or a single form with some complicated filtering based upon a combo selection to change the subforms recordsource.
I would advise looking at the the data structure first.
BTW You can't use IIF statements in a controls controlsource property to tell it which table to select records from as the records available are controlled by the SQL statement in the subforms recordsource property.

 

[techexpressinc]

Re: selecting Sub-Form data from two inputs - resolved

resolved

cking for a null value and using the other table worked.

Rus