Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Chapel Hill, North Carolina, USA
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA syntax for dynamic field names (Access 97/2000)

    I have several controls names on a form that are similar - Tooth_A, Tooth_B, Tooth_C. I need to read the value of one of the controls, but need to build the name on the fly in VBA, such as DesiredTooth = "Tooth_" & Toothname where DesiredTooth is a string variable.

    There are actually about 150 controls and I will need to check them all, so brute force is not a solution.

    What is the syntax to read the desired tooth value?

    me!DesiredTooth.value does not work - should I use a control data type? I couldn't figure out the syntax for that either.

    Any ideas?

    Thanks!

    Larry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA syntax for dynamic field names (Access 97/2000)

    You can use

    Me.Controls(DesiredTooth)

    or even

    Me(DesiredTooth)

    since Controls is the default property of a form (or report).

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: VBA syntax for dynamic field names (Access 97/2000)

    If you know that bunches of controls have the same format of the names, you can cycle thru them rather quickly. For example, let's say you have controls Tooth1,....., Tooth99. You can use this code to cyle them.

    Dim N as integer

    For N=1 to 99
    CallSomeSub Me("Tooth" & N)
    next N
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Lounger
    Join Date
    Apr 2001
    Location
    Khon Kaen, Thailand
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA syntax for dynamic field names (Access 97/2000)

    I don't know what you really want to do with the control. But you can modify the code below to fit your need.

    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    If Right(ctl.Name, 4) = "Name" Then
    MsgBox ctl.Name
    Else
    End If
    End If
    Next

    The code searches thru the Text Box controls in the form. If the controls' name ended up with ..Name, then display its name in the Message Box.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •