Solved

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

Posted on 2012-03-22
4
488 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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 30

Accepted Solution

by:
hnasr earned 250 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

679 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