Why are some of my comboboxes getting populated and other are not?

[Susan Bricker]

Any idea why a combobox would not populate?

When the form is opened, it is opened with DOCMD.OPENFORM with selection
criteria to send only those records for a particular dog (dogregnbr).
The query for the rowsource of the combobox works when run from the
query design window.

combobox: cboTrialDt (trial date)
combobox control source: trialclassID (from tblDogTitles)
form record source: tblDogTitles

The query for the combobox rowsource is:

SELECT tblDogTitles.do gtitleID, tblTrials.trial dt
FROM tblTrials INNER JOIN (tblDogTitles INNER JOIN tblTrialClass ON
tblDogTitles.tr ialclassID = tblTrialClass.t rialclassID) ON
tblTrials.trial ID = tblTrialClass.t rialID;

tblDogTitles has the following fields:
dogtitleID - PK
dogregnbr - FK (key to record in tblDogs)
titleID - FK (key to record in tblTitles)
trialclassID - FK (key to record in tblTrialClass
and then some other fields not involved in this problem.

I have another combobox setup similarly that does get populated
properly. It's control source is titleID.

It's rowsource is:
SELECT tblDogTitles.do gtitleID, tblTitles.title
FROM tblTitles INNER JOIN tblDogTitles ON tblTitles.title ID =
tblDogTitles.ti tleID;

Thanks for your help.

Regards,
SueB

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

[Pachydermitis]

Sue,
The combos only fill if the data is in list. You have 2 inner joins in
the first sql statement, I'll bet one of the tables does not contain
the trialclassID you are looking for. You can use left or right joins
to see all the data from tblDogTitles.
HTH
Pachydermitis

[Susan Bricker]

Hey, Pachydermitis,

Thanks for your reply. I'm still a bit confused. That SQL statement
with the two INNER JOINS does work properly when I run the query while
in the Query Builder screen. It's when it's run within the form that is
doesn't work.

The bound column of the combobox (dogtitleID) and the control source of
the combobox (trialclassID) are both fields that are in the tblDogTitle
record, which is the recordsource of the form. So, I still don't get
it.

Any suggestions on how to get what I want - Trial Date (which is a piece
of data located in the tblTrials table record)?

Regards,
SueB

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

[Pachydermitis]

I probably misunderstood your question. :)
Let's try again - maybe you can clarify a couple things so I can get it
right.
1. Your combo has a source of trialclassID, but your sql would be
placing dogtitleID in it. possible problem?
2. You said the combo doesn't work. Do you get anything in the list,
or is it just not highlighting the one it should
3. You said you want the Trial Date. Do you want the date or the
dogtitleID that goes with the date?
If you ever want to get a single piece of data from a field,
dlookup(field,t able,criteria) can be a life saver.

Pachydermitis

[Susan Bricker]

Ok ... I'll try to explain.

I want to display a list (continuous forms) of titles that a single dog
has earned while competing in Obedience competitions. I want to display
the following information on each detail line (in the form):

title (for example: "Companion Dog")
title_abbreviat ion (for example: "CD")
date_title_was_ earned
event_where_tit le_was_earned
host_club_name
trial_number (an event can have more than 1 trial)

Every time a title is awarded to a dog (determined by a procedure that
checks all scores earned by dogs) a new record is written to the
"tblDogTitl es" table.

The "date_title_was _earned" is a piece of data in the "tblTrials" table
record. The "tblDogTitl es" record has the key to the "tblTrialCl ass"
record and the "tblTrialCl ass" record has a key to the "tblTrials"
record.

A dog competes in a Class that is in a Trial that is in an Event. So, I
need to use the "trialclass ID" that is saved in the "tblDogTitl es"
record to find the record in "tblTrialCl ass" table and then use the
"trialID" in that record to get me to the "tblTrials" table record that
has the date.

Now, the fun part ... I want to display ALL titles (for the selected
dog) on the form in a 'continuous records' format, not a single record
at a time. I could do the single record at a time design and use
unbound fields and populate the unbound comboboxes easily. But I want
each record to populate and be displayed all at the same time
(visually).

Now to get the Eventname and HostClubName I need to do similar
comboboxes. So, if I can get the date working, I'm sure that I can get
the rest of it to work.

Now, if this is as clear as mud and you want to take a look at the
"solution", I would be willing to zip the mdb file and send it to you to
look at. Just let me know. Thanks.

Regards,
SueB

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

[Susan Bricker]

What a surprise ... I got it to work. I changed the ControlSource of
all those comboboxes to [dogtitleID] (the key of the dogtitle record)
and whadyaknow ... the comboboxes populate properly. Thanks for trying
to help. I really appreciate it.

Regards,
SueB

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