selecting Sub-Form data from two inputs

[techexpressinc]

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

[ajetrumpet]

Hi Again Russ,

I remember helping you with a report issue a long time ago. Don't you live on a farm? At any rate, your post sports some technical lingo, so it's a little tough to interpret what you want. What do you mean by "up front of ....."? I don't understand that. In general, to fill data into a sub's fields, this syntax should be used:

me.subformcontrol.form.controls("controlname") = me.mainformcontrol

sometimes, for reasons known only to MS, subform fields will only take values if you set focus to the sub, and then the actual sub's control first. This is a bug, but rarely have I seen it happen.

It also sounds like you're throwing values into combos from the DB-A control's value that is entered? How do those boxes play into this?

[techexpressinc]

By "up front of.." i meant the process of selecting the source of the form database before filling in the form. So, that the form is not filled in field by field selecting the datasource.

No combo boxes to worry about on the sub-form, just a display of data from one of the two sources.

I think your direction you are suggestting is to you one sub-form for one source and and sub-form for the other source. And there would be logical to select what sub-form to use?

I live near farm, but not on one. I have bailed hay and picked up after cows in my youth.

Russ

[ajetrumpet]

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.

I'm confused by this Russ. Are there 3 databases or 3 tables to deal with here? And what does the plan-id's role in this?

And if the plan id is part of a one-to-many relationship, then the obvious choice would be to create a form and parent-child relationships based on the relationships of those tables.

The other thing I want to mention too, is that your post on AWF was 2 minutes from this one. That doesn't encourage an answer, at least from those that are aware of cross-posters. I don't technically answer cross-posters, even if I know the answer. Nothing doing or not a big deal, just mentioning it for future reference.

[techexpressinc]

There are 3 databases a 3 table on each to to deal with.

The plan-id's role is the common key on all three databases. And the key on the table of data I need pull in.

One type of plans are on one DB (DB-b), another type on another DB (DB-c).

Sometimes, I posted the same question on multiple forums trying to get an answer. If I do get an answer, I immediately go to all forums and post that the problem is resolved.

Thx for your help.

Russ

[ajetrumpet]

well if your data is located in another database, then you'll either have to use an ADO connection in code, or the opendatabase() method, because a remote file has to be opened in order to query it in anyway.

[techexpressinc]

I do not use either "an ADO connection in code, or the opendatabase() method". The Link Table Manager pulls in the data very fast and good with impacting the performance of the other DB.

My problem is I need the little bit of tricky code to
check my DB-A's plainid the DB the user is in. To check the DB-A's planid against DB-B' plainid then use the data to fill a sub-form. If there is not a match go to DB-C and use that data to fill in a sub-form.

The sub-form data is for display only, not to update.

RUss

[ajetrumpet]

you didn't mention you had a FE/BE set up here. Is that what it is? through the linked table mgr?

If you want to perform conditionals through 2 database on the back end Russ, I'm not sure if that is easy. I think Allen Browne has some code on his website to mess around with links from basic, but I would not be able to advise you on how to destroy them and relink them through the next db to check a second condition.

I also noticed that you just re-posted this on AWF, and I'm gathering that I'm not helping much here. Might be best if you went with someone else, because I really don't think I'm understanding the setup completely...

[techexpressinc]

The data links in fine, just like using data from table within the same DB.

I need to select the source of the data for the sub-form. Which you almost had me there: a main form with two possible subforms and select the subform based on the available data.

Russ

[ajetrumpet]

so the issue is swapping out the sources via the linked mgr, right? that's really what you need, no?

and what do you mean I almost had you?

[techexpressinc]

Yes swapping out the sources via the linked mgr. The sub-form's data needs to be from one source DB-b or another source DB-c. That is based on the plain-id in DB-a. When you put this statement in I was thinking of a possible way would be selection of a different sub-form. A sub-form for DB-b and a different sub-formfor DB-c. And somehow DB-a in loading the sub-form would pick the right one to load.
Code:
me.subformcontrol.form.controls("controlname") = me.mainformcontrol

[techexpressinc]

I think you are getting me close. I am thinking it would be possible for the query behind the subform to put the data in.

Attached is what I have done in another DB to show data that is unchange from DB-b.

Now I need to to show data from one database or another database.
So, maybe the query behind the subform could load the data.

I would rare do a one time selection of which DB to go against. Not a field by field load, within the query.

I know I could do a bunch of DLOOKUP-s but that is slow.

Russ

[ajetrumpet]

Attached is what I have done in another DB to show data that is unchange from DB-b.

so how did you do that russ? How did you display the data in the past? What you basically said is that you have displayed data on a sub that came from another db before. so how?

to my knowledge, you can only connect to one back end at a time.

[techexpressinc]

You can link to multiple backends. I linked to the other DB. Then had a query behind the sub-form, that had a key to the other DB and pulled in the fields to display on the sub-form from the other DB.
Now, i need a selection process to get the sub-form populated from one other DB or another DB. Data displayed on the form will be from 2 DB-s at all times, with a source of 3 DB-s.

[ajetrumpet]

ok, so if you have a query, one for each be option, then use a lookup or a direct reference to get the key, then query the record out of either of those queries based on it. that simple, isn't it?