How can a reference fields on an Access form using a variable?

Question

I have 20 text boxes on an Access 2010 form called [P101] to [P110] which refers to fields [P101] to [P110] in the source table. The may contain a value or not, but if not I do not want to see them. I also have a field [UsedFields] in the table which has counted how many of the fields are in use. In Form_Current I can set the following code but is there a way I could set a FOR NEXT loop to use a variable for the Field Name? The current code (which works but is very clumsy) is:

If UsedFields > 0 then
   P101.Visible = True
Else
   P101.Visible = False
End If
If UsedFields > 1 then
   P102.Visible = True
Else
   P102.Visible = False
End If
.
.  
.
.
If UsedFields > 9 then
   P110.Visible = True
Else
   P110.Visible = False
End If

As the number of fields is set to increase from 10 to 100 I would like to use a variable to hold the TextBox name, something like:

Private Sub Form_Current()
    Dim Ctrl As Control
    Dim CtrlName As String
    Dim Counter As Long

    For Counter = 1 To 10
        CtrlName = "P" & Counter
        Set Ctrl = CtrlName
    If Counter > Me.UsedFields Then
        Ctrl.Visible = False
    Else
        Ctrl.Visible = True
    End If
End Sub

Is such a reference possible?

HansUp · Accepted Answer · 2013-12-09 17:59:50Z

5

You can use your string variable to refer to an item in the form's Controls collection.

Dim Ctrl As Control
Dim CtrlName As String
Dim Counter As Long

For Counter = 1 To 10
    CtrlName = "P" & Counter
    Set Ctrl = Me.Controls(CtrlName)
    If Counter > Me.UsedFields Then
        Ctrl.Visible = False
    Else
        Ctrl.Visible = True
    End If
Next

BTW, you can use a single line in place of the If block if that makes sense.

Ctrl.Visible = Not (Counter > Me.UsedFields)

answered Dec 9, 2013 at 17:59

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3083607 Over a year ago

Thanks HansUp, that worked perfectly and has saved a couple of hundred lines of code. You're a star.

Collectives™ on Stack Overflow

How can a reference fields on an Access form using a variable?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related