User selects what fields to appear on Report

napsternova
napsternova used Ask the Experts™
on
OK, this one seems complicated.  I have a form that opens and with it the report in print view.  The form lets the end user select filtering options by: City - combo box, BB option - Option Group, SF - combo box.  Sorting by: Name, City, and Size.  Then the user selects the Apply Filter button and they have their report.  Nice, but now there are different requirements for different people.  I want to keep the filtering the same but would like to allow users to select what fields appear on the report.  I have a total of 25 fields and they all won't fit so, 1) how can I give users display options on the same form as the filtering and sorting?  2) Is it possible to do the filtering without the user selecting the button?  Can the report update as a user selects different checkboxes so they can see what the report will look like in real-time?  Of course some users will cry when they cannot have all 25 fields but instead of me explaining it 1000 times to them they can see what will fit.  My report is already set up in landscape and I currently have 14 fields but they are predetermined.  If need be I can post this in segments because of the many questions.  If all my questions cannot be done I am open to alternatives.
Thanks everyone.  

Untitled-1.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
napsternovaDeveloper

Author

Commented:
Hmm, this will take some work.  I will try and edit it to my needs and if it works I will gladly donate. LOL  Seems like the multiple selection list may me the most space efficient but checkboxes looks better (in my opinion).  The other issue would be someone selecting all of my items and making a crammed report.
<this will take some work>
The sample is designed to allow you to simply paste your details into the form.

<but checkboxes looks better (in my opinion). >
That would be relatively easily to do.
 
<The other issue would be someone selecting all of my items and making a crammed report.>
Add coding to limit the number of items selected or better, limit the line width or even better, change the font size and limit the line width.

<so they can see what the report will look like in real-time>
You would need to close then recreate the report and place the form on top of the report each time they made a selection to do this.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

napsternovaDeveloper

Author

Commented:
thenelson,
Alright, looks like I have enough to keep me busy for a while.  I suppose if the user just selects apply filter to the open form that will work.  This is going to be fun.  
napsternovaDeveloper

Author

Commented:
Looks like I have an issue right off the bat.  How do I use my current popup form with my opened "print view report"?  Currently my database hase 900+ entries so displaying them all in a form will not really work.
One other thought. Could you use grouping to display all the data of the 25 fields without displaying all 25 fields in columns (and create an easier to read report to boot)?  Just guessing without seeing your report (the form covers it in the image).
For example it looks like you can group by City and remove City as one of the columns.
<How do I use my current popup form with my opened "print view report"? >
Sorry, I don't understand the question.

<Currently my database hase 900+ entries so displaying them all in a form will not really work.>
If you are trying to modify my sample, you could set the header and detail visible properties to No. If you delete them, you will need to change the code since I pull some of the report properties from the label properties but you are planning to change the listbox to checkboxes anyway.
napsternovaDeveloper

Author

Commented:
So lets say using my report.  Do I need to create a report with all the columns and then make them not visible?  And then using checkboxes or whatever make them visible?  If so then would that be on onLoad?
<Do I need to create a report with all the columns and then make them not visible?>
You could do that however, if you don't want blank columns in your report, you will also need to move the controls.  This would need to be done in design mode. These RunCommands may help:
RunCommand acCmdSelectAll
RunCommand acCmdSelectEntireRow
RunCommand acCmdAlignLeft
RunCommand acCmdAlignRight

napsternovaDeveloper

Author

Commented:
That is the problem I have now.  I tried it using the first column named CenterName.  When the report loads the field CenterName and its label CenterNameLabel are not visible but there is a blank field.   Granted when I click the checkbox in my form and hit the run Filter button the names show up.  Where do I put the RunCommand?  Not sure if this will work anyway.
You would need to open the report in design mode to move the controls.

You can use the left command to move one control at a time using the left position and width of the control to the left of the control you are moving.

You could select the controls that are visible and align them to the left:
Dim ctl as Control

For Each ctl in Report!ReportName
   If ctl.Visible = True Then ctl.InSelect = True
Next ctl
RunCommand acCmdAlignLeft
napsternovaDeveloper

Author

