Solved

Unable to link Subform to another subform

Posted on 2014-01-17
8
1,674 Views
Last Modified: 2014-01-20
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:

 =[frmInventoryDetailsSfrm].[Form]![ManufPartNo]

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?
0
Comment
Question by:EclecticBob
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 21
ID: 39790068
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
0
 

Author Comment

by:EclecticBob
ID: 39790074
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.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 39790227
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.

JeffCoachman
Database52.mdb
0
 

Author Comment

by:EclecticBob
ID: 39790248
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:

http://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp

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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39790380
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
;-)

JeffCoachman
Access--eeq-28341825--Link-sync-.accdb
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 166 total points
ID: 39790520
Try: Quick reply without checking  boag2000's comment, which might be what you want except "filtering".  I am using Master/Child link fields.

Assumptions:
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()
    Me.Parent.amain_sub2.Form.Requery
End Sub

Open in new window

0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 167 total points
ID: 39791448
@EclecticBob,

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

=[frmInventoryDetailsSfrm].[Form]![ManufPartNo]

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.
0
 

Author Comment

by:EclecticBob
ID: 39794921
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:

 =[frmInventoryDetailsSfrm].[Form]![ManufPartNo]

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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now