Need combo box to return distinct values

[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 if Combo1 is null, 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)"

 

[JerryDennison]

There is an easier way to accomplish this. Simply set the criteria of combobox 2's field to the value of combobox 1:

Like Forms!Formname!Combobox1 & "*"

By including the 'Like' and '*' you'll get all values whenever combobox1 is null and only those that match combobox1 when it isn't. This can be done without using code to actually construct the rowsource of combobox2 on the fly as you're currently doing. Simply requery combobox2 whenever it gets the focus.

 

[BarryMurphy]

Thanks very much for your reply, Jerry. You are no doubt right that there is an easier way to acieve what I am after than the method I'm using, but I haven't been able to employ your suggestiion to solve the problem. By "set the criteria of combobox 2's field to the value of combobox 1" I presume you mean the query used as the RecordSource for Combo2. I changed the criterion as follows:

Combo2 RecordSource: Query2
SELECT TABLE2.Field1, Table2.Parameter2
FROM Table2
WHERE (((Table2.Field1) Like [Forms]![Form1]![Combo1] & "*&quot)
ORDER BY Table2.Parameter2;

This returns identical results to my original query i.e. still doesn't filter out repeated values for Parameter2 when Combo1 is null. Still, it's good to learn a different approach to setting criteria, and I have a new angle to work on.

 

[JerryDennison]

Ah, then use DISTINCT or GROUP BY. In the rowsource query press the sum button and use the Group By total property.

 

[BarryMurphy]

DISTINCT doesn't work, I think because the primary key of Table2 is comprised of two fields I am referring to as Field1 (the primary key of Table1) and the Parameter2 field. With the DISTINCT predicate any unique combination of these two fields is returned, allowing repetition in the Parameter2 field.
Now, I hate to admit it, but I have no idea where the sum button is. I did select GROUP BY in the Total property as below, but when I click on Combo2, I get the following message: "You tried to execute a query that does not include the specified expression 'Year' as part of an aggregate function."

SELECT DISTINCT Table2.Field1, Table2.Parameter2
FROM Table2
WHERE (((Table2.Field1) Like [Forms]![Form1]![Combo1] & "*&quot)
GROUP BY Table2.Field1
ORDER BY Table2.Parameter2;

When I try GROUP BY Table2.Parameter2 I get the same error.

 

[BarryMurphy]

I solved the problem eventually by simplifying the whole thing somewhat. I changed the Row Source of Combo1 to a query:


SELECT DISTINCT Table2.Parameter2
FROM Table2
WHERE (((Table2.Field1)=IIf(IsNull([Forms]![Form1]![Combo2]),[Field1],[Forms]![Form1]![Combo2])))
ORDER BY Table2.Year;

and by changing Combo2's properties to:

Column Count; 1
Column Widths: 2.54cm

and also by reducing the code to:

Private Sub Form_Load()
Combo2.SetFocus
End Sub

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

Again, I appreciate your help very much. I may need some help in dealing with problems in creating the report, so you may see a new posting on that subject.