Turning columns in a report on and off with checkboxes

[lulugrant_is_a_dog]

I am trying to turn columns on an off in a report by using checkboxes
on another form (called frmAdvancedSearch). The code below is run when
the report is opened. I have named everything with numbers (chk1, chk2,
txt1, txt2 etc.) so that I can use a loop and save myself from writing
a lot of code.

--------------------------------------------
Private Sub Report_Open(Cancel As Integer)
'position of column on report
Dim position As Long
'name of check box on frmAdvancedSearch
Dim chkno As Object

position = 0.68

For K = 1 To 16
Set chkno = "chk" & K
If Forms![frmAdvancedSearch]![chkno] = True Then
Me("txt" & K).Left = position
Me("txt" & K).Visible = True
Me("lbl" & K).Left = position
Me("lbl" & K).Visible = True
position = position + Me("txt" & K).Width
End If
Next K

End Sub
--------------------------------------------------

The code doesn't work thought, and I think it has something to do with
the way I have addressed the checkboxes on the form. When the report
opens an error is displayed that says "Object Required".

Debug points me to the line "Set chkno = "chk" & K"

I am not very experienced with this and any help would be greatly
appreciated.

 

[John Spencer]

Try

Dim StrChkNo as String

strChkNo = "Chk" & K

If Forms![frmAdvancedSearch].Controls(strChkNo) = True Then
...

Also, since position should be in TWIPS you might need to multiply by 1440
(1440 TWIPs to the inch) in the first assign statement. If you are working
on the metric system, you will need to convert 1440 to the appropriate value

Position = .68 * 1440

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[lulugrant_is_a_dog]

Thanks, everything is working now!

Try

Dim StrChkNo as String

strChkNo = "Chk" & K

If Forms![frmAdvancedSearch].Controls(strChkNo) = True Then
...

Also, since position should be in TWIPS you might need to multiply by 1440
(1440 TWIPs to the inch) in the first assign statement. If you are working
on the metric system, you will need to convert 1440 to the appropriate value

Position = .68 * 1440

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am trying to turn columns on an off in a report by using checkboxes
on another form (called frmAdvancedSearch). The code below is run when
the report is opened. I have named everything with numbers (chk1, chk2,
txt1, txt2 etc.) so that I can use a loop and save myself from writing
a lot of code.

--------------------------------------------
Private Sub Report_Open(Cancel As Integer)
'position of column on report
Dim position As Long
'name of check box on frmAdvancedSearch
Dim chkno As Object

position = 0.68

For K = 1 To 16
Set chkno = "chk" & K
If Forms![frmAdvancedSearch]![chkno] = True Then
Me("txt" & K).Left = position
Me("txt" & K).Visible = True
Me("lbl" & K).Left = position
Me("lbl" & K).Visible = True
position = position + Me("txt" & K).Width
End If
Next K

End Sub
--------------------------------------------------

The code doesn't work thought, and I think it has something to do with
the way I have addressed the checkboxes on the form. When the report
opens an error is displayed that says "Object Required".

Debug points me to the line "Set chkno = "chk" & K"

I am not very experienced with this and any help would be greatly
appreciated.