Using "DISTINCT" queries to populate combo-boxes (1 Viewer)

[mattthr]

Hi,
I have an annoying little problem I hope you can help me with. I want to populate a form combo-box with the contents of one field using the DISTINCT statement to get unique entries. The problem is that Access2000 seems hell-bent on including the primary key as part of the query. With it, the DISTINCT statement fails (becuase, of course, all the primary keys are different) but without it, although the statement will work as a query, it won't fill the combo box!

What's going on here and how can I work round it?

 

[Pat Hartman]

I know this doesn't answer your question but, if you use a data item to populate a combobox, you really should have a look up table that defines its values. Then you would use the lookup table as the rowsource for the combo box and you would not need to use the distinct keywork.

Distinct works differently in A2K than it did in previous versions although you seem to be running into a bug. A different way to get distinct values without using the distinct keyword is to create a totals query and use the Group by options.

Select fld1, fld2, fld3, fld4
From YourTable
Group by fld1, fld2, fld3;