Problem with sychronised combo boxes 1

[BarryMurphy]

I have a problem with a form in Access 2000. The purpose of the form is to capture two parameters, which are inserted into a query that is the RecordSource of a report. The form contains two synchronised combo boxes. A SELECT query populates Combo1 from which the user selects Parameter1, i.e. a value of a field. The query that is the RecordSource of Combo2 uses the value selected in Combo1 to filter the values of another field displayed in Combo2, from which Parameter2 is selected. The combo boxes are set up as follows:

Combo1

RecordSource: Query1
SELECT Table1.Field1, Table1.Parameter1
FROM Table1
ORDER BY Table1.Parameter1;

Column Count: 2
Column Widths: 0cm;2.54cm
Bound Column: 1

Combo2

RecordSource: Query2
SELECT TABLE2.Field1, Table2.Parameter2
FROM Table2
WHERE (((Table2.Field1)=IIf(IsNull([Forms]![Form1]![Combo1]),[Field1],[Forms]![Form1]![Combo1])))
ORDER BY Table2.Parameter2;

Column Count: 2
Column Widths: 0cm;2.54cm
Bound Column: 2

The form has the following code:

Private Sub Combo1_AfterUpdate()
    Me!Combo2.Requery
    Me!Combo2.SetFocus
End Sub

The IIf function within the Field1 criterion in Query2 tests to see if Combo1 is null. If the combo is null, the query uses all the values of Field1 within Table2 as criteria, returning all values of Parameter2 in Combo2. If the control is not null, the Field1 associated with the value of Parameter1 displayed on the form becomes the criterion for the query and returns the related Parameter2's.
The problem is that when Parameter1 is not selected, all occurrances of Parameter2 in Table2 are displayed in Combo2's list, rather than distinct values, which is what I want. I tried using a query that returns distinct values of Parameter2 as the first argument of the IIf function but received an error: "At most one record can be returned by this subquery. (Error 3354)"

 

[sko]

I wonder if a group by table2.parameter2 would work.

 

[BarryMurphy]

Thanks for your reply, sko. You suggest I try a GROUP BY Table2.Parameter2.
Is this what you mean?
SELECT [CLASS].[Course_Code], [CLASS].[Year]
FROM CLASS
WHERE ((([CLASS].[Course_Code])=IIf(IsNull([Forms]![frmSelector]![cboCourseSelect]),[Course_Code],[Forms]![frmSelector]![cboCourseSelect])))
GROUP BY [CLASS].[Year], [CLASS].[Course_Code]
ORDER BY [CLASS].[Year] DESC;

The result of this query is the same as that of the query with just an ORDER BY clause.