Unable to link Subform to another subform

I have a form with two subforms. The first subform is linked to the master form with the SKU fields and works perfectly. I want the second subform to link ManufPartNo which is a field on the first subform. I created a textbox on the main form named MPn and set it's control source as follows:


When I try to link the second subform, the field MPn is not on the Master Fields list.

This has always worked for me when I was using Access 2000, now I'm using Access 2010. What am I doing wrong?
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPConnect With a Mentor Commented:

Thanks but I tried that, I get a dialogue saying "The text you entered isn't an item in the list." It  then drops down the field list and will not accept anything other that what's in the list.

There is not a drop down field list for the master linking property of a sub form control.  You can type anything you want into the property and Access will not through up any error.  

What property are you trying to set?

If the Control name on the parent for is MPn with a control source of


then try this:
Master Linking

The FMS article states:

A Subform Cannot Link to Fields in a Subform of its Parent

In a regular main to subform design, the Link Master Fields property would be based on a control on the main form. In the two subforms scenario, the second subform is filtered on a value in the first subform. Unfortunately, the subform's Link Master Fields property cannot directly reference a field in the first subform. It can only reference a field in its parent form.

This is not actual true. I do what they say is not possible  regularly.

A subform's Link Master Fields property CAN  directly reference a field in the first subform.  I have been doing what they what they say is not possible for 10+ years. The syntax is tricky to get correct but it does work.  I have an Access 97 app that does  what they say is not possible. It  has been converted to a Access 2002/2003 format and works in 2000/2002/2003. . As an MDE it works in Access 2007 and 2010.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Try manually entering (I copy and paste) the control name in the master linking field property.

 I have always manually entered the value in the property.  I know that works in all versions
EclecticBobAuthor Commented:
Thanks but I tried that, I get a dialogue saying "The text you entered isn't an item in the list." It  then drops down the field list and will not accept anything other that what's in the list.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Are the datatypes the same for the linking fields? (they must be)
Is one of the fields a Primary Key? (it may need to be)
Do you have non-standard names for any of the linking fields? (Use only alpha-numreic characters with no spaces in the names)
Can you join the tables directly in a queryand get the proper result set?? (if not, then they probaly wont join in a form either)

Also note that you can build multilevel (Nested) subforms with the form wizard, ...give that a try.

Also know that you can simply drag and drop a form into another form.
If the linking fields are related in the relationships window, and one of them is a PK (Primary Key), then Access will typically join them for you.

In any event, always try to make sure your master linking field is a PK and that your tables are properly related in the database window.
(may not be optimal for all cases, but it will work)

See this sample, note that the tables are all properly keyed and related.
Now open the Pets form in design view, and drag the Toys form into it.
Note that the key fields are linked automatically and the correct related data is displayed when the form is opened.

Now open the Persons form and drag the Pets form into it.
Again, note that the prpoer linking is done automatically

Finally if this is something "odd", then there is always the old standby of running the compact/repair utility, then creating a new blank database and importing all the objects, ...then try linking the forms again.

Barring that, you may have to post a sample of this database that exhibits this issue.

EclecticBobAuthor Commented:
Thanks Jeff, I understand all this but what I want to do is link a subform to a field on another subform. I have done this before in Access 2000 using the method described here:


Scroll down the page and read the section titled:
A Subform Cannot Link to Fields in a Subform of its Parent

Now that I'm using Access 2010 I cannot get the solution as described in the article to work.
Jeffrey CoachmanMIS LiasonCommented:
OK, now I understand...

So then you can filter the second subform's recordsource by the first subform link value

Then requery the second subform on the first subform's current event.

There may be another way, but this works fine for me in Access 2010

hnasrConnect With a Mentor Commented:
Try: Quick reply without checking  boag2000's comment, which might be what you want except "filtering".  I am using Master/Child link fields.

Form amain > record source  table a(aid, f1)
Form amain_sub1> record source table b(aid, bid, f2)
Form amain_sub2> table c(cid, f2, f3)

Open amain in design view
Drag amain_sub1 onto amain > master link fields: aid >child link fields aid.
Drag amain_sub2 onto amain > master link fields: amain_sub1!f2 >child link fields f2

Note: Normally in such case main_sub2 is a subform of main_sub1, but prevented from doing so if sub1 is in a datasheet view
In this case, you need to put in amain_sub1 current event the following:

Private Sub Form_Current()
End Sub

Open in new window

EclecticBobAuthor Commented:
It would not allow me to enter anything in the Link Fields that was not on the drop-down list.

However, all your answers put my brain to work and I found another solution. I'm not a professional, this database is used to run my own business and everything is self taught so this may not be the best solution.

In the main form I created a text box  named MPn with the following code:


I then created the following sub on the main form:

Private Sub Form_Current()
 Me![RB_INVENTORY].Form.Filter = "[CatalogNo] = '" & Me![MPn] & "'"
 Me![RB_INVENTORY].Form.FilterOn = True
End Sub

So, I'm filtering the second subform from the main form based on a field in the first subform.
It works perfectly.

Thanks all for your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.