• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

Linking two forms/tables in Access

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
jsawicki
Asked:
jsawicki
  • 6
  • 3
  • 3
  • +1
2 Solutions
 
als315Commented:
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
 
BobOxfordCommented:
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
 
Jesus RodriguezIT ManagerCommented:
The reference can not be Infinite to one according your tables, Must be One to One or do this design
Database11.accdb
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jsawickiAuthor Commented:
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
 
jsawickiAuthor Commented:
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
 
Jesus RodriguezIT ManagerCommented:
Sorry, didn't realize, hast to change the field ID from Number to Autonumber on the equipment table and will work
0
 
Jesus RodriguezIT ManagerCommented:
That's the correct database
Database11.accdb
0
 
BobOxfordCommented:
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
 
jsawickiAuthor Commented:
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
 
jsawickiAuthor Commented:
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
 
jsawickiAuthor Commented:
When i say foreign key, i meant the infinite sign.  How do i change that to a 1?
0
 
BobOxfordCommented:
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
 
als315Commented:
@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
 
jsawickiAuthor Commented:
Thanks for your comments, help and insight.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now