sort order in report using a query by form

[Davros]

hi
sounds simple enough but i can't get it to work. the reference is spot on - i use the expression builder actaully in the sort area of the report to build the expression. it i actually select the field name in the sort area it works - but i want the report order to be dynamic and sent from a QBF. when i try this it does not work.
the syntax is correct, the spelling is correct, it works when i select the field name in the report but it does not work when i try to send the field name to to report by using a QBF

the expression i'm using to send the field name by QBF to the report is this. so if the sort in the report was by static field name it would be

inspection_date

however the text in comorder1 is 'inspection_date'

=[Forms]![frm_query_all_inspections_combined]![comorder1]

any ideas anyone

 

[boblarson]

You need VBA for this.

Me.OrderBy = Forms!frm_query_all_inspections_combined.comorder1
Me.OrderByOn = True

 

[Davros]

thank Bob Larson
you're a star!!

 

[Davros]

actually it's still not working
i've cut the code down to its basic component and its still not working.
the code i'm using in the on load event of the report is

Me.OrderBy = Forms!frm_query_all_inspections_combined.comorder1
Me.OrderByOn = True

i can't see anything wrong with this yet the report still defaults to sorting by 'Inspection ID' and not by what is in 'comorder1' of my QBF. the sorting by 'inspection ID' comes from the 'group on inspection ID from smallest to largest.

could the problem be that i have two group headings within the report? i'm saying this because if i put a sort (bottom of the report design in the 'group, sort and total area') above the group headings and choose a field presented to me by the report design, it works. this is the only time the sort works. i've treid an expression in the same place but i can't get it to work. it seems really strange.
any more advice

 

[Davros]

problem solved.
i needed to use the grouplevel command. the code above now becomes

Me.GroupLevel(0).ControlSource = Forms!frm_query_all_inspections_combined!comorder1
Me.GroupLevel(0).SortOrder = False

which is
sort in ascending order based on the value in comorder1
another sort field in the same group would be grouplevel(1)

hope this helps someone

thanks anyway Bob