Commented:
You guys have me a bit confused.  The images are what I get when I use the code to hide the fields.  Then the user selects from a few check boxes what they want to see on the report.  The thing now is I cannot get the fields to shift to the left.
report.jpg
report-DV.jpg
Yes, to shift the controls to the left, the report has to be opened in design mode. Then the controls can be shifted with the suggestion #24896347 above. Then you change the report to print preview mode.

I am in the process of creating a form that will modify a report as we have been discussing.
napsternovaDeveloper

Author

Commented:
Well, that would be great because I have gone a bit of a different way, instead of giving the user complete control I created 5 radio radio buttons each responsible for displaying data relating to the user needs.  Less control for the user but easier to maintain and get results.  So the problem still remains, I had to create a report with 50 columns only 3 are viewable at the start.  For the life in me I cannot figure out what you are talking about.

<You can use the left command to move one control at a time using the left position and width of the control to the left of the control you are moving.

You could select the controls that are visible and align them to the left:
Dim ctl as Control

For Each ctl in Report!ReportName
   If ctl.Visible = True Then ctl.InSelect = True
Next ctl
RunCommand acCmdAlignLeft
napsternovaDeveloper

Author

Commented:
I am so close, how do I use the code?  Does this go in an event?

Dim ctl as Control
For Each ctl in Report!CompleteReport
   If ctl.Visible = True Then ctl.InSelect = True
Next ctl
RunCommand acCmdAlignLeft
>how do I use the code?

Post the entire procedure of the code that makes the controls not visible and I will show You.
napsternovaDeveloper

Author

Commented:
This is the code for the On Open Event.  It hides two fields as of now

Option Compare Database
Option Explicit
 
 
Private Sub Report_Open(Cancel As Integer)
  Me![TradeArea].Visible = Nz(Forms![TradeAreaCityForm]![TradeAreaCheck], False)
  Me![Trade Area Label].Visible = Nz(Forms![TradeAreaCityForm]![TradeAreaCheck], False)
 
  Me![Intersection].Visible = Nz(Forms![TradeAreaCityForm]![IntersectionCheck], False)
  Me![IntersectionLabel].Visible = Nz(Forms![TradeAreaCityForm]![IntersectionCheck], False)
 
End Sub

Open in new window

You don't want to put the code there.  Put this code in the button click event on the form that opens the report:

Private Sub Button_Click ()

'We will copy the report, then save the copy so the user will not get a popup
'message to save the report. Later we will delete the copy.
DoCmd.CopyObject, "ReportCopy", acReport, "YourReportNameHere"

DoCmd.OpenReport "ReportCopy", acViewDesign

With Reports!ReportCopy

If TradeAreaCheck Then
   ![TradeArea].Visible = True
   ![TradeArea].InSelect = True
   ![Trade Area Label].Visible = True
   ![Trade Area Label].InSelect = True
End If

If TradeAreaCheck Then
   ![Intersection].Visible = True
   ![Intersection].InSelect = True
   ![IntersectionLabel].Visible = True
   ![IntersectionLabel].InSelect = True
End If

End With

RunCommand acCmdAlignLeft
RunCommand acCmdPrintPreview
RunCommand acCmdZoom75
DoCmd.Restore
DoCmd.Save acReport, "ReportCopy"

End Sub

In the form's Open and Close events, add these lines:

Private Sub Form_Close()

On Error Resume Next
DoCmd.DeleteObject acReport, "ReportCopy"
On Error Goto 0

End Sub

Private Sub Form_Open()

On Error Resume Next
DoCmd.DeleteObject acReport, "ReportCopy"
On Error Goto 0

End Sub

I just thought if the leftmost label and textbox is not selected, the next label and textbox will have nothing to left align to and there will be a gap. To resolve this, create a label and a textbox against the left margin with these properties:
Name: Label: lblForAlignment   Textbox: txtForAlignment
Left: 0
Width: 0.0007"
Visible: No

Then add this to the code:

With Reports!ReportCopy

!lblForAlignment.InSelect = True
!txtForAlignment.InSelect = True

If TradeAreaCheck Then
napsternovaDeveloper

Author

