?
Solved

Linking two forms/tables in Access

Posted on 2012-08-27
14
Medium Priority
?
531 Views
Last Modified: 2012-08-28
I am creating an access database and am trying to link two tables and utilize a form in adding and updating info within these tables.  I have successfully created a relationship, but i can't seem to get an ID number created in the equipment table that matches the ID number created in the main table so the data between the two forms/tables are linked.  

My desired output is to create a record in the main form, then when i click on the equipment button on this form, the data entered on that form correlates to the data on the main table.  

I have attached a copy of my database so you can see what i have created so far.  Let me know if you need additional information.  

Thanks.....
Database1.accdb
0
Comment
Question by:jsawicki
  • 6
  • 3
  • 3
  • +1
14 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 38337728
Test this sample. You can use form and subform (Main and Equipment) or add ID to the query in record source and to default value (Main1 and Equipment1)
DB27843596.accdb
0
 
LVL 2

Assisted Solution

by:BobOxford
BobOxford earned 1000 total points
ID: 38337743
I have adjusted your design slightly and merged the two forms together into a single form so that when you move from record to record in the main form, you will see the records in the equipent for that correspond to the main record.   This should show you how the basic design of the tables should be set up.

After that, I see that you really want to be able to click a button to call up the equipment table.  Once you have adjusted the design, it is a simple matter to use the Form Wizard to create the Forms you want.  I have attached a database with the examples.  Open up Main1 to see the adjusted Form with the button you wanted to open the equipment form.

The Equipment form, created by the Form Wizard, may not be exactly how you envisioned it but you can adjust the form to meet your needs.

Hope that helps.
Database1-revised.accdb
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38337800
The reference can not be Infinite to one according your tables, Must be One to One or do this design
Database11.accdb
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:jsawicki
ID: 38338325
Als315 & BobOxford,  Thanks for the two examples since the button in a form is definitely what i am looking for.  Just so i understand, what exactly did each of you do to link the two forms.  I have reviewed all the coding and options and can't seem to figure out exactly what piece links them.  This is why i had to pose the question so knowing this will help me with future projects or potentially linking additional forms to this one form.  

k-designers:  unfortunately your database did not work.  When i clicked on the equipment button and tried entering data, i got a index or primary key can't contain a null value error which was the same error i received which told me the two were not ajoined by a common ID or field.
0
 

Author Comment

by:jsawicki
ID: 38338353
Bob, one thing i noticed for yours is that i have to actually enter the main ID in the equipment form in order to have them link.  Is there a way to have them auto link without having to enter that info in the equipment form?
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38338358
Sorry, didn't realize, hast to change the field ID from Number to Autonumber on the equipment table and will work
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38338368
That's the correct database
Database11.accdb
0
 
LVL 2

Expert Comment

by:BobOxford
ID: 38338425
The simplest way at the moment is, in the Default Value property of the MainID Control on the Equipment form, put this: =[Forms]![Main1]![ID]

This requires that the Main1 Form be open when the Equipment Form is open but since you are opening it from the Main1 form, this shouldn't be an issue.  If you were to open the Equipment form some other way, you would see an error
0
 

Author Comment

by:jsawicki
ID: 38338493
K-designers, it works now.  How were you able to get the one to one relationship.  Everytime i create that relationship, it gives me one to many.  Just trying to understand all examples.  

Thanks Bob for your insight on how you got them to link.  The goal would be not to open the equipment form outside of the main form.
0
 

Author Comment

by:jsawicki
ID: 38338677
k-designers, i figured out how to do the one to one relationship, however, it doesn't put a 1 to 1 on each side it still shows the foreign key sign on the main table.  Can you tell me what you did to get the table id's to link because no matter what i do on my database, i can't get the two fields to link and in fact, my main table doesn't show a subcategory with the plus sign anymore.  I liked your example based on the simplicity.  Once i get this i will go ahead and award the points and close this question.  

Thanks all.....
0
 

Author Comment

by:jsawicki
ID: 38338683
When i say foreign key, i meant the infinite sign.  How do i change that to a 1?
0
 
LVL 2

Expert Comment

by:BobOxford
ID: 38339223
If you want a one to one relationship between the two tables you would need to make the MainID field in the Equipment table the Primary Key.  You won't be able to do this if you have created a relationship between the two tables.  Or if there are any records in the Equipment table where a duplicate number exists in the MainID field.  You will first need fix that and then remove the relationship.  Go into the relationships window, click on the line representing the join (It will turn bold when you have succeeded) and press delete.  That will delete the relationship.

Now adjust the Primary Keys in both tables.  The Primary Key in the Main Table would be an autonumber. I believe that you called it ID.   The primary key in the related table (Equipment) would be what I created and called the MainID field.  Once you have set the Primary keys in both tables and you make sure that there is no more than 1 record in the Equipment Table for each record in the Main table, you would then go into the relationship window and connect the two tables linking between the ID(Autonumber)  field in the Main Table and the MainID(Number) in the Equipment Table.  Be sure you click "Enforce referential integrity".  Because you have joined between 2 primary keys, Access will set the relationship to be a 1:1

This structure will mean that you will only be able to have a single record in the equipment table for each record in the Main Table.  You will not be able to have a single record in the Main table connect with many records in the Equipment table.

Hope that helps.
0
 
LVL 40

Expert Comment

by:als315
ID: 38339496
@jsawicki: As I've mentioned in my comment, you can use query as record source of your Equipment form. In this query you can limit records to ID from Main form (look record source at sample above - Equipment1 form). If you like to add records, you can set default value for ID field on a Equipment form equal to ID from Main form (it is made also in sample).
0
 

Author Closing Comment

by:jsawicki
ID: 38341045
Thanks for your comments, help and insight.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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