Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TWO TABLES AS SUBFORMS IN ACCESS...HOW DO I LINK THEM?

Posted on 2012-03-22
4
Medium Priority
?
515 Views
Last Modified: 2012-03-24
I have a Microsft Access Solution I am repairing.

I have three tables.

TBL1 is parent to TBL2, and TBL2 is parent to TBL3.

I also have FORM1, which represents TABLE 1 and has the necessary fields.

inside FORM1 is SUBREPORT1 which displays TABLE 2, in a table format for data entry.  Table 2 is linked to Table one via FORM1's Link Master/Child Fields.

I want to integrate Table three.  I want to make a subreport2 which will hold Table three, and have a relationship set (master/child) between Table 2 and Table 3.

Can I do this?  If so, how?

in short, Form 1 represents Table 1.  Subreport 1 represents Table 2 and is Child/Master Locked to Table 1.  I want Subreport 2 added representing Table 3 and lock it Child/Master to Subreport 1.  Both Subreports are on the same Form.

Thanks
0
Comment
Question by:Evan Cutler
4 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37755761
Just note that your question here is: "TWO TABLES AS SUBFORMS "

...yet in your posts, you are referring to "subReports"



In any event, I will presume you are using forms.

A three level nested form structure requires a bit of VBA to have it work just right.
The form wizard will create this type of form for you.
Open the form wizard
Select the fields you want from the Parent table
Select the fields you want from the first Child table
Select the fields you want from the second Child table

Then you should be able to click through the rest of the wizard's steps...


;-)

JeffCoachman
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 37756743
Little trick to this; a master link can be a field or a control.  So here's the setup:

1. Main form and first subform, set master/child links as normal
2. On the main form, create a hiddent control.  Call it something like txtSubform1Key.
3. In the on current event of the first subform, place the key you want to use for subform 2 in the control:

Me.Parent.txtSubform1Key = <some value>

4. Set the master link for subform 2 to  txtSubform1Key and the child link as normal.

That's it!

Jim.
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1000 total points
ID: 37758631
Example:
3 tables:
a(aid, adesc)
b(aid, bid, bdes)
c(bid, cid, desc)

1 Main form EE record source a, and display both fields.

2 Create first subform control on form EE, if wizard starts, click cancel. A blank control appears.
   Select Control and choose properties window
   Data Tab
   Record Source: Table.a
   Master Link field: aid
   Child Link field: aid
3 repeat step 2
   Record Source: Table.b
   Master Link field: Child1.bid
   Child Link Field: bid

Check this sample database.
Run EE and watch.
Two-tables-as-subforms.accdb
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 37760964
Thanks Guys.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

886 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