Distinct items from recordset
in [Programming]
|
Prev: Unable to change "Enabled" state of an Excel 2007 ribbon galle
Next: Macro to copy a VLOOKUP formula - Data with a filter on
From: Bob Phillips on 23 Mar 2010 07:56 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" <igorm(a)live.com> wrote in message news:ECB53461-53DF-4F73-A40F-91E69F7DA8D3(a)microsoft.com... > 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" <bob.phillips(a)somewhere.com> napisa� w > wiadomo�ci grup dyskusyjnych:OTgcbrmyKHA.5936(a)TK2MSFTNGP04.phx.gbl... >> Igor, >> >> Why don't you make the query return a distinct set? >> >> -- >> >> HTH >> >> Bob >> >> "IgorM" <igorm(a)live.com> wrote in message >> news:DC376AB8-6964-4F36-9E1B-92A97582C07E(a)microsoft.com... >>> 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 >> >>
From: IgorM on 23 Mar 2010 09:13 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" <bob.phillips(a)somewhere.com> napisa� w wiadomo�ci grup dyskusyjnych:OqYuU$nyKHA.2644(a)TK2MSFTNGP04.phx.gbl... > 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" <igorm(a)live.com> wrote in message > news:ECB53461-53DF-4F73-A40F-91E69F7DA8D3(a)microsoft.com... >> 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" <bob.phillips(a)somewhere.com> napisa� w >> wiadomo�ci grup dyskusyjnych:OTgcbrmyKHA.5936(a)TK2MSFTNGP04.phx.gbl... >>> Igor, >>> >>> Why don't you make the query return a distinct set? >>> >>> -- >>> >>> HTH >>> >>> Bob >>> >>> "IgorM" <igorm(a)live.com> wrote in message >>> news:DC376AB8-6964-4F36-9E1B-92A97582C07E(a)microsoft.com... >>>> 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 >>> >>> > >
From: Bob Phillips on 23 Mar 2010 10:32
I presume that this is a cascading set of controls, pick a country and it shows those provinces, pick a province and it shows those cities, and so on? If so, you will have to process it somewhere, and I would do it in VBA and build a set of associated ranges and populate the controls as and when needed. Shouldn't be that hard. -- HTH Bob "IgorM" <igorm(a)live.com> wrote in message news:82817CDB-6F22-4FE7-854C-69F1B3DF4BF6(a)microsoft.com... > 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" <bob.phillips(a)somewhere.com> napisa� w > wiadomo�ci grup dyskusyjnych:OqYuU$nyKHA.2644(a)TK2MSFTNGP04.phx.gbl... >> 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" <igorm(a)live.com> wrote in message >> news:ECB53461-53DF-4F73-A40F-91E69F7DA8D3(a)microsoft.com... >>> 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" <bob.phillips(a)somewhere.com> napisa� w >>> wiadomo�ci grup dyskusyjnych:OTgcbrmyKHA.5936(a)TK2MSFTNGP04.phx.gbl... >>>> Igor, >>>> >>>> Why don't you make the query return a distinct set? >>>> >>>> -- >>>> >>>> HTH >>>> >>>> Bob >>>> >>>> "IgorM" <igorm(a)live.com> wrote in message >>>> news:DC376AB8-6964-4F36-9E1B-92A97582C07E(a)microsoft.com... >>>>> 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 >>>> >>>> >> >> |