Need help w/ SQL INSERT statement

[Tom]

Background Info:
===========

I need some help with updating a table (single field at this time) based on
a unbound textbox in an unbound form.

The following describes my process (simplified for explanation purposes).

Table: tblTest
- contains an autonumber field
- contains a field called "Score"

Form: frmTest
- contains 4 unbound textboxes ("Data" property is left blank; "Name"
property (Other tab) equals "Box1", "Box2", "Box3", "Box4", respectively.
- contains a "Submit" command button. Its code is listed below:


Private Sub Submit_Click()

strSQL = "Insert into tblTest (Score) " & _
"VALUES ('" & Me!Box1 & "' & '" & Me!Box2 & "' & '" & Me!Box3 & "' & '"
& Me!Box4
& "')"

CurrentDb().Execute strSQL, dbFailOnError

End Sub



Here's what currently happens:
====================
1. I enter the following values into unbound texboxes Box1, Box2, Box3, &
Box4: "10", "20", "30", & "40", respectively
2. Then I press the SUBMIT command button
3. Open up tblTest to review the record(s)
4. Result equals: 1 record (reading "10,203,040.0" -- field format is
Double, Standard, 1 decimal)


What should happen instead:
===================
A. I follow steps 1 to 3 (as described above)
B. Result should equal: 4 records; each of them has the values of "10",
"20", "30", & "40" in the Score field


So, my questions are the following:
======================

1. how can I make it that I create 4 record (with their respective values)
instead of a single records with the strung values?
2. In the real application, I will have probably 200 unbound textboxes. At
this time, it does not seem very efficient to hardcode each "Name" property
into the INSERT statement. Maintenance would become almost a nightmare, I
guess. So, is there a more elegant way of approaching this?


Thanks,
Tom

 

[Dan Artuso]

Hi,
Something like this:

Dim i as Integer
Dim strSql as String

For i = 1 To 4
strSql = "Insert Into tblTest (Score) Values(" & Me("Box" & i) & ")"
Next i

 

[Tom]

Thanks, that works great.

Tom

Hi,
Something like this:

Dim i as Integer
Dim strSql as String

For i = 1 To 4
strSql = "Insert Into tblTest (Score) Values(" & Me("Box" & i) & ")"
Next i