master and child field link problem

S

scubadiver

I have three tables

Customer-> 1:n -> Branch -> 1:n -> Query

I am trying to link the query subform to the branch subform but it won't let
me choose the primary key for "branch", only "customer".

What is happening?
 
R

Rick Brandt

scubadiver said:
I have three tables

Customer-> 1:n -> Branch -> 1:n -> Query

I am trying to link the query subform to the branch subform but it won't let
me choose the primary key for "branch", only "customer".

What is happening?
Click to expand...

Did you place the Query subform as a subform inside of the Branch subform or do
you have them both on the Customer form? If the latter then you cannot use the
builder for entering the linking fields, but will have to enter them manually.
The MasterLink would look something like...

Forms!Customers!BranchSubformControl.Form!FieldName

It is important to note that the third reference above is the name of the
subform control which might not be the same as the name of the form within it.
 
S

scubadiver

Cheers.

They are in two separate subforms.

The "BranchID" field is what relates each branch to all its respective
queries.

I am assuming the reference you have given goes in the queries subform and
the master field refers back to the branch form? This is what I have typed:

Link Child Fields: BranchID
Link Master Fields: [Forms]![queries form]![branch form].Form![branchID]

But isn't quite right. I get the following: "The expression you entered
refers to an object that is closed or doesn't exist".

For Customer 1 / Branch 1, I have three logged queries, numbered 1-3. If I
move to Customer 1 / Branch 2, I can still cycle through the 3 queries. If I
move to a different customer number, the queries disappear.
 
R

Rick Brandt

scubadiver said:
Cheers.

They are in two separate subforms.

The "BranchID" field is what relates each branch to all its respective
queries.

I am assuming the reference you have given goes in the queries
subform and the master field refers back to the branch form? This is
what I have typed:

Link Child Fields: BranchID
Link Master Fields: [Forms]![queries form]![branch
form].Form![branchID]

But isn't quite right. I get the following: "The expression you
entered refers to an object that is closed or doesn't exist".

For Customer 1 / Branch 1, I have three logged queries, numbered 1-3.
If I move to Customer 1 / Branch 2, I can still cycle through the 3
queries. If I move to a different customer number, the queries
disappear.
Click to expand...

You might need to issue a Requery on the Querys subform whenever you
navigate in the Branch subform or in the Main form. Subforms aren't
supposed to need this, but I have seen occassions where it was necessary.

As an alternative you can put a hidden control on the main form that
references the control on the Branch Subform with a ControlSource of...

=[branch form].Form![branchID]

....and then just use the name of that TextBox as the MasterLink property of
the Querys subform control. Leave the TextBox visible during testing so you
can see that it is showing the correct value as you navigate. Once you get
it working you can hide it.
 
T

tbl

It turned out that re-doing the forms was a lot easier even though a bit
fiddly.

It has taught me another lesson!
Click to expand...


Did you find the source of the problem?

If so, please share it!
 
Top