none
Current field must match join key '?'...Error 3341; query works, form doesn't RRS feed

  • Question

  • Hi,

    I'm working on improving an Access database that stores student, tutor, and guardian information for a tutoring business. 

    I have a main form for student details with a subform for a "plan," which basically associates a schedule with a tutor and subject. The Plan subform is bound to the Plans Query, which retrieves the data from the Plans table (Plan ID, subject, specialty, Students and Schedules ID, Tutors and Schedules ID) as well as Student ID from the Students and Schedules Table and Tutor ID from the Tutors and Schedules Table. There is a one to many relationship between Students and Schedules and Plans and between Tutors and Schedules and Plans, in essence Many-One-Many. Students and Schedules ID is a lookup field, which displays the data from the Schedules table (e.g., start time, end time) for the student. 

    Entering a new plan via the query works fine; however, when using the form, I get a 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. error, which is error number 3341. I've spent some time on Google and Bing and read many different things, none of which seemed to exactly replicate my issue.

    I've looked at the KB article here http://support.microsoft.com/kb/928023?wa=wsignin1.0 and here http://support.microsoft.com/kb/208564.

    As I mentioned before, inputting via the query works fine! I can enter in data with no issue; it's when using the form that an issue arises. So, while similar to 928023, it is distinct there.

    One thing that I've noticed is that when adding a new record, there is a value already input into the StudentsAndSchedID combo box; I have no idea why this is happening and I believe that is the reason for the issues. If I press ESC twice and go back, it lets me choose the right schedule without issue.

    Also, setting the mode to Inconsistent Dynaset works.

    Could anyone shed light on this? I'd like to use normal Dynaset, but if that's not possible, then I can settle I suppose for inconsistent dynaset; alternatively, and how I originally had this, I had separate fields within the Plans table for StudentID and TutorID. I went to this method to try and eliminate some redundancy, but if that's the simplest way, then I could settle for that, also.

    Thanks for your help! 

    Wednesday, December 26, 2012 4:55 AM

All replies

  • This is a quick note to let you know that we are doing research on this issue and we will let you know if there is any progress.

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 28, 2012 10:23 AM
    Moderator
  • Hello wwelchj,

    It appears the issue is in some way related to the articles you've mentioned. Since this only occurs in the form/subform view, maybe it's related to how the parent form is linked to the subform.

    Some thoughts/tests:

    1. In the form design, select the subform control. Within the data tab of the properties window, remove the 'link master fields' and 'link child fields' settings to remove the link between the forms. When you change the form back to form view and enter a new record in the subform, does the error still occur?

    2. When you open the Plans query in datasheet view, do you see any values populated in the new record row (the last row which should have an * at the left side)? Other then the word (New) for an autonumber, a value here would indicate a default value is specified for that field.

    3. It's difficult to fully reconstruct your tables with the descriptions given. However it seems as though your parent form is intended to display the student data, but yet you include the students table within your Plans query and have the student data displayed within the subform. I would consider removing the students table from the plans query and only displaying student data in the parent form.

    If these suggestions do not yield progress for you. I would suggest detailing the field names/datatypes for each table and then provide the SQL syntax of the Plans query.

    Regards,

    Dennis


    Friday, December 28, 2012 3:58 PM
    Answerer
  • This usually happens when you copy / paste a control in the subform. Just clear the default value of the new control and you're OK.
    Monday, January 6, 2014 2:20 AM