error on recordset code

[bob]

When I am running these code it is giving me an error on
the db.execute line saying"
runtime error 3070. The Microsoft jet engine does not
recognise '00040' as a valid field name or expression"
can someone help?


Private Sub Command46_Click()

'below is ken's code.above is the test code to insert a
record in to the tbl attendance

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 & ") values(" & _
rs.Fields("StudentClockNum") & ");"

db.Execute strsQl 'dbFailOnError

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

End Sub

 

[Scott McDaniel]

I'm betting that the value of StudentClockNum is 00040 ... is this a field
in tblAttendance? From the error, I would think not. What's the value of
strSQL immediately before the .Execute statement?

Also, is the value of StudentClockNum unknown at designtime? I don't recall
ever writing an SQL Insert statement where I didn't know the fields into
which I would be adding data. What are you trying to accomplish with the
code routine?

 

[Tim Ferguson]

strsQl = _
"Insert into tblattendance(" & _
StudentClockNum & ") values(" & _
rs.Fields("StudentClockNum") & ");"

db.Execute strsQl 'dbFailOnError

We can only guess what StudentClockNum refers to - presumably it has the
value '0040' though --, and similarly I guess the recordset field contains
some kind of numeric value (let's say 2).

The SQL therefore parses into

INSERT INTO tblAttendance(0040)
VALUES(2);

which does not seem very sensible to me, and the db engine obviously
agrees. What is it that you are trying to achieve? Could it not be done
more simply by a simple append query

INSERT INTO tblAttendance(SomeFieldName)
SELECT StudentClockNum
FROM WhateverTheRecordsetCloneOriginallyWas;

Hope that helps


Tim F

 

[bob]

Thanks...I corrected the code. But now I need to modify
the code to have 4 other fields also. Now it is stopping
at rs.fields and saying "Compile erroe: Wrong number of
arguments or invalid property assignment"
Can you tell me where he problem is....


Private Sub Command46_Click()

'below is ken's code.above is the test code to insert a
record in to the tbl attendance

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

 

[bob]

Thanks...I corrected the code. But now I need to modify
the code to have 4 other fields also. Now it is stopping
at rs.fields and saying "Compile erroe: Wrong number of
arguments or invalid property assignment"
Can you tell me where he problem is....


Private Sub Command46_Click()

'below is ken's code.above is the test code to insert a
record in to the tbl attendance

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

 

[Tim Ferguson]

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

I think I can guess what you wanted from

rs.Fields("StudentClockNum", "progcode", "cnum")

but your best friend would tell you to go and look up help on "Retrieving
items from collections" and then the examples for the Fields collection.

The only sensible way to build up dynamic SQL commands is to have a clear
idea of what the finished command should look like. At a guess, it will be
something like

INSERT INTO tblAttendance
(StudentClockNum, ProgramCode, CourseNum)
VALUES(4004, "ESNA", "B607")

Clearly, I have no idea what data types each of these fields are going to
be: but you simply must know because getting right the quote marks or other
delimiters is going to be critical (once you have sorted out how to get the
values from the recordset). I do encourage you to put a

MsgBox strSQL

after creating the string but before the call to db.Execute so you can see
precisely what you are sending. Even cordon bleu cooks taste the food
before sending it out to the customers!

Hope that helps


Tim F