Repost (corrected explanation): Lengthy Error from Combobox

[Susan Bricker]

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.score ID, tblScores.dogre gnbr, tblScores.trial classID,
tblScores.score , tblScores.quali fied, tblScores.note,
tblScores.proce ssed, tblScores.proce sseddt, tblScores.level ,
tblTrialClass.j udgeID
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.event ID =
tblTrials.event ID) INNER JOIN ((tblClasses INNER JOIN tblTrialClass ON
tblClasses.clas sID = tblTrialClass.c lassID) INNER JOIN (tblPeople INNER
JOIN (tblDogs INNER JOIN tblScores ON tblDogs.dogregn br =
tblScores.dogre gnbr) ON tblPeople.peopl eID = tblDogs.peopleI D) ON
tblTrialClass.t rialclassID = tblScores.trial classID) ON
tblTrials.trial ID = tblTrialClass.t rialID
ORDER BY tblScores.dogre gnbr, tblTrials.trial dt, tblEvents.event name,
tblTrials.trial nbr, tblClasses.clas s;

Bound combobox in Form Header Section: cboDogInfo
cboDogInfo ControlSource: dogregnbr (tblScores)
cboDogInfo RowSource: SELECT tblDogs.dogregn br, [tblDogs].[formalname] &
" [" & [tblDogs].[callname] & "] " & "(" & [tblPeople].[fname] & " " &
[tblPeople].[midinit] & " " & [tblPeople].[lname] & " " &
[tblPeople].[suffix] & ")" AS doginfo
FROM tblPeople INNER JOIN tblDogs ON tblPeople.peopl eID =
tblDogs.peopleI D
WHERE (((tblDogs.remo ved)=False))
ORDER BY tblDogs.formaln ame;

Fields in each row/record:
Bound combobbox: cboEventInfo
cboEventInfo ControlSource: trialclassID (tblScores)
cboEventInfo RowSource: SELECT tblTrialClass.t rialclassID, " (" &
[tblTrials].[trialdt] & ") * " & [tblEvents].[eventname] & " * Trial #"
& [tblTrials].[trialnbr] & " * " & UCase([tblClasses].[class]) & " * " &
[tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] AS eventinfo
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.event ID =
tblTrials.event ID) INNER JOIN (tblClasses INNER JOIN tblTrialClass ON
tblClasses.clas sID = tblTrialClass.c lassID) ON tblTrials.trial ID =
tblTrialClass.t rialID
ORDER BY tblTrials.trial dt, tblEvents.event name, tblTrials.trial nbr,
tblClasses.clas s;

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.judge ID, "Judge: " &
[tblPeople].[fname] & " " & [tblPeople].[midinit] & " " &
[tblPeople].[lname] & " " & [tblPeople].[suffix] AS judgeinfo
FROM tblPeople INNER JOIN tblJudges ON tblPeople.peopl eID =
tblJudges.peopl eID;
*************** *************** *************** *****
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 ***

[Ian Hinson]

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" <sl*****@verizo n.net> wrote in message
news:vn******** *******@news.us west.net...

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.score ID, tblScores.dogre gnbr, tblScores.trial classID,
tblScores.score , tblScores.quali fied, tblScores.note,
tblScores.proce ssed, tblScores.proce sseddt, tblScores.level ,
tblTrialClass.j udgeID
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.event ID =
tblTrials.event ID) INNER JOIN ((tblClasses INNER JOIN tblTrialClass ON
tblClasses.clas sID = tblTrialClass.c lassID) INNER JOIN (tblPeople INNER
JOIN (tblDogs INNER JOIN tblScores ON tblDogs.dogregn br =
tblScores.dogre gnbr) ON tblPeople.peopl eID = tblDogs.peopleI D) ON
tblTrialClass.t rialclassID = tblScores.trial classID) ON
tblTrials.trial ID = tblTrialClass.t rialID
ORDER BY tblScores.dogre gnbr, tblTrials.trial dt, tblEvents.event name,
tblTrials.trial nbr, tblClasses.clas s;

