By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Lengthy error message when attempting to select from a combobox list

P: n/a
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 (one-to-many to tblDogs)
tblJudgeID (one-to-one to tblTrailClass)
tblPeople (one-to-ont to tblPeople)
tblClasses (see below for how this table is connected)

tblScores has the following fields (all keys are long):
scoreID (primary key - autogen)
dogregnbr (foreign key - tblDogs)
trialClassID (foreign key - tblTrialClass)
score (integer)
level (text - single character - A, B, C)

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 as the follwoing fields:
eventID (primary key)
eventstartdt (short date)
eventenddt (short date)
eventname (text)
eventcity (text)
eventstate (text)

tblJudges has the follwoing 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, Open, 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 knowledge. Thanks.
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.