MS access 2003

[cra57]

I have a form that cotains lots of check boxes and i only want the fields with the box checked "true" to show up on the report. How can i do this?

 

[ck1999]

try this code


Private Sub Form_Load()

Dim ctl As Control

For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox Then
If ctl.Value = True Then
ctl.Visible = True
Else
ctl.Visible = False
End If
End If



Next ctl

End Sub

ck1999

 

[cra57]

where do i put the code?

 

[ck1999]

if the form load event.

Open your form (in design ) then select properties
make sure the selection type is form

click the event tab, click "On Load" choose event procedure then click the three little dots.


ck1999

 

[andrea08]

This isn't what i meant. i guess. I need the report to not display the false fields but the form needs to show all for selection purposes. so i pretty much want my report to look like my form but not showing the empty check box fields or empty comment field.

 

[ck1999]

Sorry I missed that part of the original post.

You will have to place code in the report "detail_format" section to check if field value = false then visible=false

I think you will have to check each individually.

Ck1999

 

[andrea08]

i am new at this. could you type the exact code that i would need for each field? Use "Tile" for an example.

 

[fneily]

Should be posted in Microsoft: Access Reports.
In design view of the report, click on the Detail Bar. Bring up the property sheet(click the Property button). Click the tab Event. Click in the box next to On Print. Click the button with the three dots. Select Code Builder. Copy or type in:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
If TypeOf ctl Is CheckBox Then
If ctl.Value = True Then
ctl.Visible = True
Else
ctl.Visible = False
End If
End If
Next ctl
End Sub

You will not have to type the Private Sub or End Sub statements.

 

[ck1999]

This needs to be put in the detail_format event

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

        If Me.[tile] = True Then [tile].Visible = True Else [tile].Visible = False
        If Me.[tile2] = True Then [tile2].Visible = True Else [tile2].Visible = False
        If Me.[tile3] = True Then [tile3].Visible = True Else [tile3].Visible = False
       
        
        Text4 = "as"
End Sub

find this event similar to the way i explained the form event. Also you will need to print preview to see results.

ck1999

 

[andrea08]

Thank you!! It works, now one more thing??? can i make the space go away or am i stuck with empty spaces where the field won't show up?

 

[ck1999]

Replace the other code with this

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
         
       Dim vtop
       Dim vspacer
       Dim vinitialtop
         vtop = 800
         vspacer = 375
         vinitialtop = 800
        If Me.[tile] = True Then
            [tile].Visible = True
            [Label1].Top = vtop
            [tile].Top = vtop
            vtop = vtop + vspacer
        Else
            [tile].Visible = False
            [Label1].Top = vinitialtop
            [tile].Top = vinitialtop
        End If
     If Me.[tile2] = True Then
            [tile2].Visible = True
            [Label2].Top = vtop
            [tile2].Top = vtop
            vtop = vtop + vspacer
        Else
            [tile2].Visible = False
            [Label2].Top = vinitialtop
            [tile2].Top = vinitialtop
        
        End If
        If Me.[tile3] = True Then
            [tile3].Visible = True
            [Label3].Top = vtop
            [tile3].Top = vtop
            vtop = vtop + vspacer
        Else
            [tile3].Visible = False
            [Label3].Top = vinitialtop
            [tile3].Top = vinitialtop
        End If
 
End Sub

To use this code

1. Change detail section to can grow = yes.
2. Click on your 1st check box and determine its top propery value i think under format
3. change in the above vtop and vinitial top to whatever this value was in inches * 1440 so if value was 2.00 take 2.00*1440 and replace the 800 with 2880
4. Stack all checkboxes ontop of the 1st checkbox
5. Change me fields above to your field names
6. click print preview


see how it works for you

 

[andrea08]

You guys are awesome. Thank you!