Dynamically Include Columns Based on Checkbox

[umchemist]

Hi Everyone,

I have a report with many columns in the detail listing data, and their associated titles above in the page header.

What I would like to accomplish is the ability for the user to check boxes off on a form which will remove/add specific columns to the report. The form will essentially be a "build your report" style, with a button on it opening the print preview of the custom report.

Im assuming this is accomplished by the "on load" part of the form through VBA. How is the sizing handled? If the user un-check's a box to remove a column of data, how is the header and detail data "moved over" or re-arranged to accomplish a relatively decent looking layout?

Can anyone provide an example or example code to deal with one column/setup (which I can copy and modified)??

Thank you

 

[billmeye]

I've tackled this in a report that has a datasheet type view, maybe ineffectively, by using the On Load to both determine what to show and where to position them. I used a bunch of if then etc. and I count how many controls to know what I've gotten and then hide the rest. I have all the controls I need with the Left set to 0 and the Top set to whatever I need it to be and adjust when needed. I make sure the widths in total don't expand beyond the page. You can always widen the widths after determining how many are showing and dividing by the overall width etc. and re-positioning based on new width.

ctlCount = 1
If [forms]![frmWhatToShow]!CheckBox1 = -1 Then
     Me("Ctl" & ctlCount).controlsource = "Whatever CheckBox1 represents"
     Me("labelCtl" & ctlCount).caption = "Whatever CheckBox1 represents"
     Me("Ctl" & ctlCount).visible = True
     Me("labelCtl" & ctlCount).visible = True
     If ctlCount = 1 then 
          Me("Ctl" & ctlCount).left = 0 
          Me("labelCtl" & ctlCount).left = 0 
     Else 
          Me("Ctl" & ctlCount).left = Me("Ctl" & ctlCount - 1).left + Me("Ctl" & ctlCount - 1).width
          Me("labelCtl" & ctlCount).left = Me("labelCtl" & ctlCount - 1).left + Me("labelCtl" & ctlCount - 1).width
     End If
     ctlCount = ctlCount + 1
ElseIf [forms]![frmWhatToShow]!CheckBox2 = -1 Then
     Me("Ctl" & ctlCount).controlsource = "Whatever CheckBox2 represents"
     Me("labelCtl" & ctlCount).caption = "Whatever CheckBox2 represents"
     Me("Ctl" & ctlCount).visible = True
     Me("labelCtl" & ctlCount).visible = True
     If ctlCount = 1 then 
          Me("Ctl" & ctlCount).left = 0 
          Me("labelCtl" & ctlCount).left = 0 
     Else 
          Me("Ctl" & ctlCount).left = Me("Ctl" & ctlCount - 1).left + Me("Ctl" & ctlCount - 1).width
          Me("labelCtl" & ctlCount).left = Me("labelCtl" & ctlCount - 1).left + Me("labelCtl" & ctlCount - 1).width
     End If
     ctlCount = ctlCount + 1
ElseIf etc.
End If
If ctlCount < 10 Then 'say max of controls
     For X = ctlCount to 9
         Me("Ctl" & X).visible = False
        Me("labelCtl" & X).visible = False
     Next X
End If

 

[ChrisO]

There is a demo here:-

http://www.access-programmers.co.uk/forums/showthread.php?t=243278

Chris.

 

[Pat Hartman]

Hi Bill,
We're almost neighbors I'm in Stratford. Have you ever been to the CT Access Users Goup meetings? Sign up in our Yahoo group - CTAUG to get anouncements. or go to www.ctaug.org. Monthly meetings are at the MS office in Farmington on the second Wednesday of the month. Hope to see you in June.
Pat

 

[billmeye]

Have you ever been to the CT Access Users Group meetings?

Hi Pat. It's a bit a haul for me so I haven't gone. I would like to but I just haven't been able to get there. I'll have to get up there one of these days.