Solved

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

Posted on 2012-03-22
4
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

632 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