Filter on a QUERY

[Bob Richardson]

I want my drop down list to include only the classes from the selected
event. Here's my query, but it's including every class from every event, not
just the classes in the selected event.
SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
[ClassCode];

I also tried to link the tables, (because there is a one to many
relationship from Events to Classes) but this didn't work either.

SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
Events.Event=Classes.Event ORDER BY Classes.ClassCode;

 

[John Vinson]

I want my drop down list to include only the classes from the selected
event. Here's my query, but it's including every class from every event, not
just the classes in the selected event.
SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
[ClassCode];

Well, that's not working because Classes.Event=Event is comparing the
Event field from Classes to itself - there's no other table, so Event
by itself simply refers to the Event field in this table. This
obviously is always true, since the value must be equal to itself.

I also tried to link the tables, (because there is a one to many
relationship from Events to Classes) but this didn't work either.

SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
Events.Event=Classes.Event ORDER BY Classes.ClassCode;

What constitutes the *SELECTED* event? Is this on a Form? If so, you
can use the Form control containing the Event as a criterion:

SELECT Classes.ClassCode FROM Classes
WHERE Classes.Event = Forms!YourForm!Event
ORDER BY ClassCode;

You'll need to Requery the combo in the AfterUpdate event of the
control whereby the user selects or defines the Event.

John W. Vinson[MVP]

 

[Van T. Dinh]

You need the full reference to the Ontrol that has the Event selected. In
your SQL, Event will be the same as Class.Event so it is always True.

It sounds like you have a set of Cascaded ComboBoxes. If this is the case,
see The Access Web article:

http://www.mvps.org/access/forms/frm0028.htm

 

[Bob Richardson]

Your SELECT worked perfectly. There is a form (key = event) and a subform
(key=event/class)

The idea is to have the appropriate class subform appear when the user
clicks the desired class on the drop-down list. How would that onclick event
look. I'm most concerned because the sub-form record has two fields in its
key...event and class.

I want my drop down list to include only the classes from the selected
event. Here's my query, but it's including every class from every event,
not
just the classes in the selected event.
SELECT Classes.ClassCode FROM Classes WHERE Classes.Event=Event ORDER BY
[ClassCode];

Well, that's not working because Classes.Event=Event is comparing the
Event field from Classes to itself - there's no other table, so Event
by itself simply refers to the Event field in this table. This
obviously is always true, since the value must be equal to itself.

I also tried to link the tables, (because there is a one to many
relationship from Events to Classes) but this didn't work either.

SELECT Classes.ClassCode FROM Events INNER JOIN Classes ON
Events.Event=Classes.Event ORDER BY Classes.ClassCode;

What constitutes the *SELECTED* event? Is this on a Form? If so, you
can use the Form control containing the Event as a criterion:

SELECT Classes.ClassCode FROM Classes
WHERE Classes.Event = Forms!YourForm!Event
ORDER BY ClassCode;

You'll need to Requery the combo in the AfterUpdate event of the
control whereby the user selects or defines the Event.

John W. Vinson[MVP]

 

[John Vinson]

Your SELECT worked perfectly. There is a form (key = event) and a subform
(key=event/class)

The idea is to have the appropriate class subform appear when the user
clicks the desired class on the drop-down list. How would that onclick event
look. I'm most concerned because the sub-form record has two fields in its
key...event and class.

One way (if I understand you correctly) would be to have the subform's
Master Link Fields property be the two controls on the mainform
containing Event and Class, separated by semicolons; and the Child
Link Field be

Event;Class

This will automatically display only the records pertaining to that
combination.

John W. Vinson[MVP]