Multiple Tables in a Single Subform - PK Issues

[wtucker]

Hey Everyone,

I'm back, asking questions to problems I didn't know existed.

I have a main form with multiple tabbed subforms (generally the info from each subform is in a separate table). Each subform shares the same PK as the main form. The issue I'm running into now is that I need display duplicate values across two subforms.

So I have a Rates tab and a Billing tab. The Rates tab has all the dates, but there are some complex billing equations that need to be done against the rates, and I need to display the rates in the billing tab as well.When I use the Subform wizard and try to add fields from both tables, I get an error: "You have chosen fields from record sources that the wizard can't connect. You may have chosen fields from a table and from a query based on that table.If so, try choosing fields from only the table or only the query."

I'm assuming this has to do with the parent-child key relationship and that I already have one form with half of the info in this form.
I'm going to try to create a query with the info I need and then add it as a subform. Does anyone have any suggestions?

[RuralGuy]

Did you just say that each table in your subforms has the same PK as the PK of the main form? If so, that is a very unusual structure for your tables.

[davegri]

Each subform has a recordsource "generally ... from a separate table". So you can't bind a textbox to a field in a different table. You will have to do Dlookups to the pertinent table to get those textboxes populated.

[June7]

DLookup() is an option.

Is this a Navigation form or a Tab control on a normal form? If the latter, expressions (in VBA or textboxes) can reference controls of another subform. I do that. Name subform containers different from the objects they hold, for example ctr1, ctr2, ctr3. Following example is code behind ctr2 form that references ctr1:

.tbxSSCret1 = 100 - Nz(Forms!DataSoilsAgg.ctr1![tbxLabOneHalf], 100)

DataSoilsAgg is the main form these subforms sit on.

[John_G]

If you need to display the data from the Rates tab on the billing tab, put a textbox on the billing tab, and make the control source for it the name of the control on the Rates tab.

for example: if the textbox on the billing tab is to display the data from a control Rate_1 on the rates tab, put =[Rate_1] as its control source.

That won't work if you want to save the textbox on the Billing tab into a table, because you can't bind the control to a table field. You would need a little VBA to copy the data from the controls on the Rates tab to controls on the Billing tab.