Hello,
I was hoping one of you could help me with this seemingly trivial VBA
issue that has nevertheless confounded me. I have a form with
fourteen textboxes. Each textbox has the same name except for the
last digit (e.g., the names are txtOrderID1, txtOrderID2, txtOrderID3,
etc.) Clicking a button opens a sub-procedure that is supposed to
insert the data from the textboxes into a table. Instead of creating
an a SQL insert statement for each textbox, I have attempted to create
a single loop that will create them all for me. So far, I have
attempted to do this by having the loop create the name of the
textbook in the form of a string by adjusting the last digit:

Note: I apologize for any errors that may appear here; I've manually
typed this as an example of what I'm trying to do.

dim i as integer
dim q as integer
dim sql as string
dim txtOrderIDName as string

i = 14
q = 1

Do until q > i
txtOrderIDName = "OrderID" & count
sql = "INSERT INTO Order (OrderID) VALUES (Me." & txtOrderIDName &
");"
DoCmd.RunSQL(sql)
count = count + 1
Loop

My hope was that VB would substitute the actual value of the textbox
for its name in string form but unfortunately this has not worked.
Could someone please tell me how I can take the name of a textbox in
string form and use it to retrieve the actual data from a textbox? Is
there another way to do this?

Thank you very, very much for your help.


Swann

Take a look at the Controls collection...

me.controls("ctlName").value

If you need to access it from a module other than the one that holds the
form, you will either need to pass the form into the function or
reference it with it's full name.

or loop through using "for each". I set the "tag" property so I can tell
which ones to use as a group :

for each c in me.controls
if (c.ControlType = acTextBox) then
if (c.tag = "autoinsert") then
msgbox c.Name & "-" & c.value
end if
end if
next

HTH,
Andrew Backer

Swann wrote: