Sorting a Query by different columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

greetings to all

I have an unbound combo box called cbxRegion with a fixed list of 5 records.
The first column has width 0.

A Central
B North
C East
D South
E West

The query underlying my form is [qRegionSales] with fields RS_ID, A_reg,
B_reg, C_reg .... When I make a selection from the combo box, I want to
change the query in the following way. If, for example, B is chosen, I want
to sort by field B_reg Ascending, with all other sorts removed. If C is
selected, then sort by C_reg Ascending, again removing all other sorts. And
so on...

Could one of you readers point me in the right direction. I've tried a
number of things with no luck yet.

TQ
 
Hi Victoria,

In the after update event of your combo box, have the following code:

dim strQuery as string

select case me.cbxRegion
case "A"
strQuery = "select * from qRegionSales order by A_Reg"

case "B"
strQuery = "select * from qRegionSales order by B_Reg"

case else
' put this one after you have done one for each option
msgbox "Unable to handle that option"
end select

me.recordsource = strQuery
me.requery

This will cause your form to be requeried with the records sorted as you
wish. If you want to remain on the currently viewed record you will need to
record its ID and move to it after the requery.

Hope this helps.

Damian.
 
Looks good. Thanks

Damian S said:
Hi Victoria,

In the after update event of your combo box, have the following code:

dim strQuery as string

select case me.cbxRegion
case "A"
strQuery = "select * from qRegionSales order by A_Reg"

case "B"
strQuery = "select * from qRegionSales order by B_Reg"

case else
' put this one after you have done one for each option
msgbox "Unable to handle that option"
end select

me.recordsource = strQuery
me.requery

This will cause your form to be requeried with the records sorted as you
wish. If you want to remain on the currently viewed record you will need to
record its ID and move to it after the requery.

Hope this helps.

Damian.

Victoria said:
greetings to all

I have an unbound combo box called cbxRegion with a fixed list of 5 records.
The first column has width 0.

A Central
B North
C East
D South
E West

The query underlying my form is [qRegionSales] with fields RS_ID, A_reg,
B_reg, C_reg .... When I make a selection from the combo box, I want to
change the query in the following way. If, for example, B is chosen, I want
to sort by field B_reg Ascending, with all other sorts removed. If C is
selected, then sort by C_reg Ascending, again removing all other sorts. And
so on...

Could one of you readers point me in the right direction. I've tried a
number of things with no luck yet.

TQ
Click to expand...
Click to expand...
 
Back
Top