Distinct items from recordset
Yes, you are right. But, for instance, if I run a query as this: SELECT
Country, Province, City, PostCode FROM tblPostCodes and I then, after
assigning the results to a recordset, I want to populate form controls (drop
down lists, one for data from each column) with appriopriate data I need to
get a list of unique countries from the recordset - I only want one name of
each country appear in a drop down list with countries. I could of course
write a separate query for each column but I don't want to do this for
performance reasons - I'd have than query a database for a new list of
provinces, cities, etc when user selects a different country in a country
drop down list.
For this I use:
Private Function GetProvinceList(ByRef mrsDataSet As ADODB.Recordset)
Dim mcolProvinceDistinctNames As New Collection
Dim mvCollectionItem As Variant
On Error Resume Next
Do
mcolProvinceDistinctNames.Add mrsDataSet![Province],
CStr(mrsDataSet![Province])
mrsDataSet.MoveNext
Loop Until Not mrsDataSet.EOF
On Error GoTo 0
But I thought there is a better way to do this.
Użytkownik "Bob Phillips" napisał w wiadomości
grup ...
Surely, you are already querying the database to get a recordset? What I
am saying is to change the query to only return distinct records.
--
HTH
Bob
"IgorM" wrote in message
...
Its probably me not being too specific. I don't want to query the
database to restrict connections to database.
I've been thinking about dumping values from a recordset to a collection
and using the values as a collection key too. But is there a better way
to do that - a buid-in unique filtering function.
Użytkownik "Bob Phillips" napisał w
wiadomości grup ...
Igor,
Why don't you make the query return a distinct set?
--
HTH
Bob
"IgorM" wrote in message
...
Hi
How to get distinct items from a recordset? Let's assume I have two
columns returned from a database: countries and cities.
I want to retrieve a list of unique country names.
Kind regards
IgorM
|