Creating dynamic report in Access 2003

[ngohil]

Good day Access Gurus!

I am required to create a report that displays all records between certain start and end dates. At the same time, the report should also display columns based on checkbox selections made on the form.

The "Report Parameter Selection" form has start date and end date textboxes and these are mentioned as restrictions in a query. This query in turn runs the resultant report. Now, the "Report Parameter Selection" form has 25 checkboxes which - if selected - should display as a column on the report. My logic is that all columns on the report be set to Visible=No to start with. When the report loads or opens, the code should verify whether the checkboxes on the form are selected or not, and if they are selected, then the visible property be set to Visible=Yes or Visible=True.

I have tried to enter a condition in the Report_Activate event of the report such as:

If Forms!frm_EnterDates!chkfl.Enabled = False Then

Reports!rpt_Training!fl.Visible = False
Reports!rpt_Training!fl_Label.Visible = False

ElseIf Forms!frm_EnterDates!chkfl.Enabled = True Then

Reports!rpt_Training!fl.Visible = True
Reports!rpt_Training!fl_Label.Visible = True

End If

The above code does not respond and the fl and fl_Label objects appear on the report regardless.

Is there a way to achieve the above so that when a checkbox or a group of checkboxes is selected on a form, the resultant report displays the selections as a column?

I am using Access 2003 SP1. Many thanks in advance.

 

[vbaInet]

Welcome to the forum ngohil

It's easy to hide the controls but the main hurdle is re-arranging the controls, i.e. moving them left (if necessary) in order to fill up the gaps. How do you intend handling this?

Have you considered just using a query instead?

 

[ngohil]

Hi vbaInet,

My hope was to find a control that can enable the re-arranging of the query columns. Is there something within MS Access that allow you to do this?

Thanks.

 

[vbaInet]

Nope! It can only be done in code and a significant amount of good logic too.

I think you will find some examples of this approach if you perform an advanced search here.