Summary: the user is able to control (include or exclude in the output) 35 checkbox / fields in the query. Currently output is going to an excel sheet. Goal create a dynamic changing report.


Dear All,
This is a bit complicated but I hope that you would be able to help me out.
I have created a query off a linked table (from AS/400). This query has several fields that are controlled from a form using checkboxs, where if the check box is checked then the field will show up in the sql statement if not then then sql will run without that field. Currently I'm having the spreadsheet output to an excel sheet.
But what I'm looking (which is my optimum goal) to have a report based on that query that would dynamically include whatever fields are checked and remove whatever not checked.
Now I thought about this and I'm thinking of creating a report based on the query that would include ALL the fields then I insert some code that should be run- when the report is activated or being populated - that would go and check all the check boxes then what ever is not checked then the code will make the attribute of that field in the report of Visible as false thus removing it from the report...
Is my solution possible? is there such a code that would be created? I would appreciate if you can help me out with this...

Any idea/opinion is greatly appreciated...
Thank you,
Ahmad.

It looks to me that what you are asking for, while inconsistent (is this an Excel spreadsheet or an Access report?) can be done with the Access report.

Create a module, and then within the module, create 35 public variables As Boolean:

Public bCheck1 As Boolean
Public bCheck2 As Boolean
... etc


Then on the form that launches the report, set the value of the boolean based on the check box state:

If Me.Check1.Value = True Then
   bCheck1 = True
Else
   bCheck1 = False
End If

If Me.Check2.Value = True Then
   bCheck2 = True
Else
   bCheck2 = False
End If
... etc

Then on the On Format event of the Report's Detail section, do this:

Me.Column1.Visible = bCheck1
Me.Column2.Visible = bCheck2
... etc

This will set the Visible property of each column to either True or False, depending on whether the checkbox was checked or not.

If you wanted to be more rigorous, you could make sure that the default value of each check box is set to false.

Did that help? Does that resolve all your issues, or do you still have the export to Excel issue?




mmcdonal

Look it up at: http://wrox.books24x7.com