Solved

how do i create a sub form or sub table in access 2007?

Posted on 2010-09-14
19
2,415 Views
Last Modified: 2013-11-28
how do i create a sub form or sub table in access 2007?
0
Comment
Question by:RalphS007
[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
  • 8
  • 7
  • 3
  • +1
19 Comments
 

Author Comment

by:RalphS007
ID: 33675904
that video tutorial is not working for me.
I open a form that i already have:
I click the sub form tool under the design ribbon in design view.
I position it in place.
It doesn't link propperly.
0
 

Author Comment

by:RalphS007
ID: 33675936
when i try linking it through relationships it says indeterminate relationship.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33675949
did you read the first sentence from the link?
<If you have established relationships in a database, you can create a form that contains a subdatasheet with just a few quick steps.>
0
 

Author Comment

by:RalphS007
ID: 33675987
i'm trying to make it into a 1 to many relationship.  How could i do that?
0
 

Author Comment

by:RalphS007
ID: 33676003
yes but i can't view video on this machine.  I'm on a company machine and they've blocked access to video feeds.
0
 
LVL 10

Expert Comment

by:t_hungate
ID: 33676010
See capricorn1's how to, and note that it is very important that you design and layout the structure of your database before you start to build it.  Sit down with a pencil and some paper, write out all of the data that you will need to collect, then figure out how it all relates.  That will give you an idea of what data should be contained in what table, it will also make the creation of the table relationships much easier.

You can even go as far as to associate form and queries before you ever open the software.  Once you have a good idea of how you want to tackle the problem putting the database together is the easy part.

If you are new to Access you may want to read over this article as well.

http://www.experts-exchange.com/A_3560.html

TLH
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33676031
0
 
LVL 85
ID: 33677434
What do you mean by "it doesn't link properly"?

To add a Subform, you do as you've done (drag-drop the control onto a form) and then you set the SourceObject of that control to a form. Note that your "parent" form will need to have some form of relationship with your "child" form - for example, if you have a table that holds Invoices and one that holds InvoiceDetails, then you can build a "parent" form that shows the Invoices, and embed a "child" form to show the InvoiceDetails.

"Links" between parent and child forms are defined using the Master/Child links of the Subform object. If you already have a relationship established between the two objects when you build your subform, Access will automatically add thos Master/Child links. If not, then you'll have to define them yourself.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33677547
<when i try linking it through relationships it says indeterminate relationship.>

check your table structure.
are you using primary keys? on the one side of the relationship?
0
 
LVL 85
ID: 33681681
Also, check what you've used when building your forms (i.e. the Recordsource). Are you using Queries or Tables? In most cases, you build a form based on a single table. If you're using Queries, you'll have to insure that (a) your queries are updateable and (b) you've output all the needed Primary and Foreign key fields in order to properly relate your main/sub forms.
0
 

Author Comment

by:RalphS007
ID: 33681691
in response to  ID: 33677547 yes i am using primary keys on the subtable but i've got it linking to a field in the main table that is not with a prmary key associated to it.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33681734
i think you have done the reversed. see this

Main table          sub table
ID   PK  1-----M   mID       FK  foreign key
                          SubID    PK
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33681753
did you see the posted link at http:#a33676031 ?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 33687430
Yes, as cap said, you should link a "child" table by storing the Primary Key value of the "parent". For example, with an Invoice (parent) and INvoiceDetails (child) table, you'd store the value of Invoice.PrimaryID in the INvoiceDetails table. This would relate that table to the Parent, and would allow Access to show the correct child records in your subform.
0
 

Author Comment

by:RalphS007
ID: 33702872
The child is the one with many and the parent is the one with the unique value, correct?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33702888
yep.
0
 

Author Comment

by:RalphS007
ID: 33702911
yep i had it backwards indeed.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33702956
???????
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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