User customizeable reports

[dan32539]

I'm using Access 2000 and need to allow users to be able to pick what items to show in a report. For example, if I have the following fields: LastName, FirstName, MI, Address, Phone#, SpouseName and the user only wants a report with LastName, Phone#. Does anyone know how to accomplish this?

 

[Guest_imported]

Quickie Answer, more fleshing out could be done:

Have the Report_Open event load a form (say, frmParam)

The form should have a checkbox for each field that can be displayed or not displayed, with the captions set to the fieldnames. All boxes checked by default. Maybe a button to clear/check all checkboxes to give your users quick starting places. Do not give a Cancel button (because the report is going to run anyway). If all boxes are cleared when OK is clicked, either let the report come up with everything invisible, or warn the user and set a "minimum set" of fields to visible.

Include all the data in your report.

In the ReportHeader_Format event, check the values of frmParam's checkbox controls, and set the Visible property for your field controls on the report accordingly:

'This report was designed with all columns visible.
'f is the prefix for boolean: your checkbox controls
Me.[txtField1].Visible = Forms![frmParam].fField1.Value
Me.[lblField1].Visible = Forms![frmParam].fField1.Value
Me.[txtField2].Visible = Forms![frmParam].fField2.Value
Me.[lblField2].Visible = Forms![frmParam].fField2.Value
'...do as many as you want...

You could do some fancy looping if you named controls appropriately and used some string manipulation, but this gives you the idea.

Now that just makes columns visible or invisible; it doesn't rearrange them. I don't know if you can change control's positions (Left and Top properties) in the ReportHeader_Format event, but it's worth trying! How much you would move them would depend on the space being vacated and probably involve math using the Width and Height properties as well.

Good luck!

>>>W=H=O=o=s=h=(((QuickieBoy