Selecting fields to show on report

  • Thread starter Thread starter Andrik via AccessMonster.com
  • Start date Start date
A

Andrik via AccessMonster.com

Im trying to create a form based on a query or table. Form will conttain
check boxes. Each check box represents a field to be showed on a report.
Im not very proficient with vb, and do not know how to do somthing like this.

PLEASE HELP
 
This will take some VBA. The basic setup is to go into your report in design
mode.
Then in the Format event of the detail section of the report you will need
to check to see if the check box for that control(what you are calling field)
is checked or not to determine whether to show it. To keep it simple for
now, put this in for each control on the report you want to use:

Me.MyControl Name.Visible = forms!MyFormName!MyCheckBoxName
 
Thanks for your reply
I wrote four lines of code "Me.SchoolName.Visible = forms!MyForm!
SchoolNameBox" for each of the controls on the report.
However, when i try to run a report it gives me mistate error.
The thing im not sure about, is the checkboxes on the form, they are unbound,
i dont understand if i have to bound them or not
 
This is what ive pu in the report

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.SchoolName.Visible = Forms.frmRunReport.chkSchoolname
Me.SchoolDistrict.Visible = Forms.frmRunReport.chkSchooldistrict
Me.Address.Visible = Forms.frmRunReport.chkAddress
Me.City.Visible = Forms.frmRunReport.chkCity
End Sub

I get a type mismatch error.
 
One thing I see, and it may not be the problem, but you are using a . where
there should be a !
Me.SchoolName.Visible = Forms!frmRunReport!chkSchoolname

Also, be sure your check boxes are not Triple State and therefore containing
Null.

As a test, try this version:
Me.SchoolName.Visible = IIf(forms!frmRunReport!chkSchoolname = True, True,
False)
 
Thanx alot, my problem was that my form has a control source of a query.

I am able to check off boxes of controls that i would like to see, if a box
is checked a field is visible, if not it is not
when the box is not visible how do i make a page header with a column name
to not show.

Thank you very much
 
Your column name is probably a label. You can use the same logic to make the
column label invisible as you do the data in the column.
 
got that, works like a charm
when i check only the School Name and District checkboxes they show on a
report.
the problem is that the columns where City and Address suppose to be are
empty, because i did not check off checkboxes for them to show on the report.
How do I make the visible fields to shift on a report
 
This is a little tricky, but not impossible. The location of a control on a
report is determined by its Left, Top, Width, and Height properties. For
what you want to do, you only need to change the Left property. Open your
report in design view, select the control you want to move, open the
properties dialog, and on the format tab, put your cursor in the Left
proprerty and press F1. It will give you the information on how to handle
this.
 
it works. the only problem im gettingis:
when I open the form with check boxes and just click preview report, an empty
report is supposed to show, instead i get a mismatch error:
Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.SchoolName.Visible = Forms.frmRunReport.chkSchoolname <--- Error on line
Me.SchoolName_Label.Visible = Forms.frmRunReport.chkSchoolname

I read something about auto update but cant completely comprehend how to
solve this problem
Your help is apreciated
 
Try changing it to:
IIf(Forms.frmRunReport.chkSchoolname = True, True, False)
Also, the line after the error may be in the wrong place. If the label is
not in the detail, it will be too late. You may need to move it to the page
header.
 
Ive tried what u have written but i still get an error "Run Time error 438,
Object doesn't support this property or method".
Here is the code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.SchoolName_Label.Visible = Forms.frmRunReport.chkSchoolname <<----- Yellow
error appears here
Me.SchoolName.Visible = Forms.frmRunReport.chkSchoolname

Me.SchoolDistrict_Label.Visible = Forms.frmRunReport.chkSchooldistrict
Me.SchoolDistrict.Visible = Forms.frmRunReport.chkSchooldistrict

Me.Address_Label.Visible = Forms.frmRunReport.chkAddress
Me.Address.Visible = Forms.frmRunReport.chkAddress

Me.BTUSqFoot.Visible = Forms.frmRunReport.chkBTUsqFoot
Me.BTUSqFoot_Label.Visible = Forms.frmRunReport.chkBTUsqFoot

Me.City_Label.Visible = Forms.frmRunReport.chkCity
Me.City.Visible = Forms.frmRunReport.chkCity

Me.CostPerSquareFoot_Label.Visible = Forms.frmRunReport.chkDollarSFoot
Me.CostPerSquareFoot.Visible = Forms.frmRunReport.chkDollarSFoot


End Sub
 
Back
Top