locked
Using Control Names as Variables RRS feed

  • Question

  • I have a VBA statement ( If me.boxname = 7 then xxxx) that I would like to use read values in various control boxes.  Rather than create 100 statements, one for each control box name, I would like to create a generic statement where I can change the control box name each time like a variable.  Is this possible?  I use Access Office 365.

    Any help would be appreciated

    Frank Wagner

    Friday, January 31, 2020 5:48 PM

All replies

  • Is this concerning VB.NET, a .NET platform that the forum is dedicated to or is concerning VBA a COM solutions that has its own forum?
    Friday, January 31, 2020 6:01 PM
  • Yes that is possible, via the Controls collection:

    dim strCtlName as string

    strCtlName = "txtFirstName"

    if Me.Controls(strCtlName).Value = "Fred" then ...


    -Tom. Microsoft Access MVP

    Friday, January 31, 2020 6:47 PM
  • Oops, I wanted to add you can also do this (off the cuff):
    dim ctl as Access.Control
    for each ctl in Me.Controls
      if typeof ctl is TextBox then
        if ctl.Value = "Fred" then ...


    -Tom. Microsoft Access MVP

    Friday, January 31, 2020 6:49 PM
  • I have a VBA statement ( If me.boxname = 7 then xxxx) that I would like to use read values in various control boxes.

    Hi Frank,

    You can use the syntax   Me(variablename)   or   Me("boxname")   to address a control on a form.

    Imb.

    Friday, January 31, 2020 6:50 PM
  • I think what you are asking is something like this, using one generic function with an argument of a control.

    Public Function DoSomething(ctrl As Control) As Boolean
        'Call this from a form this way: rtn=DoSomething(Me.txtCustomers)
        If ctrl.ControlType = acTextBox Then
            DoSomething = True
        Else: DoSomething = False
        End If
    End Function
    

    You pass the control from the form using Me.<controlname>


    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Friday, January 31, 2020 8:45 PM
  • As you can see, it can be done. But as you seem to have the same type of data in several (100?) controls, it could also mean that there is something wrong with your data structure. Without context this is impossible to tell. 

    Groeten, Peter http://access.xps350.com/

    Friday, January 31, 2020 8:53 PM