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

Topic

New Reply

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

Reply | Quote

Replies

You can use

Me.Controls(DesiredTooth)

or even

Me(DesiredTooth)

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote