469,273 Members | 1,769 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,273 developers. It's quick & easy.

Lengthy error message when attempting to select from a combobox list

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,
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 =
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 =
ORDER BY tblTrials.trialdt, tblEvents.eventname, tblTrials.trialnbr,

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 =
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.

*** Sent via Developersdex http://www.developersdex.com ***
Nov 13 '05 #1
0 1675

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Peter Frost | last post: by
3 posts views Thread by Adriano | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.