Form based on query not working (1 Viewer)

[robert693]

I accidentally posted in the Macro & Module forum by mistake first! Anyway, I have a form based on a query. The query is based on three tables. One table is called Institution, it holds information about universities, hospitals and other institutions. Another is called Contact, which holds information about contacts from the institutions. A third table joins the two and hols information from surveys the institution sends us. The form we use to enter this infomation allows us to enter information about the contact and the survey. Entering info about the contact is fine but when a person enters info about the survey a error message appears that says "join key of Survey not found in recordset". I am able to click out of the form and come back in to the sme record and then the message does not appear. Can anyone help with this? I would greatly appreciate it!



Report this post to a moderator | IP: Logged

 

[erislover]

I am having the EXACT same problem. Let me guess, you have a many-to-many relationship set up with a "link" table inbetween the other two (where each table forms a many to one relationship here)?

Well, even if not, that's my problem. And what is killing me is that the underlying queries are displaying all the appropriate information, but when I build a form based on these queries nothing works. All the information is not displayed.

Worse than that, it allows me to enter information in, and this, in fact, is where the error was coming from, because I was trying to re-enter information that was already there but was mysteriously not displaying!

I am stumped.

 

[erislover]

Oh, join key not found.... no, we are having different problems of the same type.

 

[robert693]

When I get to a field that pertains to the survey it does not allow me to and a message appears on the task bar below that says "cannot add record(s); join key of table 'Survey' not in recordset.

 

[Fizzio]

Can I just get your data structure right in my mind first. The way you have structured it seems to be a Many-Many with Survey as the linked table. Therefore an institution can have many contacts and a contact can work for many institutions. Each Survey is from 1 contact in a given institution - Am I right so far? Can each combination of Institution and Contact have more than 1 survey? (See Image)

 

[robert693]

You are correct

The reason it is many to many for contacts is that the contacts can work for different institutions at the same time and do move around alot from year to year. This is a multi year database. I did not want to put the contact in the survey table because we need their personal info and I did not want to type it in multiple times and wanted to have one place to change it if necessary.

 

[David R]

Make sure the joining table is in your query. If you don't include it, Access has no way to know what records join to what in your two primary tables.

Your form should be based on ONE of the main tables; your subform should be based on the jointable+the other main table. If you try to put all three in one form I can see where you'd have problems.
In your case it sounds like you want your main form to be Contacts and the subform to be about the different surveys. You can of course have another form that is exactly the opposite, showing the Survey in the main form window and related contact information in the subform.

HTH,
David R

 

[Randomblink]

Many-to-Many / Front-End / Add New!

I posted the same basic request on another forum and got nowhere. Let me see if my wording asks the same question and whether anyone can help me here or not.

Ok...

I have Three tables, I am going to give an example and hope someone can help.


Table #1: [tbl_Employee]
Table #2: [tbl_LINK_EmployeeProject]
Table #3: [tbl_Project]

Ok, in Table #1, I have three fields.
#1) Employee_ID (This is an autonumber field, primary key)
#2) Employee_Name
#3) Employee_Department

Ok, in Table #2, I have two fields.
#1) Employee_ID (This is an Integer field, primary key)
#2) Project_ID (This is an Integer field, primary key)
In Table #2, I set them both as Primary keys by highlighting both fields and clicking on the key icon in Access.

Ok, in Table #3, I have many fields, but only two are important.
#1) Project_ID (This is an autonumber field, primary key)
#2) Project_Title

I have gone into the relationship area and linked the following fields.

[tbl_Project].[Project_ID] connected-to [tbl_LINK_EmployeeProject].[Project_ID]
AND
[tbl_Employee].[Employee_ID] connected-to [tbl_LINK_EmployeeProject].[Employee_ID]

Ok...
I create a form to input new Projects. What I want to do is:

I have a drop-down/combo-box: [cmb_AssignEmployee] that looks up values from [tbl_Employee]. What I want to do is select an employee with this box, upon selecting an employee it will then create a new record in [tbl_LINK_EmployeeProject] where the [tbl_LINK_EmployeeProject].[Project_ID] field will equal the [tbl_Project].[Project_ID] AND where the [tbl_LINK_EmployeeProject].[Employee_ID] field is equal to the selection I just made from the combo box.

Does this make sense?

Basically, I want to link several Employees to each Project. Several Employees can be on ONE Project AND several Projects can be handled by ONE Employee.

Other than manually inputting directly into the table I don't know what to do. And the BIGGEST problem is, this Project is linked the same way to many other tables in the exact same way

 

[David R]

Posted back to your original topic: http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=95305