Subform from a Query Error

[sondriven]

Relationships and Queries

I created a simple 3 table relationship. The third table is a junction table. All three are related to each other. Now I built a query for a subform. I put the subform into my main form. I went to add data into the subform and this error popped up:

Cannot add record(s) join key of Table " " not in recordset.

What do I need to do from here?

Thanks.

j

 

[Rob.Mills]

So you've setup a many-to-many relationship. And the data that you're putting in the subform is going into the junction table, correct?

What's happening here is that the junction table is looking for the id's from both tables to go in each record to setup the relationship correctly. I'm guessing that in the main form you've only got one of the tables in the recordsource.

What I would suggest is if you've already got the subform linked to the mainform then leave that as is. But setup a combobox on the main form whose selects values from the other table. Then in your subform create a hidden textbox whose control source is the foreign key for the other table and whose default value references the combobox you created. The should get both id's in there.

 

[sondriven]

I have been receiving help from Pat Hartman and Im really close on solving this issue but im thinking he gave up on me.....



Maybe this can help you out on exactly what I have here:

http://www.access-programmers.co.uk/forums/showthread.php?threadid=39000&goto=newpost


I figured out how to build the relationships. I need help to build the query and put the form with the subform together from the queries.

Thanks.

j

 

[Rob.Mills]

Well to be perfectly honest if Pat can't help you I hardly think I can. I believe I understand the premise of what needs to be done to make this work but I haven't crystalized this in my head yet. I'll keep thinking about it, though.

BTW, I think Pat's a she. Not totally sure, though. Guess that's the challenge of using a gender-neutral name on a forum.

 

[sondriven]

Oh sorry Pat if youre a female. Not been around long enough to know.

I dont think she cant help me, I think she wants me to figure it out on my own. But Im really stuck on this.

Thanks for trying.

j