error on recordset

[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

 

[Gary Miller]

Bob,

You seem to be treating the field you are inserting into as
a variable by using the ampersands. If the field you are
inserting the clock number into is named 'StudentClockNum'
then try this revision.,,

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


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[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

 

[Scott McDaniel]

You must list the fields separately:

"Insert into tblattendance
(StudentClockNum,programcode,coursenum) values (" & _
rs("StudentClockNum") & "," & rs("progcode") & "," & rs("cnum") & " )"

Note that is these are text fields, you need to surround them with single
quotes.

 

[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