Relationship or Form/Subform issue

[Malavia]

I'm starting to feel stupid. I spent all day on this yesterday and couldn't figure out my problem.

I'm not going to give up though.

I'm just getting started, but I didn't go into this naively. I want to learn and I will continue to read the book I have, search google, and seek out guidance from those that have experience.


If you get the time to look it would be appreciated.

I think I might be having a relationship issue or some setting with a Form/subform connection.

I have two tables (t_People; t_Contact). I have a relationship from ContactID *primary key (t_Contact) to ContactID (t_Professors).

I created a form for People names and a subform for Contact. When I fill out the form with people name, then the subform with contact info and navigate to a new record the form changes for me to enter a new people, but the subform stays the same with the old address.

 

[EternalMyrtle]

Your subform is not linked to the master form. You need to open the main form in design view and select the subform, then choose the fields you want to link.

I don't see a matching field in your subform.

I looked at your tables. Contact Info needs an FK field to the People table.

Then you can link the FK field in Contact Info table to the PK field in People. Does this make sense?

I will fix it and post for you.

 

[EternalMyrtle]

Here you go. I put the ProfID field into the child table (contact info). I also put the ProfID field into both the parent form and the subform (but made it invisible). The forms are now linked by this field.

You could also consider linking by SS# since it is unique but I wouldn't do it.

You may want to give the ID field a more generic name or name it after your table, so PeopleID or something. Unless this is really just for Professors?

Hope this helps.

 

[EternalMyrtle]

Sorry to post so many messages but there is one more important thing...

I just realized that I forgot to "consummate" the relationship in the relationships window. When I looked there, I saw that you had intended to link AddressID as the PK/FK but the relationship would be backwards.

The parent table is People (this is the ONE side of the relationship) and the child table is Contact info (the MANY side), so you need the FK to be in the child table, not the parent table.

When you go into the relationships window, you should remove the AddressID relationship and link the ProfID fields in the two tables. Then select enforce referential integrity.

The relationships should like like the attached image.

I hope this helps.

 

[Malavia]

I’m not sure what happened with my original attachment, but I had relationships set up and showing in the relationships tab only they weren’t in the attachment. Of course they were wrong, but they were supposed to be there.


Thanks for the responses, they were extremely helpful and I was able to accomplish my goal.


So the rule of thumb is that when I want to link a master and subform, I need to include the actual fields that are linked in the tables and then just hide the linked fields in the forms, that and make sure my table relationships are correct?


Well on to learning how to do a query and report. I'm sure I will be back with more questions.

 

[EternalMyrtle]

Hi, glad it helped!

You don't actually have to include the linked fields in the form/subform but it helps if you intend to link the form(s) to other forms or reports in the future. I just put it in there to show you. If you make them visible you will see that they match up.

The important thing is that the tables include the proper ID (PK/FK) fields and that the correct relationship is established. You also have to make sure the query shows the fields even if you do not use them in your forms otherwise there is no Master/Child field to link. The fields do not have to have the same names either.

If you try removing the ProfID field from both the forms and you will see that it still works. That is because it is now included in the tables and in the query for the subform.

 

[EternalMyrtle]

Hi, glad it helped!

You don't actually have to include the linked fields in the form/subform but it helps if you intend to link the form(s) to other forms or reports in the future. I just put it in there to show you. If you make them visible you will see that they match up.

The important thing is that the tables include the proper ID (PK/FK) fields and that the correct relationship is established. You also have to make sure the query shows the fields even if you do not use them in your forms otherwise there is no Master/Child field to link. The fields do not have to have the same names either.

If you try removing the ProfID field from both the forms and you will see that it still works. That is because it is now included in the tables and in the query for the subform.