Solved

Unable to link Subform to another subform

Posted on 2014-01-17
8
1,634 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

18 Experts available now in Live!

Get 1:1 Help Now