Query and Report Puzzler

[Simon Harris]

Hi All,

I have a form which has a list box on it, which is currently driven from a
query. The query has various criteria set against the columns, which enables
my user to run a search based on what they input into text boxes on my form.

Now, the user wants to be able to order the listbox results by various
columns (e.g. Order by surname/postcode etc, both ASC and DESC

Also, they want a button which produces a report based on what they see in
the list box - So general idea is they run there search, order the results,
then click the button to print the results out.

Problem is this:

How do I change the order of the query using some sort of form input? Is it
possible to access the properties of a query programically, using VBA code
(e.g. Can i say queries.queryname.sortcolumn=xyz?? (You see what Im getting
at??)

Dont forget I also need to run a report based on what ever data is driving
the list box.

Thanks lots,

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!

 

[John Vinson]

How do I change the order of the query using some sort of form input? Is it
possible to access the properties of a query programically, using VBA code
(e.g. Can i say queries.queryname.sortcolumn=xyz?? (You see what Im getting
at??)

I'd suggest actually building the entire SQL string for the query in
VBA code in a string variable, adding the Order By clause as
appropriate. To get started, create a typical query in the query
designer window and view it in SQL view - use this as your model for
building the string.

Dont forget I also need to run a report based on what ever data is driving
the list box.

Simply set the Report's Recordsource property to the generated string.

 

[Simon Harris]

Many Thanks for your reply John, to actually set the recordsource
progmatically, is it simply a case of...

Object.RecordSource = "Select * from Foo order by " & MyVar

Thanks again,

Simon.