Hello, I have been struggling with this for a while, and I'm hoping I can get some help. The problem I have relates to three tables. I pulled the tables off to the side to show the current relationships between them; Please see attached.

Here is my setup. I created a form that has all the fields in tblEntry, and a combo box whose record source is [EmployeeID] in tblEmployees.

The problem, when the user selects from the combo box, I am getting an entry for the field [EmployeeID] in tblEntry, but I am not getting an entry for [CategoryID] from tblCategory. Basically, I want the user to be able to select one thing and to get two entries: [EmployeeID] and [CategoryID].

Can anyone help me?

Thanks.

If your relationship is correct, then you do not want to pull both the categoryid and employeeid to the Entry table. You will only want to pull the employeeid.

Since the categoryid is stored in the Employee table, it will always be available through the relationship between the Employee and Category table. So by selecting the correct employee, you have the category registered on this employee.

Hello Roy-Vidar,

Thank you for your response. I agree with what you are saying, but it doesn't seem to work. What happens is that when I try to run a query using [CategoryID] as the criteria, I get a null value. It doesn't make sense because all [EmployeeID]'s have a [CategoryID]. I have also tried creating a direct relationship between tblEntry and tblCategory, but that doesn't work either.

Any ideas why?

Thanks.


If your relationship is correct, then you do not want to pull both the categoryid and employeeid to the Entry table. You will only want to pull the employeeid.

Since the categoryid is stored in the Employee table, it will always be available through the relationship between the Employee and Category table. So by selecting the correct employee, you have the category registered on this employee.
__________________
Roy-Vidar

Your combo box should have a colum for [CategoryID]. This can be hidden if you don't want the user to see it. Then refer to this column as your criterion.

Are you then setting the criterion on the categoryid in the Entry table, or the Employee table? It should be the categoryid in the employee table.

In a copy of the db, try to delete the categoryid field in the entry table, to be sure that doesn't messes up the situation.

Thanks to both Neileg and Roy Vidar,

You guys both helped me with this one. I don't know why I didn't realize it before but you are right, I was setting the criteria in the wrong table. Also, I had an unnecessary relation that was creating problems. When I deleted [categoryID] from tblEntry, it solved the problem.

Much obliged to both of you.