filter and/or sort a combos "RowSource"

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

Bill Stanton

How do you filter and/or sort the "RowSource"
query for a combo box from code? (I realize I could
write a function that effectively modifies the query to
sort and filter as desired, but I have the idea it should
be simpler?)

Thanks,
Bill
 
I let/hope so other show some ideas here, but what I do in those cases where
you need to modify the RowSouce, is to simply stuff the sql right into the
control.

As for sorting, well, you just have to set the order in the sql. Even for
your existing combos, you can always invoke the query builder while in
design mode on the control. You then just simply set the "ascending" for
whatever field you want to sort by.


Anyway, you can for ease of work/time still build the combo with the wizard,
and then "steal" the sql. Anyway, to make a combo box of people in my city,
I would use:

dim strSql as string

strSql = "select CompanyName from tblCustomers " & _
"where City = 'Edmonton' order By CompanyName"

me.MyListBox.RowSouce = strSql

That all you need.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
\
 
Thanks Albert, I'll do as you suggest.

After the Requery, how does one pre-select the
first record of the RowSource? (I want to find out
what the 1st record of the RowSource contains.)

Bill
 
If it is a bound form, then usually you can just stuff the default value
into the control.

In fact, often just stuffing a value into the control will do the trick.

For listbox, I often use the following to highlight the first entry:

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

At any rate, you can always grab the first value from a combo like:

me.cboWhatSearch.Column(0,1)

The column is:
..Column(Thecollum, TherowNumber)

You also have to use caution, as if you have "display headings" as yes, then
the row number starts at 1, but other wise, it starts at zero.
 
Albert, looks like I'm on a role here... I stuffed
the SQL directly into the RowSource for the combo
in such a way that I can easily alter the string appendage
between descending and ascending sorts depending on
another switch. I'd never had an occasion to pre-examine
a specific row in a combo, so I'd not noticed that the
"Column" syntax included notation to specify the row
as well as the column.

As always, thanks for your thorough replies to my questions.

Bill Stanton
Graeagle, CA
 
Back
Top