Commented:
Just to be sure about this.  I have a switchboard, the user clicks a button called Detailed Report by City, the macro opens a form called TradeAreaCityForm, the form opens as a pop-up and has an On Load event procedure that opens the report CompleteReport in acPreview behind it, this way when users select filter options and hit the button apply filter they see it happen.  So...  you say apply this code to the button that opens the form do you think I should apply it to the TradeAraCityForm macro?  I will give more points because you have really done more then should be expected.  I really appreciate it.  Really I may have to rethink how I am going about these reports.
<the report CompleteReport in acPreview behind it, this way when users select filter options and hit the button apply filter they see it happen.>

You will not be able to do that. You cannot move the controls on a report after the report is created. So the only way to make this work is open the report in design view. Move the controls around. Switch the report to Preview.  If the user does not like the format, he can close the report and try again.

So the steps to change what you have is:
1. Remove the OpenReport Statement in the form's On Load event procedure
2. Add the lblForAlignment, txtForAlignment controls to the report as I described in http:#24921829 above
3. Change the code in the apply filter button On Click event to:

'We will copy the report, then save the copy so the user will not get a popup
'message to save the report. Later we will delete the copy.
DoCmd.CopyObject, "ReportCopy", acReport, "YourReportNameHere"

DoCmd.OpenReport "ReportCopy", acViewDesign

With Reports!ReportCopy

!lblForAlignment.InSelect = True
!txtForAlignment.InSelect = True

If TradeAreaCheck Then
   ![TradeArea].Visible = True
   ![TradeArea].InSelect = True
   ![Trade Area Label].Visible = True
   ![Trade Area Label].InSelect = True
End If

If TradeAreaCheck Then
   ![Intersection].Visible = True
   ![Intersection].InSelect = True
   ![IntersectionLabel].Visible = True
   ![IntersectionLabel].InSelect = True
End If

End With

RunCommand acCmdAlignLeft
RunCommand acCmdPrintPreview
RunCommand acCmdZoom75
DoCmd.Restore
DoCmd.Save acReport, "ReportCopy"

4. Change the form's Open and Close events as i described in http:#24921769 above
If you want, you can post your db. I will make the necessary changes and post it back to you.

To make the db as small as possible and remove all sensitive material, do this:
1. Make a copy of your db. Since it looks like you are using Access 2007, make the copy in A2003 format.
2. In the copy, remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
3. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete.
4. Compact and repair
5. Zip (optional) and attach to a message.
napsternovaDeveloper

Author

Commented:
Yeah I am at a complete loss here.  I wouldn't even mind just having a combo box with "Report Style" and have pre formatted forms for different data.  I have 50 fields and need only 10 max to be viewed at once.  I just don't want to create copies of every report and form.  I may have taken out too much info from the form because it won't open the report at all.  I also get an error at the RunCommand syntax.
MidamDB1.mdb
napsternovaDeveloper

Author

Commented:
After doing some research couldn't I do this easier by removing the labels from the Page Header and just use the Detail section?  Then how can I do the same shift function?
I have uploaded a db with working forms. You will see two forms:

"RunReport" is my form which uses the report "CompleteReport2" which is your report but with the label and textbox names changed to support my form. That is controls that are hidden or displayed by the form have special name requirements.   Data controls (textboxes, comboboxes, etc) and their associated labels should have the same name except labels have a prefix of "clbl" and data controls have a prefix of "cctl".

In the top off the module for "RunReport", you will find:
Private Const ReportName As String = "CompleteReport2"  'Enter name of report to control here
Private Const MaxWidth As Integer = 10098  'Enter maximum width in twips (1440 twips = 1inch) here.
Change those as needed.

"TradeAreaCityForm" is your form modified to work.

Note: your report was corrupted - drove me nuts for a while but it is fixed now.
MidamDB1.mdb
napsternovaDeveloper

Author

Commented:
WOW, truly amazing!!!  I have been working on this thing for days.  I cannot thank you enough.  The group of people here is really something else.  
You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
Upon probably want to add one line to the "cmdOpenReport_Click" procedure in the code for "RunReport".  I've included the line above and below so you know where it goes:

Next varItm

.Width = MaxWidth  'add this line
End With

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial