multifields recordset error

[bob]

I am having a problem when I am running this code. It
works fine with one field but when I am putting all the
three fields at a time it is creating a problem...

the error is pointing to the statement...insert into...

and the message is..
Compile error:
Wrong number of argumentsor invalid property assignment.


Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

strsQl = _
"Insert into tblattendance
(StudentClockNum,programcode,coursenum) values (" & _
rs.Fields("StudentClockNum", "progcode", "cnum") & ");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

 

[John Spencer (MVP)]

You need to build the string correctly.

strsQl = "Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum) values (" & _
rs.Fields("StudentClockNum") & _
", """ & rs.Fields("progcode") & """" & _
", " & rs.Fields("cnum") & ");"

Best way to check this to see if you have a valid SQL statement is to
temporarily insert

Debug.Print strSQL
Stop

in your code and then examine the statement in the immediate pane of the code window.

NOTE: I guessed that StudentClockNum is a number field, ProgCode is a text
field, and CNum is a number field. You will have to add/delete the quote marks
as needed to make adjustments.

 

[bob]

Thanks!!

I figures this one out but only partly.


I have 2 fields which are coming froma table and I am
writing a query and showing it on a form and then
inserting them in to an anotther table. Simultaneously on
the same form I have 3 fields called Progcode,coursenum
and logintime which I am having the user enter in
textboxes and then for each record in the recordset I will
be inserting these values on to the new table.

Essentailly my question is how would I enter these unbound
fields in to the new table.

The code that I have devleoped so far worsk fine..and that
is... Please note that the Thours afield and the
Studentclocknum fields are bound fields. The other three
fields called Programcode,cnum and logintime are unbound
fields. Should I use them as variables and also add them
to my insert statement or what?





Private Sub Command46_Click()


Dim db As Database
Dim rs As Recordset
Dim strsQl As String

Set rs = Me.RecordsetClone
Set db = CurrentDb

rs.MoveFirst

Do Until rs.EOF

'begin of the insert statement to insert the records

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,shours) " & _
"Values (" & _
rs.Fields("StudentClockNum") & ", " & _
rs.Fields("thours") & _
");"

db.Execute strsQl, dbFailOnError

rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing

End Sub

-----Original Message-----


You can only index your recordset by one field at a time, so you must
write your statement like this:

strsQl = _
"Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum) " & _
"Values (" & _
rs.Fields("StudentClockNum") & ", " & _
rs.Fields("progcode") & ", " & _
rs.Fields("cnum") & _
");"

Unless I made a typo somewhere, that will work so long as these are all
numeric fields.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

-----Original Message-----
You need to build the string correctly.

strsQl = "Insert into tblattendance " & _
"(StudentClockNum,programcode,coursenum) values (" & _
rs.Fields("StudentClockNum") & _
", """ & rs.Fields("progcode") & """" & _
", " & rs.Fields("cnum") & ");"

Best way to check this to see if you have a valid SQL statement is to
temporarily insert

Debug.Print strSQL
Stop

in your code and then examine the statement in the

immediate pane of the code window.