dynamic groupheader selection

[kchernak]

I'm using a form which the user can select various fields and various values which is then used to auto-generate a report. I'm using a report template that has a default grouping, but would like to be able to change it programatically. i.e. if the report default (groupheader0)is grouped by state, ascending sort, how can I change it to group by salesrep or product?
Thanks!

 

[PWise]

on your form create a combo box in the row source
salesrep;product;state;....

create the groupeheader and in the reportopen event

me.GroupLevel(0).ControlSource = forms!myform.combobox
Me.GroupLevel(0).SortOrder true 'false for decending

 

[kchernak]

Thanks Pwise - that works! now the problem is, I have a testbox (text79) in the header section with a formula in the control source that needs to be modified according to the grouping selected. i.e. if grouped on sales rep it should be:

=IIf([salesrep] Is Null,"SALESREP NOT ASSIGNED","SALESREP: " & [salesrep])

otherwise it should be:
=IIf([state] Is Null,"STATE NOT DETERMINED","STATE: " & [state])

I tried adding the following in the report On_Open:
Me.Text79 = =IIf([state] Is Null,"STATE NOT DETERMINED","STATE: " & [state])

but I get an error: "you can't assign a value to this object"

How do I assign a value to this text box?

Thanks!

 

[PWise]

select case me.GroupLevel(0).ControlSource
case salesrep
me.text79.controlsource = IIf([salesrep] Is Null,"SALESREP NOT ASSIGNED","SALESREP: " & [salesrep])
case state
me.text79.controlsource = IIf([state] Is Null,"STATE NOT DETERMINED","STATE: " & [state])

end select