Syntax issue

[tomtom123]

Hi Everyone,

I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i. I`m sure it's rather simple, but I simply can't solve it. Thanks!

DoCmd.OpenReport "tblInterval subreport", acViewDesign, , , acHidden
Reports![tblInterval subreport]!BoxInsp1.Visible = False
Reports![tblInterval subreport]!BoxInsp2.Visible = False
Reports![tblInterval subreport]!BoxInsp3.Visible = False
Reports![tblInterval subreport]!BoxInsp4.Visible = False
Reports![tblInterval subreport]!BoxInsp5.Visible = False
Reports![tblInterval subreport]!BoxInsp6.Visible = False
Reports![tblInterval subreport]!BoxInsp7.Visible = False
Reports![tblInterval subreport]!BoxInsp8.Visible = False
Reports![tblInterval subreport]!BoxInsp9.Visible = False
Reports![tblInterval subreport]!BoxInsp10.Visible = False
DoCmd.Close acReport, "tblInterval subreport", acSaveYes

 

[Cronk]

I remember being in the same position as you and getting a heads up from a colleague. It was in Access 2 when I was relatively new to Access. So I'm pleased to pass the advice on while I recall pleasant memories of my work times with ChrisG.

You can refer to a control using its name as in forms!frmX.CtrlX or the form's collection of controls as in forms!frmX("CtrlX")

If your controls all have the same prefix and successive numeric suffix, you can use a loop and using your example, the code would be

For n = 1 to 12
Reports![tblInterval subreport]("BoxInsp" & n).Visible = False
next n

Note no ! or . is used in referencing the control.

Where the control names are disparate, you can use the Tag property of the control and set all of the ones you want to something, and then loop through all controls checking if the property matches.

 

[CJ_London]

If your code is within the form or report you can use the following construct:

For n = 1 to 12
    Me("BoxInsp" & n).Visible = False
next n