insert query for a recordset

[bob]

I have a recordset...a form of which I am reading the
values and inserting it in to a table. I am having an
error somehwere and I am not able to figure it out.The
code is something like this.....

The cPrivate Sub Command46_Click()
Dim rs As Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
Insert into tblattendance
(studentclockNum,ProgramCode,CourseNum,LoginTime,Exempt,Sho
urs)values(rs.Fields"studentclocknumber,
,progcode,cnum,login,exemptall,thours)


rs.MoveNext
Loop
End Sub

 

[John Vinson]

I have a recordset...a form of which I am reading the
values and inserting it in to a table. I am having an
error somehwere and I am not able to figure it out.The
code is something like this.....

The cPrivate Sub Command46_Click()
Dim rs As Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
Insert into tblattendance
(studentclockNum,ProgramCode,CourseNum,LoginTime,Exempt,Sho
urs)values(rs.Fields"studentclocknumber,
,progcode,cnum,login,exemptall,thours)


rs.MoveNext
Loop
End Sub

You're mixing two different languages, VBA and SQL; and though you're
opening the recordset, you're not referencing it at all.

COuld you step back a bit and explain what you're trying to
accomplish? What's the Recordsource of the form? or is it an unbound
form (and if so, why did you choose to use the more complex technique
of using an unbound Form rather than a simple bound form?)

 

[bob]

Essentially I have a form which is pulling the data and
then there are some moe fields on the form which get
filled in by the user and then I want to insert all these
records in to a third table...not the table the form was
originally linked to. Hence I am creating a clone and then
using a recordset to insert the records one by one.

 

[John Vinson]

Essentially I have a form which is pulling the data and
then there are some moe fields on the form which get
filled in by the user and then I want to insert all these
records in to a third table...not the table the form was
originally linked to. Hence I am creating a clone and then
using a recordset to insert the records one by one.

A Form's RecordsetClone refers to the Recordsource OF THAT FORM; it
sounds like you want to open an independent recordset based on a third
table, and insert into that recordset the values in various textboxes
on the Form. Is this the case? Or do you want to enter an arbitrary
number of records on a form, and then copy (at least a part of) these
records to the third table?

The need to do this is VERY suspicious. It certainly sounds like
you're storing these fields redundantly in two different tables. Is
that the case? If so why?

The syntax for the single-record operation (if that is indeed what you
want to do) would be something like

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("thirdtablename", dbOpenDynaset)
rs.AddNew
rs!ThisField = Me!ThisControl
rs!ThatField = Me!ThatControl
<etc>
rs.Update
rs.Close
Set rs = Nothing

 

[bob]

Thanks again

Let me digress here and give you the details.

I have an application called Student Training System which
is basically used by the mechanics/technicians etc as part
of their ongoing training which is computer based.

I am doing an enhancement whereby the Supervisor of this
program is able to exempt all the students for a
particular week such as Xmas etc.

I have a table called tblstudents which has all the
information about the students and the individual
schedules.

The only thing that I have to calculate on the fly is the
noumer of hours for each student,Thours field which is
basically a product of the number of sched seesions and
the sched hours for each session. What I am doing is I am
taking all these information and putting it on to a form.

Now, at any given time each student maybe in diffrent
program eg M11,G12 etc but That information comes from a
different table and I am assuming that the supervisor will
just give the exemption on a dummy program call T11 and a
dummy course num of 25.

Now everytime a student attends a session a record of that
is created in the tblattendance table and this includes...
Student clock number,Prog code,CoursNumber,Login
Time,Logout time,Exempt,Hours attended.

Of which the Stud clock num,prog code,course num,login
time,are all part of the pkey and hence they must be
filled at all times.

So I am creating a form..part of the data on that form
coes from this tblstudents. Then on that form I am adding
text fields for the super to add like Prog code,Course
num,Login time,Exempt all,Hours and when the user enters
these values..for each student record these values get
filled in.

I want to insert all the 7 fields
Student clock number,Prog code,CoursNumber,Login
Time,Logout time,Exempt,Hours attended.

in to the tblattendance. As long as the student clock num
and the Thours fields have the correct value evrything is
fine. The students are graded according to the fact that
well they took 4 hrs to complete a 3 hr course etc. and a
number of reports run of the tblattendance.

Now since the data is coming from the tblstudents, new
fields are added and the destination table is different I
had to create a recordset clone.




When I am running the code below I am getting the
following error....

Run-time error "3265"
Item not found in this collection.

The message says...
An attempt to reference a name in a collection failed.

Possible causes:
1)the object doesn't exist in this collection. Make sure
that the object is appended to collection before
referencing it.
2)There is more than one object with this name in this
collection; using its name with this name is an ambiguous
reference. reference the object by its original position
in the collection for e.g.. recordsets(3)).

Code..........


private sub command 46_click()
Dim rs As DAO.Recordset
Dim db As DAO.Database ' for insert queries
Dim strsql As String

Set db = CurrentDb
Set rs = Me.RecordsetClone

With rs

If .RecordCount <> 0 Then .MoveFirst

Do Until .EOF

strsql = _
"INSERT INTO tblAttendance " & _
"(StudentClockNum, ProgramCode,
CourseNum, " & _
"LoginTime, Exempt, SHours) " & _
"VALUES (" & _
"'" & !studentclocknumber & "', " & _
"'" & !ProgCode & "', " & _
"'" & !CNum & "', " & _
Format(!Login, "\#mm/dd/yyyy
hh:nn:ss\#") & ", " & _
!Check54 & "," & _
!THours & ")"

' Uncomment if needed for debugging:
Debug.Print strsql

db.Execute strsql, dbFailOnError

.MoveNext
Loop

End With

Set rs = Nothing
Set db = Nothing
End Sub