Because tblScores is foreign keyed to both tblDogs and tblTrialClass if
needs to know *both* the foreign key fields (ie dogregnbr and trialClassID)
values at the time the record is created since, being foreign keys in
tblScores, they are both required fields.
This poses a problem when you want to populate those fields each
individually using a bound combo box.
A workaround is to make cboDogScores and cboEventInfo unbound controls.
To achieve that you could do it like this:
1) create a main form that is unbound
2) make frmDogScores a subform of the main form
3) cut cboDogInfo and cboEventInfo from frmDogScores, and paste them onto
the main form
Delete their ControlSource value to make them unbound.
4) set up link fields on the subform control as follows:
Link Child Fields: dogregnbr; trialClassID
Link MasterFields: cboDogInfo, cboEventInfo
HTH,
Ian.
"Susan Bricker" <slbrick@verizon.net> wrote in message
news:vnmwe.87$jU.22918@news.uswest.net...[color=blue]
>I went back to read my post and found an error in my description ...
> here is the post, again, corrected:
>
> The following error:
>
> "The current field must match the join key '?' in the table that seves
> as t 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."
>
> .. happens when I click on an entry of a combobox. HELP!!
>
> Here's the background:
>
> 1. The form (frmDogScores) is for display and input of SCORES that a
> contestant (in this case a dog)has earned at competitions. The form is
> opened up when the user clicks on a command button (btnScores) in a
> previous form with information about the contestant (frmDogs).
>
> 2. The frmDogScores form has DefaultView = Continuous Forms
>
> 3. Tables involved:
> tblEvents (one-to-many to tblTrials)
> tblTrials (one-to-many to tblTrialClass)
> tblTrialClass (one-to-many to tblScores)
> tblScores (many-to-one to tblDogs)
> tblJudgeID (one-to-one to tblTrailClass)
> tblPeople (one-to-one to tblPeople)
> tblClasses (see below for how this table is connected)
>
> Note: (all keys are defined as Long)
> Note: Not all fields in each table are listed. Just the pertinant
> fields.
>
> tblScores has the following fields:
> scoreID (primary key - autogen)
> dogregnbr (foreign key - tblDogs)
> trialClassID (foreign key - tblTrialClass)
> score (integer)
> level (text - single character - A, B, C)
>
> tblDogs has the following fields:
> dogregnbr (primary key)
> peopleID (foreign key or owner)
> formalname (text)
> callname (text)
>
> tblTrialClass has the following fields:
> trialClassID (primary key)
> trialID (foreign key - tblTrials)
> judgeID (foreign key - tblJudges)
> classID (foreign key - tblClasses)
>
> tblTrials has the following fields:
> trialID (primary key)
> eventID (foreign key - tblEvents)
> trialdt (date of trial - short date)
> trialnbr (number - integer (1,2,3,...))
>
> tblEvents has the follwoing fields:
> eventID (primary key)
> eventstartdt (short date)
> eventenddt (short date)
> eventname (text)
> eventcity (text)
> eventstate (text)
>
> tblJudges has the following fields:
> judgeID (primary key)
> peopleID (foreign key - tblPeople)
>
> tblPeople has the following fields:
> peopleID (primary key)
> fname (text)
> midinit (text)
> lname (text)
>
> tblClasses has the following fields:
> classID (primary key)
> class (text: "Novice" or "Open" or "Utility")
>
> I want to display the dogname at the top of the frmDogScores form and
> display all the scores for that dog. Each score row should display the
> event name, trial number, trial date, event city, event state, class,
> level, and score.
>
> Here's how I did it ... it almost works.
>
> Form RecordSource:
> SELECT tblScores.scoreID, tblScores.dogregnbr, tblScores.trialclassID,
> tblScores.score, tblScores.qualified, tblScores.note,
> tblScores.processed, tblScores.processeddt, tblScores.level,
> tblTrialClass.judgeID
> FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
> tblTrials.eventID) INNER JOIN ((tblClasses INNER JOIN tblTrialClass ON
> tblClasses.classID = tblTrialClass.classID) INNER JOIN (tblPeople INNER
> JOIN (tblDogs INNER JOIN tblScores ON tblDogs.dogregnbr =
> tblScores.dogregnbr) ON tblPeople.peopleID = tblDogs.peopleID) ON
> tblTrialClass.trialclassID = tblScores.trialclassID) ON
> tblTrials.trialID = tblTrialClass.trialID
> ORDER BY tblScores.dogregnbr, tblTrials.trialdt, tblEvents.eventname,
> tblTrials.trialnbr, tblClasses.class;
>
> Bound combobox in Form Header Section: cboDogInfo
> cboDogInfo ControlSource: dogregnbr (tblScores)
> cboDogInfo RowSource: SELECT tblDogs.dogregnbr, [tblDogs].[formalname] &
> " [" & [tblDogs].[callname] & "] " & "(" & [tblPeople].[fname] & " " &
> [tblPeople].[midinit] & " " & [tblPeople].[lname] & " " &
> [tblPeople].[suffix] & ")" AS doginfo
> FROM tblPeople INNER JOIN tblDogs ON tblPeople.peopleID =
> tblDogs.peopleID
> WHERE (((tblDogs.removed)=False))
> ORDER BY tblDogs.formalname;
>
> Fields in each row/record:
> Bound combobbox: cboEventInfo
> cboEventInfo ControlSource: trialclassID (tblScores)
> cboEventInfo RowSource: SELECT tblTrialClass.trialclassID, " (" &
> [tblTrials].[trialdt] & ") * " & [tblEvents].[eventname] & " * Trial #"
> & [tblTrials].[trialnbr] & " * " & UCase([tblClasses].[class]) & " * " &
> [tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] AS eventinfo
> FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
> tblTrials.eventID) INNER JOIN (tblClasses INNER JOIN tblTrialClass ON
> tblClasses.classID = tblTrialClass.classID) ON tblTrials.trialID =
> tblTrialClass.trialID
> ORDER BY tblTrials.trialdt, tblEvents.eventname, tblTrials.trialnbr,
> tblClasses.class;
>
> Bound combobox: cboLevel
> cboLevel ControlSource: level (tblScores)
> cboLevel RowSourceTyepe: Value List
> cboLevel RowSource: "A","B","C"
>
> Bound textbox: score (tblScores)
>
> Bound combobox: cboJudgeInfo
> cboJudgeInfo ControlSource: judgeID (tblTrialClass)
> cboJudgeInfo RowSource: SELECT tblJudges.judgeID, "Judge: " &
> [tblPeople].[fname] & " " & [tblPeople].[midinit] & " " &
> [tblPeople].[lname] & " " & [tblPeople].[suffix] AS judgeinfo
> FROM tblPeople INNER JOIN tblJudges ON tblPeople.peopleID =
> tblJudges.peopleID;
>
>
> **************************************************
> If a dog has scores then the form displays all info just fine. However,
> when I go to the new record row and and attempt to select an item in the
> cboEventInfo dropdown list, that's when I get the error message. The
> information in the combobox list is correct, so the query is working.
>
> I have been struggling with this for quite some time. Anybody want to
> try to tackle this and give me a hint on how to fix it? Please keep in
> mind that I am not a real Access/VBA programmer. I am pretty much
> self-taught so please make no assumptions about my prior knowledge.
> Thanks.
>
>
> Regards,
> SueB
>
> *** Sent via Developersdex
http://www.developersdex.com ***[/color]
