Form sub-form error: The current field must ...

[TCARPENTER]

I'm having a problem with a form and subform, that I have actually been using for a while. In a subform, if I try to add a record, I'm getting the following error:

The current field must match the join key 'Tools_Table.Tools_Recnum' in the table that serves as the 'one' side of the ont-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.

Here's the strange part, there is no problem with this in the tables, only in the form and only if the tool has been referenced in another location. I'm stumped. Here is my table structure:

Tools_Table
[ul]
[li]Tools_Recnum - PK - Autonumber[/li]
[li]Tools_Descr[/li]
[li]...[/li]
[/ul]

ToolsDetail_Table
[ul]
[li]ToolsDetail_Recnum - Index - Autonumber[/li]
[li]Tools_Recnum - FPK - Required: Yes (Duplicates OK)[/li]
[li]Location_Recnum - FPK - Required: Yes (Duplicates OK)[/li]
[li]Notes[/li]
[/ul]

Location_Table
[ul]
[li]Location_Recnum - Index - Autonumber[/li]
[li]Location_Address - PK[/li]
[li]Location_Descr[/li]
[li]...[/li]
[/ul]

My main form is based on my location table and my subform is based on a query which joins Tools_Table and ToolsDetail_Table.

If I add a tool that has not been referenced in the detail table before, no problem, but if the tool already exsists, I get the above error message - what am I missing?
Todd

 

[KenReay]

Hi

You say

"subform is based on a query which joins Tools_Table and ToolsDetail_Table."

Why not a query simply based on ToolsDetail_Table?


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TCARPENTER]

Hi Ken,

Thanks for responding, the reason is just for flexibility. The users decided instead of flipping back and forth from a setup form to a working form all the time, they wanted the flexibility to edit Tool information in the working form.

Incidentally, for grins, I did try setting the forms record source to just the ToolsDetail_Table and got the same error.

Any ideas?

Todd

 

[TCARPENTER]

Finally figured it out - I had a control set to a default value - removed the default value, problem solved!

Todd