User selects what fields to appear on Report

Take a look at:
www.thenelson.name/#CustomizableReport

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.

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.  

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.

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

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

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.

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

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.

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

Select allOpen 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

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.

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

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?

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