jsawicki
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?
Sorry, didn't realize, hast to change the field ID from Number to Autonumber on the equipment table and will work
That's the correct database
Database11.accdb
Database11.accdb
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
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
ASKER
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.
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.
ASKER
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.....
Thanks all.....
ASKER
When i say foreign key, i meant the infinite sign. How do i change that to a 1?
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.
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.
@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).
ASKER
Thanks for your comments, help and insight.
Database11.accdb