Changing a reports underlying query at runtime

  • Thread starter Thread starter Dave the wave
  • Start date Start date
D

Dave the wave

I am working on a project where I want to use the same report layout and
fields, I just want to change which field the report is sorted by. I'm
hoping I can use VBA to modify the SORT portion of the underlying query. If
not, is there a way to use VBA to change which query gets used when opening
a report? (I am opening the report from a switchboard. I would like to have
one button for this report, then have a dialog appear that allows for
selecting how the report is sorted.)

I would appreciate links to sites that would help me understand what
commands and program structure I need to use to make this happen.

Thanks!
 
Dave said:
I am working on a project where I want to use the same report layout and
fields, I just want to change which field the report is sorted by. I'm
hoping I can use VBA to modify the SORT portion of the underlying query. If
not, is there a way to use VBA to change which query gets used when opening
a report? (I am opening the report from a switchboard. I would like to have
one button for this report, then have a dialog appear that allows for
selecting how the report is sorted.)
Click to expand...


Using a query to sort the data for a report only works in
the most trivial reports. Reports are really sorted by
what's specified in their Sorting and Grouping window (View
menu).

If you have prespecified a sorting level in the report's
design, then you can use VBA code in the report's Open event
procedure to specify the field to use:

Me.GroupLevel(0).ControlSource = "fieldname"

If you want to have the report pop up a dialog for users to
enter the field name, then you could use the InputBox
function:

Dim strFieldName As String
strFieldName = InputBox("Enter Field Name")
Me.GroupLevel(0).ControlSource = strFieldName

Of course you would also have to add code to make sure the
user didn't enter an invalid name. All in all, you be
better off having a combo box on the switchboard so the user
can only select from a list of names that you allow. In
this case, the code would be:

Me.GroupLevel(0).ControlSource = Forms!theform.thecombo

If you really need to, you can also change the report's
RecordSource in the report's Open event:

Me.RecordSource = "nameofquery"
 
Back
Top