Bound combobox in Form Header Section: cboDogInfo
cboDogInfo ControlSource: dogregnbr (tblScores)
cboDogInfo RowSource: SELECT tblDogs.dogregn br, [tblDogs].[formalname] &
" [" & [tblDogs].[callname] & "] " & "(" & [tblPeople].[fname] & " " &
[tblPeople].[midinit] & " " & [tblPeople].[lname] & " " &
[tblPeople].[suffix] & ")" AS doginfo
FROM tblPeople INNER JOIN tblDogs ON tblPeople.peopl eID =
tblDogs.peopleI D
WHERE (((tblDogs.remo ved)=False))
ORDER BY tblDogs.formaln ame;

Fields in each row/record:
Bound combobbox: cboEventInfo
cboEventInfo ControlSource: trialclassID (tblScores)
cboEventInfo RowSource: SELECT tblTrialClass.t rialclassID, " (" &
[tblTrials].[trialdt] & ") * " & [tblEvents].[eventname] & " * Trial #"
& [tblTrials].[trialnbr] & " * " & UCase([tblClasses].[class]) & " * " &
[tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] AS eventinfo
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.event ID =
tblTrials.event ID) INNER JOIN (tblClasses INNER JOIN tblTrialClass ON
tblClasses.clas sID = tblTrialClass.c lassID) ON tblTrials.trial ID =
tblTrialClass.t rialID
ORDER BY tblTrials.trial dt, tblEvents.event name, tblTrials.trial nbr,
tblClasses.clas s;

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.judge ID, "Judge: " &
[tblPeople].[fname] & " " & [tblPeople].[midinit] & " " &
[tblPeople].[lname] & " " & [tblPeople].[suffix] AS judgeinfo
FROM tblPeople INNER JOIN tblJudges ON tblPeople.peopl eID =
tblJudges.peopl eID;
*************** *************** *************** *****
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 ***

[Susan Bricker]

Ian,

Thank you, so much for your reply. I wonder if I am understanding your
solution. By making a new main form that is unbound with cboDogInfo and
cboEventInfo unbound controls on the main form, and making frmDogScores
a subform on the main form, the only scrollable information left on the
subform will be 'score' and 'level'. That means that the scores for a
single dog at a single trial will be displayed. That means that for the
user to view additional scores for the same dog at other trials, they
will need to hit a navigational control or a command button to move
through all trials that the dog attended.

That's doable, but not what I had in mind. Bummers!!! I wanted to be
able to move through all the scores for a particular dog by scrolling up
and down (having - let's say around 15 scores viewable at a time), as is
possible with the continuous forms design.

What I did last night after I posted the original request for help, was
removed the cboJudgeInfo from the detail row. That was what was causing
the problem. To get the judges name I needed to get judgeID. To get
judgeID I needed to get from trialclassID to find judgeID and then using
the peopleID (tblPeople) to get the name of the judge.

By the way, in the OnClick event of cboEventInfo, I test for NewRecord
and if true, I assign the dogregnbr (that I have sitting in a variable
that was populated upon entry to the frmDogScores form from the OpenArgs
variable into the form). This way when I click on a chosen row of
cboEventInfo, I have both foreign keys.

I added the judge "stuff" to the cumbersome query in the RowSource of
cboEventInfo and added judgename to the display. It works the way I
wanted, but it is UGLY to look at. Becauase 'eventname' and 'class'
values are variable in length, the data doesn't line up so it's hard to
look at when there are many rows on the screen.

I am going to see if I can force the width of these pieces of
information inside the combobox display (inside the query) to be
constant so that the information lines up making it look like columns.

Of course, if you have another suggestion, feel free to let me know.

Again, thanks for the explanation and suggestion. I really appreciate
it.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***