[RESOLVED] 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

[anhn]

This is shorter and clearer:

Option Explicit

Public Function BuildSQL() As String
   Dim i As Long
   Dim sQ As String
   Dim sFields As String
   Dim sValues As String
   '--Dim q1 As String
   
   sFields = "QuestionaireID"
   sValues = "'" & q1 & "'" '-- What is q1 ????
   For i = 0 To 185
      sQ = Format(i, "\Q000")
      sFields = sFields & ", " & sQ
      sValues = sValues & ", '" & Forms![frmSection1].Controls(sQ).Value & "'"
   Next
   BuildSQL = "INSERT INTO tblSection1 (" & sFields & ") VALUES (" & sValues & ")"
   
   '-- to test:
   Debug.Print BuildSQL

End Function