Ordering a query from a form

[Simon Harris]

Hi All,

I have a form which has a list box, driven by a query. I want to add a combo
to my form which will set the sort criteria for the query, then requery the
listbox.

My idea is:

- Create a combo for user to select what column to sort by
- Create a combo for user to select how to sort (Asc/Desc)
- Create a text box (hidden) for each column that I want to sort by
- Query columns has the value of the corrosponding text box in the sort area
of the query (e.g. [Forms]![FRM_switchboard]![CustomersSortByCombo])
- Onchange event of combo(s) sets the value of the textboxes accordingly -
e.g: User selects 'surname' and 'ascending' - All boxes apart from the
surname box are emptied, apart from the surname box which has the value
'ascending' set
- Listbox is then re-queried

Sounds Ok to me...but, when I enter the form values in the sort area of the
query, Access says I must choose one of the options.

Anyone got any ideas on how I can achieve this?

Thanks loads!

Simon ;-)

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Arvin Meyer]

The OrderBy clause in a query cannot take a parameter (sorting Ascending or
Descending). You have several choices that I can see:

1. Run the query in code, branching on the values in the OrderBy and sort
columns in the combo's AfterUpdate event.

2. Write multiple queries and choose the correct based on a branch of the
code in the combo's AfterUpdate event.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hi All,

I have a form which has a list box, driven by a query. I want to add a combo
to my form which will set the sort criteria for the query, then requery the
listbox.

My idea is:

- Create a combo for user to select what column to sort by
- Create a combo for user to select how to sort (Asc/Desc)
- Create a text box (hidden) for each column that I want to sort by
- Query columns has the value of the corrosponding text box in the sort area
of the query (e.g. [Forms]![FRM_switchboard]![CustomersSortByCombo])
- Onchange event of combo(s) sets the value of the textboxes accordingly -
e.g: User selects 'surname' and 'ascending' - All boxes apart from the
surname box are emptied, apart from the surname box which has the value
'ascending' set
- Listbox is then re-queried

Sounds Ok to me...but, when I enter the form values in the sort area of the
query, Access says I must choose one of the options.

Anyone got any ideas on how I can achieve this?

Thanks loads!

Simon ;-)

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Simon Harris]

Hi Arvin,

Many thanks for your reply.

When you say 'run the query in code' do you mean I drive the listbox from an
SQL statement in my VBA code, instead of driving it from an actual query,
which would result in a dynamic query, which I can change based on user
input?

Kind regards,

Simon.

The OrderBy clause in a query cannot take a parameter (sorting Ascending or
Descending). You have several choices that I can see:

1. Run the query in code, branching on the values in the OrderBy and sort
columns in the combo's AfterUpdate event.

2. Write multiple queries and choose the correct based on a branch of the
code in the combo's AfterUpdate event.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Hi All,

I have a form which has a list box, driven by a query. I want to add a combo
to my form which will set the sort criteria for the query, then requery the
listbox.

My idea is:

- Create a combo for user to select what column to sort by
- Create a combo for user to select how to sort (Asc/Desc)
- Create a text box (hidden) for each column that I want to sort by
- Query columns has the value of the corrosponding text box in the sort area
of the query (e.g. [Forms]![FRM_switchboard]![CustomersSortByCombo])
- Onchange event of combo(s) sets the value of the textboxes accordingly -
e.g: User selects 'surname' and 'ascending' - All boxes apart from the
surname box are emptied, apart from the surname box which has the value
'ascending' set
- Listbox is then re-queried

Sounds Ok to me...but, when I enter the form values in the sort area of the
query, Access says I must choose one of the options.

Anyone got any ideas on how I can achieve this?

Thanks loads!

Simon ;-)

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!

 

[Arvin Meyer]

Hi Arvin,

Many thanks for your reply.

When you say 'run the query in code' do you mean I drive the listbox from an
SQL statement in my VBA code, instead of driving it from an actual query,
which would result in a dynamic query, which I can change based on user
input?

Excatly.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Simon Harris]

Thanks again Arvin,

Can anyone point me to an example of this, or a tutorial?

Regards,

Simon.