Field Key, Join Key Mismatch Error (1 Viewer)

[Big D]

Greetings:
Has anyone ran into the following error:

The error: "The current field must match the join key '?' in the table that serves as the 'one' side of one-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."

Very little, if any, information via Google search to help understand a) what this error actually is in layman's terms, b) what caused it after people have been successfully using the database (zipped copy attached) for about 3 months and more importantly c) how does one fix this sort of error? Help resolving this error is greatly appreciated.

Cheers,
Big D

 

[bob fitz]

Could it be that you are trying to create a new record in a sub form but the main form is at a new, blank record.


Sent from my SM-A600FN using Tapatalk

 

[Big D]

Hi Bob, that might be the case. The first button on the Switchboard to "Enter Project Details" does have a link to "Add Task" into a subform but for some unknown reason shortly after launching the database, it started to duplicate the tasks and caused a huge amount of confusion. So I asked people to enter their project tasks using the 2nd button on the Switchboard which is the "Task Details" button. It seemed to be working fine using this approach until recently.

Cheers,
Big D

 

[theDBguy]

Hi Big D. I downloaded your file but can't figure out where to look for this error. Can you please provide a step by step instruction on how to duplicate the problem? Thanks!

 

[mike60smart]

Hi Big D

I only looked briefly at your tables and have the following observations which need addressing.

1. You should NOT use Lookup Fields in tables.

You are using a Lookup as the Foreign Key in the Tasks table to link to the Master ID Primary Key in table Projects.

When you create your Main Parent Form for Projects you should base this on the Projects Table and NOT both Projects and Tasks.

Then the related Child Form for related Tasks should also ONLY be based on the Tasks Table and NOT both Projects and Tasks.

Also I could not replicate the error as noted by theDBGuy

 

[Big D]

Hi mike60smart & theDBguy:

First of all, many thanks to you both for looking into my database problem. To answer your question concerning how the problem surfaces. Please follow these steps:

1. Select the "Task Details" button on the switchboard.
2. Select the LAST RECORD...all fields will be blank.
3. Enter the task title in the title field.
4. Select the project drop-down menu and select a project. Upon selecting a project, a pop-up message will appear stating the problem.

Cheers,
Big D

 

[isladogs]

I followed your instruction. The new record was created with various control default values shown. There was no error message

 

[theDBguy]

Hi mike60smart & theDBguy:

First of all, many thanks to you both for looking into my database problem. To answer your question concerning how the problem surfaces. Please follow these steps:

1. Select the "Task Details" button on the switchboard.
2. Select the LAST RECORD...all fields will be blank.
3. Enter the task title in the title field.
4. Select the project drop-down menu and select a project. Upon selecting a project, a pop-up message will appear stating the problem.

Cheers,
Big D

Hi Big D. Thanks for the additional information. I was able to see the error message. However, I have a question, why does the user have to select the "last record" and why is it "blank?" Check out the attached modified version of your db and instead of selecting the "last" record, the user can select the "new record" to add a new title and project. Hope it helps...

 

[Big D]

Hi theDBguy,
Absolutely, users should select the "new record" to enter a new task. I tried out the modified version that you provided and it works perfectly! No more error message popping up! I can't thank you enough for your help!

All the fields in the form are working except for the drop-down for the "Billing Rate". Did this need to be disabled in order to stop the error from occurring?

Cheers,
Big D

 

[theDBguy]

Hi theDBguy,
Absolutely, users should select the "new record" to enter a new task. I tried out the modified version that you provided and it works perfectly! No more error message popping up! I can't thank you enough for your help!

All the fields in the form are working except for the drop-down for the "Billing Rate". Did this need to be disabled in order to stop the error from occurring?

Cheers,
Big D

Hi Big D. Let me check the Billing Rate control. I didn't look at it earlier.

 

[Big D]

Hi theDBguy,

Outstanding!!!! This day started out very rocky but looks like it's going to result in a great ending! Thanks a bunch for all your help!!!

Cheers,
Big D

 

[theDBguy]

Hi theDBguy,

Outstanding!!!! This day started out very rocky but looks like it's going to result in a great ending! Thanks a bunch for all your help!!!

Cheers,
Big D

Okay, check it out now. It looks like the Billing Rate is based on the shipping method, so I took out the dropdown, and the user shouldn't need to select a rate, just a shipping method. Hope it helps...

 

[Big D]

Hi theDBguy,

Yes, this will work fine. I see that when the "Priority" drop-down menu item is selected "Standard" work the "Billing Rates" field is populated with $70. This is the correct amount. But when "Emergent" work is selected from the "Priority" field, the "Billing Rates" field gets populated with $150. Can I impose on you to change this amount from $150 to $115? I'd go ahead and try to do it but it would probably take me a year to figure out how to do it. Thanks again for all your fantastic help, theDBguy!!!

Cheers,
Big D

 

[theDBguy]

Hi theDBguy,

Yes, this will work fine. I see that when the "Priority" drop-down menu item is selected "Standard" work the "Billing Rates" field is populated with $70. This is the correct amount. But when "Emergent" work is selected from the "Priority" field, the "Billing Rates" field gets populated with $150. Can I impose on you to change this amount from $150 to $115? I'd go ahead and try to do it but it would probably take me a year to figure out how to do it. Thanks again for all your fantastic help, theDBguy!!!

Cheers,
Big D

Hi Big D. That should be an easy change to make. Go to the Design View of your Tasks table and change the value in the spot shown by the arrow below.

 

[Big D]

Hi theDBguy,

Got it changed. Thank so much for your patience and all your help!!! Hope some day I can return the favor. Thanks again!!

Cheers,
Big D

 

[theDBguy]

Hi theDBguy,

Got it changed. Thank so much for your patience and all your help!!! Hope some day I can return the favor. Thanks again!!

Cheers,
Big D

Hi. You're welcome. We're all happy to assist. Good luck with your project.