HI,
I have a report that can sort and group in various ways, and I need to put the group heading onto the report as it runs.
I am setting the grouping levels in the report open event and this is giving me the data I want.
I have tried to set the group title using an unbound text box and setting the control source with VBA.
I have tried putting the code in the on open event of the form, the load event, and the format and print events of the section.
None of these work, I either get a message saying the control source can't be assigned during print preview, or that I am assigning a value that doesn't exist depending where I am running the code from.
I can manage to assign a string for the label (using a text box), but if I try to assign the variable in the same place it tells me it doesn't recognise the variable.
I have also tried putting the following code in the control source of the unbound field on the report.
="[" & DLookUp("reportfieldname","tblreportsortorder","reportfieldname = Forms![frmSalesReportSelector]![CboLevel1]") & "]"
Again this works for the label, but in the field where I want the relevant name for that record, I get the name of the field not the value in it.
Can anyone help with either of these approaches, or suggest another way of achieving what I want, the only suggestions I have seen so far seem to be for pivot tables which is not what I am trying to do.
If I don't solve this I am going to have to use hidden fields, and make the correct one visible, very messy!
Thanks in anticipation,
Sue
I have a report that can sort and group in various ways, and I need to put the group heading onto the report as it runs.
I am setting the grouping levels in the report open event and this is giving me the data I want.
I have tried to set the group title using an unbound text box and setting the control source with VBA.
I have tried putting the code in the on open event of the form, the load event, and the format and print events of the section.
None of these work, I either get a message saying the control source can't be assigned during print preview, or that I am assigning a value that doesn't exist depending where I am running the code from.
I can manage to assign a string for the label (using a text box), but if I try to assign the variable in the same place it tells me it doesn't recognise the variable.
I have also tried putting the following code in the control source of the unbound field on the report.
="[" & DLookUp("reportfieldname","tblreportsortorder","reportfieldname = Forms![frmSalesReportSelector]![CboLevel1]") & "]"
Again this works for the label, but in the field where I want the relevant name for that record, I get the name of the field not the value in it.
Can anyone help with either of these approaches, or suggest another way of achieving what I want, the only suggestions I have seen so far seem to be for pivot tables which is not what I am trying to do.
If I don't solve this I am going to have to use hidden fields, and make the correct one visible, very messy!
Thanks in anticipation,
Sue
