[RESOLVED] Creating a string builder
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:
Code:
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
Re: Creating a string builder
This is shorter and clearer:
Code:
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
