Software

Ask something
Question Locked

MS Access

0 helpful
10 replies

by dayle1011

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello All,
I'm in a bind. For some reason, I can't seem to create a FORM in MS Access using two tables, that are related ID# on a single form. I get an error message that the Join Key is not vaild. Something to that effect. I've never had this problem. Could someone please HELP?

Thanks
~Anita

10 replies

by john.a.wills

You should give us some more informaton. At a guess, what you ought to do is construct a query with an explicit JOIN of the tables ON the ID# then use the wizard to construct the form on the basis of that query. If that is what you are already doing, please give more details.

by dayle1011

Thank you John, I'll try and see if it works. I've already related the tables by ID number, so are you saying I should create a query and re-join them?

by dayle1011

I created the query with the relationship of ID#. Unfortunately, I can't enter any data into the form. Am I having a DUH moment? Basically this is what I have:
1. 3 tables: Master List, Department & Benefits
2. Each table is related by SS#, with the Master List containing the primary key.
3. I would like to create one continuous form where data can be entered into all 3 forms.
4. Unfortunately, I can enter data into the Master List, but when I tab into the fields from the other 2 tables, I can't enter any data.

by myronjams

The reason your not able to enter data is due to the relationships you created. You need to make sure the Master table is populated first as that is the Primary Key to the Foreign Key in the other tables; provided you set up the Relational Constraints. For instance you may have an Employee table with SS# as the PK, Name & Pay related to the Address Table where the SS# is the FK, there has to be a SS# in the Employee table before you can add any data to the Address Table for that SS#; Just set up the code to populate the Master Table with the Primary Key first then the other data next.

Hope this helps

by alan williams

You need the Master form to deal with the Main table and a sub form added to the main form for each of the related tables. I believe that there is aWizard to add sub forms.

by myronjams

Or create a query using those tables and join them accordingly.

by dayle1011

Thank you for your help. I've already related the tables, but are you saying I should query the tables and then base the form from that?

by dayle1011

Thank you for your response. I have a much better understanding of my problem.

by mnmcollector

In addition to all of the information that was previously posted, make sure you have your relationship set correctly. By default, Access wants to define an Equijoin which would require data to be in both tables in order to view anything. Since the Master table is Primary, set the join to be all records in Master and only those that are in the other tables. I have had this type of a join cause me problems in the past also.

This discussion is closed to new replies.