Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do you connect two tables in a form so that they are in sync when the form is used

Posted on 2012-04-07
8
Medium Priority
?
389 Views
Last Modified: 2012-04-10
I have two tables a Header and Detail table I have the primary key in the header table and the primary key in the detail table.

My question is how do I connect them together so that they show up when a form is entered and records are entered in both the header and the detail section. I am a beginner so I have learned the table building but do not know nor have ever seen how to connect them so they show up in sync on a form page.

I know it has something to do with the background SQL and tying it together. I am clueless how that works. Please advise. Any examples or video is really helpful. Thank you.
0
Comment
Question by:ruavol2
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 400 total points
ID: 37819142
For this table structure you typically use a form consisting of a  main form containing a subform.

You create a main form based on the header table.

Then you create another form based on the detail table.  (In due course thiss will be placed o your main form as a subform- as below).

Then you add a subform control to your main form and the wizard guides you through the process, in which you select the form to be used (your detail form) and how the main form and subform link together.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 37819153
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 800 total points
ID: 37820458
Assume the tables:
A (a, r, x) - primary key (a)
a r
1 a1
2 a2

B(a, b, r) - primary key (a,b)
a b r
1 1 b11
1 2 b12
2 1 b21

When you create a form based on table A (bound to table A), you get the default text boxes a and r. Let's call it frmA
The same for a form bound on table B, you get the default text field names a, b,  and r. Let's call it frmB.

Open form frmA in design mode, and drag frmB on it in the detail section, this frmB is now encapsulated in a control (subform control) with the default name frmB. The wizard if it is activated, will ask for the master link fields, and child link fields. Select a for master, and a for child the link is established.

If the form fields are renamed, the link keys has to be changed to the new names.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:ruavol2
ID: 37821823
I do not know if you guys have any knowledge of this but when I use the instructions from above everything looks good right up until I try to enter the sub form. Then I get the following error message and I do not know how to get past it. Can you tell me what is blocking it. I am not seeing it. Please advise. Else I can post a new question if it is not related. Thank you.
AccessError.png
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37821979
it will help if you can upload the db you are working on.
0
 
LVL 31

Assisted Solution

by:hnasr
hnasr earned 800 total points
ID: 37822249
Find Msaccess.exe
Right click the icon  and select Run as administrator.
Open database. Check and comment.
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 37824707
ruavol2,

I am confused by your request.

First, can you please put real names on these very vague "Detail" and "Header" tables...

Customers/Orders?
Employees/Sales?
Orders/Parts?

Typically you already have all the "Parent" records and you are simply entering the "Child" records...
So I am not clear on what you mean by:
"and records are entered in both the header and the detail section."

Here is a very basic sample in addition to the info already posted

In this case the Main/subform setup that the previous experts mentioned seems to be what you need.
0
 

Author Closing Comment

by:ruavol2
ID: 37831204
I got it to work. Thank you for your help.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 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