Display only Selected Fields in a Report

[Ian Stow]

In a report called COSTS (that runs from a Query called COSTS I have 4 fields - CLIENTCOST,DRVCOST,SUBSCOST & COMMCOST. Is there a way ,when I run the report, that I can have displayed a box? that allows me to specify which fields I want printed.

If there is, can the report (say in the report header) print something like the following

REPORT SHOWING THE FOLLOWING COSTS - CLIENTCOST - DRVCOST - SUBSCOST - COMMCOST (but obviously only print the fields that I have selected previously)

Thanks

A very Green Access User

 

[simongallop]

You can have code on the OnOpen event of the report which opens a form. On that form you have 4 check boxes, a cancel button and an OK button. When you click OK, code will then look to see which box is selected and will then set the visible property of the textboxes on the report to True or False. It can then create the header that you want.

The only draw back to this system is that on the report you have 4 fields in the details section so if the user selects only 1 & 2 then the report will be weighted to the left and if they select 1 & 4 then there will be a big gap between the two fields. As an advanced idea, depending on the number of fields selected will determine which of the 4 textboxes in the Details section get used. Ie if 2 fields selected then you could code it so that the information is placed in the middle 2 boxes, cutting out the gaps.

HTH

 

[Ian Stow]

Thanks for the route to go down

I made a form up and then went to the 'On open' and it asks me on the event proceedure whether I want to build an expression, macro or visual basic. I presume I use VB but having never used it before I do not know where to start. Can any one recommend any book I could buy (it would have to be for greenhorns like me)

 

[simongallop]

OK. Simple way is by opening a form and clicking OK which will display the report.

1/ Create a form with 4 checkboxes. Call them chk1, chk2, chk3,chk4
2/ Create a button which has a label of "Launch Report" or "OK"
3/ In the OnClick event of the button past this code

Docmd.OpenReport "Cost_Rep", acViewPreview

4/ Save your form as Rep_Sel

5/ Create your report with 4 textboxes in the Details section. Call them txt1, txt2, txt3, txt4 and set the control values to the 4 fields in your query (as you would for a normal report)

6/ In the OnOpen event of the report paste this code:

If Forms![Rep_Sel]!chk1.Value = 0 Then
Me.txt1.Visible = False
Else
Me.txt1.Visible = True
End If

and repeat the code for each control ie chk2.Value = 0 then me.txt2 etc.

7/ Save your report as Cost_Rep

Naturaly you can change the names but make sure that you change them in the code as well
HTH

 

[Ian Stow]

Thanks very much Harry

Much Appreciated

Ian

 

[sarkar]

You can have code on the OnOpen event of the report which opens a form. On that form you have 4 check boxes, a cancel button and an OK button. When you click OK, code will then look to see which box is selected and will then set the visible property of the textboxes on the report to True or False. It can then create the header that you want.

The only draw back to this system is that on the report you have 4 fields in the details section so if the user selects only 1 & 2 then the report will be weighted to the left and if they select 1 & 4 then there will be a big gap between the two fields. As an advanced idea, depending on the number of fields selected will determine which of the 4 textboxes in the Details section get used. Ie if 2 fields selected then you could code it so that the information is placed in the middle 2 boxes, cutting out the gaps.

HTH

Hii, Kindly please assist me with the VBA code for cutting the gaps between the fields in the report. Thanks.