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" & q
sql = "INSERT INTO Order (OrderID) VALUES (Me." & txtOrderIDName &
");"
DoCmd.RunSQL(sql)
count = q + 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?

hephaestus123 (AT) hotmail (DOT) com (Swann) wrote in message news:<64c8ad31.0404231422.eb5268a (AT) posting (DOT) google.com>...
Hello Swann,

Perhaps the following code is close to what
you need. What has to be done is to loop through
all of your controls and get the data required.
Then append the data to where you want it to go.

Example:

Dim ctl As Control
Dim frm As Form
Dim i as Integer, q as Integer
Dim ctlData As String, sql as String

Set frm = Forms("frmFormNameGoesHere")
i = 14
q = 0

For Each ctl In frm.Controls
ctlType = ctl.ControlType
If ctlType = 109 Then '109 = a text box
ctlData = ctlData & ctl.Value & ", " 'This line adds the data together.
q = q + 1
end if
Next ctl

Now that the variable ctlData has the concantination of
all fields, put your expression for your SQL statement
after the loop.

sql = "INSERT INTO Order (OrderID) VALUES (Me." & txtOrderIDName & > ");"
DoCmd.RunSQL(sql)

Hope this sets you on the right road.

Regards,

Ray