Changing order by using code

[FRAZ]

I have a subform based on a query. I am attempting to sort on up to five different field, selected by controls on a pop-up form while it is the active form.
I have tried, the docmd.setorderby, me.orderby, which will not work while the pop up form has focus. However I can run a query doing the "docmd.openquery" method. But I do not know how to run a select query from code, I can build the "order by " clause. but I don't understand how to change the clause.
I've searched the forum and examples of select queries just show "Select.......ect.ect" when I paste that in an event it gives me and error "expected case"



I not that good at SQL.
also any suggested reading on the subject would also be appreciated

[rpeare]

so let's say the record source for your form is "SELECT * FROM tblTest" Let's also say on your form you have a button named cmdResort.
Let's say on tblTest you have three fields Field1, Field2, Field3

You want to be able to sort by any of those three fields in any order you want.
you would have to have a combo box or some other mechanism to say which FIELD you wanted to sort by first and whether you wanted it to be descending or ascending
so let's call these 6 controls
Sort1 SortType1
Sort2 SortType2
Sort3 SortType3

where sort 1 through 3 are your combo boxes with field names, your sorttype 1 through 3 is your ascending or descending

You'd have to have code something like this attached to your cmdResort:

dim sSQL as string

sSQL = "SELECT * FROM tblTEST"
if not isnull(sort1) then
    sSQL = & " ORDER BY [" & sort1 & "]"
    if sorttype1 = "DESCENDING" then
        sSQL = sSQL & " DESC"
    endif
endif

if not isnull(sort2) then
    sSQL = & ", [" & sort2 & "]"
    if sorttype2 = "DESCENDING" then
        sSQL = sSQL & " DESC"
    endif
endif

if not isnull(sort3) then
    sSQL = & ", [" & sort3 & "]"
    if sorttype3 = "DESCENDING" then
        sSQL = sSQL & " DESC"
    endif
endif

me.rowsource = ssql
me.requery

NOTE I didn't test this query but basically what you're doing is changing the rowsource of the form to be a new query with a new sort order and you're building the SQL statement that you can base it on. This also assumes that if you have a sort 3 you will have a sort 2 AND a sort 1, in other words you're preventing someone from having 'gaps' in the sorts.

[FRAZ]

I gave it a try, It works when the pop up form is based off the main form, how ever I have the following situation.
I have a form named main, on that form I have a tab control, each with a single subform. From one of the sub form I have a pop up (activated via a command button) which has the filtering controls for the sort routine. It does work when the pop-up is on the inital form, but not the subform. I tried the "forms!main![maintence list].recordset=ssql. (the form maintence list is on a tab control)
and me.recordset=ssql both give me errors. I need to leave the pop-up open for this. As I stated earlier, it works when I do "docmd.openquery" I think it may be the way I am referenceing the form.

[rpeare]

referencing a subform is different that referencing a the main form.

So your 'routine' is this:

1. Click a button on your main form
2. Set up the sorting mechanism you want for your SUBFORM in a separate form (for instance frmSortSetup)
3. Apply the sorting indicated in the form frmSortSetup to a specific subform on you main form

My first question is this

when you debug.print the SQL statement you're generating that you want to pass to your subform, have you cut and pasted that into a query window to see if it's giving you the results you want?

If it is (I haven't ever done this before and do not have an example in front of me so unless you want to provide a sample of your database I'm shooting in the dark here) you can adapt the code here:

http://www.techrepublic.com/blog/mic...ccess-subform/

note I'm not sure I have the correct syntax for referencing the subform control because I don't use forms/subforms but you should be able to find what you need.

[FRAZ]

Thanks; got it work out, I went ahead and used a listbox instead of a continious form. What I was trying to accomplish was to replicate the drop down filter, that available in the data sheet view, Which I did manage to accomplish using a sub form as the drop down list, which I could place check boxes, and a few queries, thanks for you input.