Creating a string builder

[dreamdelerium]

hi everyone. im trying to create a string builder and ran into a problem. i have a form with 185 controls on it. i want to create an automated sql insert statement using a for loop. (ie. insert into MyTable (q000,q001...q185) values (forms![frmSection1].controls![Q000].value,forms![frmSection1].controls![Q001].value...forms![frmSection1].controls![Q185].value) ). here is what i got so far:

Public Function buildMyString()
Dim GetLength As Integer
Dim InsertString, InsertString1 As String
Dim MyNewString As String
 
For i = 0 To 185
InsertString = InsertString & "Q" & Format(i, "000") & ","
Next i

GetLength = Len(InsertString) - 1
InsertString = Left(InsertString, GetLength)
'builds first part of string.  this is working fine
 
For i = 0 To 185
InsertString1 = InsertString1 & Forms![frmSection1].Controls![Q001].Value & "','"
'this is where i need help.  where it says [Q001] i want it to increments by 
' one. ie Q001,Q002, etc. so i can get the value for every control on the
'form
Next i

 
GetLength = Len(InsertString1) - 1
InsertString1 = Left(InsertString1, GetLength)

MyNewString = "Insert into tblSection1 (QuestionaireID," & InsertString & ") values ('" & q1 & "','" & InsertString1 & ")"
 
 GetLength = Len(MyNewString) - 2
MyNewString = Left(MyNewString, GetLength)
MyNewString = MyNewString & ")"
MyNewString = MyNewString
 
  
  buildMyString = MyNewString

end function

thanks everyone

 

[Guus2005]

Note that concatenating strings is very slow. When you use a lot of & it might help to use a function to concatenate strings. It looks complicated to use a function for something as basic as pasting strings together but it saves on execution time.
Few years back i came accross this code at www.planetsourcecode.com.

Share(d) & Enjoy(ed)!

 

[Guus2005]

Yes i saw it. It is here: http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=37141&lngWId=1

Sorry 4 the inconvenience!

 

[dreamdelerium]

cool ill take a look at it. thanks all