insert query for a form 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

 

[Dirk Goldgar]

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

Please post your actual code in the future, not loose transcriptions,
and tell exactly what error message you are getting (and number, if
possible). How can we tell which of the errors in your posted code are
the source of your problem, and which you created by retyping and
summarizing it?

It may be that you need something like this:

'---- start of speculative code revision ----
Private Sub Command46_Click()

Dim rs As DAO.Recordset
Dim db As DAO.Database ' for insert queries

Set db = CurrentDb
Set rs = Me.RecordsetClone

With rs

If .RecordCount <> 0 Then .MoveFirst

Do Until .EOF

db.Execute _
"Insert into tblattendance " & _
"(studentclockNum, ProgramCode, CourseNum, " & _
"LoginTime, Exempt, Shours) " & _
"Values(" & _
!studentclocknumber & ", " & _
!progcode & ", " & _
!cnum & ", " & _
Format(!login, "\#mm/dd/yyyy hh:nn:ss\#") & ", " & _
!exemptall & ", " & _
!thours & ")",
dbFailOnError

.MoveNext
Loop

End With

Set rs = Nothing
Set db = Nothing

End Sub
'---- end of speculative code revision ----

Since I don't know the field types involved, the code above assumes that
they are all numeric with the exception of LoginTime, which I've assumed
is a date/time field. If any of the fields is text, you'll need to
modify the code that builds the SQL string to embed quotes in he string
surrounding the value of that field. I'm also not sure if the format
I've imposed on the LoginTime field is correct, but it's most reliable
to format date values into the US mm/dd/yyyy format to prevent
misinterpretations.

 

[bob]

Thanks Dirk..

My error appears when I use the insert statement..it says
it is not right Syntax.

If I use your code and have 4 text values in
progcode,coursenum,studentclocknum and exempt....Exempt is
a checkbox..is that text or numeric?

How would the code you have me change then?

 

[Dirk Goldgar]

Thanks Dirk..

My error appears when I use the insert statement..it says
it is not right Syntax.

That's not the exact error message. Next time, please post the exact
error message. It matters, in tracking down bugs.

If I use your code and have 4 text values in
progcode,coursenum,studentclocknum and exempt....Exempt is
a checkbox..is that text or numeric?

How would the code you have me change then?

I don't understand; how is Exempt *both* a "text value" and a check
box? Normally, a check box is bound to a yes/no (also called "boolean")
field in the table. The significant issue here is the field type as
established in the table design. Bear in mind also that, technically,
what you have in your table are *fields*, while what you have on your
form are *controls* (which may be bound to fields). Sometimes that's an
important distinction, sometimes not.

Assuming that the fields ProgramCode, CourseNum, and StudentClockNum are
all defined as text fields in tblAttendance, and Exempt is a yes/no
field, the code might look like this:

'---- start of code ----
Private Sub Command46_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\#") & ", " & _
!exemptall & ", " & _
!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
'---- end of code ----

Do you see how I've inserted single-quotes (') around the values for the
text fields? I also changed it around slightly to make it easier to
debug, in case this version also gets an error. If it does,
"un-comment" the line "debug.print strSQL", then run it again and press
Ctrl+G to see in the Immediate Window exactly what SQL we attempted to
execute. If necessary, copy and paste that into a reply message.

 

[bob]

Thanks again Dirk.

The code seems to work except that it is coimg up with the
error

runtime error'3265'
item not found in this collection.

and the code from the Strsql...onwards till the Thours...

is shown yellow and the arrow points to T hours.

 

[Dirk Goldgar]

Thanks again Dirk.

The code seems to work except that it is coimg up with the
error

runtime error'3265'
item not found in this collection.

and the code from the Strsql...onwards till the Thours...

is shown yellow and the arrow points to T hours.

That sounds pretty self-explanatory, doesn't it? It appears that one of
the fields you're trying to pull from the form's recordsetclone is
misspelled. Check the form's recordsource to see what the actual field
names are. Unless ... none of those so-called "fields" is a calculated
control, is it? That would gum up the works.

 

[bob]

Yes that is a calculated control.

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


That sounds pretty self-explanatory, doesn't it? It appears that one of
the fields you're trying to pull from the form's recordsetclone is
misspelled. Check the form's recordsource to see what the actual field
names are. Unless ... none of those so-called "fields" is a calculated
control, is it? That would gum up the works.

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

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Yes that is a calculated control.

If it is a calculated control, then it doesn't exist in the form's
recordset. What *are* the fields in the form's recordset, and what is
the ControlSource expression for THours? If its an expression derived
solely from fields in the recordset, then you can still do this by
substituting the expression (suitably adjusted for proper reference
syntax). If not, then you can't do it using the form's recordsetclone.

 

[bob]

Thanks Again Dirk.

See below....

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


If it is a calculated control, then it doesn't exist in the form's
recordset.

A query is calculating the value "on the fly" from the
tblstudents whic maintains the schedule information for
the students.

What *are* the fields in the form's recordset, and what
is

the ControlSource expression for THours? If its an expression derived
solely from fields in the recordset,

Can I not use an another nex field called New_THours and
get that field populated by the values from th Thours
field.

then you can still do this by

substituting the expression (suitably adjusted for proper reference
syntax). If not, then you can't do it using the form's

recordsetclone.


If I cannot do it using the recordsetclone what else can I
do?

 

[Dirk Goldgar]

(see comments and questions interspersed)

Thanks Again Dirk.

See below....



A query is calculating the value "on the fly" from the
tblstudents whic maintains the schedule information for
the students.

Wait a minute. If your form is based on this query, and it's the query
that is calculating the value which is displayed in a control on the
form, then it is *not* a calculated control. It's a bound control, that
happens to be bound to a calculated *field*. This is a crucial
distinction. Which is it? Please answer my questions, or I will be
unable to help you.

1. What is the name of the control containing this "THours" value
(wherever it comes from)?

2. What is the ControlSource of this control?

3. What is the SQL of the form's RecordSource?

If I cannot do it using the recordsetclone what else can I
do?

If you'll answer my questions we may be able to figure it out.

 

[bob]

Thanks again Dirk.


See below....


1)The name is Thours

2) Controlsource is Thours

3) SELECT tblStudents.StudentClockNum,
tblStudents.StudentName, tblStudents.StudentShift,
tblStudents.StudentCraft, tblStudents.StudentDepartment,
tblStudents.ForemanClockNum, tblStudents.SchedHours,
tblStudents.SchedSessions, tblStudents.Current,
tblstudents.schedhours*tblstudents.schedsessions AS
THOURS, tblStudents.StartDate
FROM tblStudents
WHERE (((tblStudents.Current)=Yes));

 

[Dirk Goldgar]

Thanks again Dirk.


See below....


1)The name is Thours

2) Controlsource is Thours

3) SELECT tblStudents.StudentClockNum,
tblStudents.StudentName, tblStudents.StudentShift,
tblStudents.StudentCraft, tblStudents.StudentDepartment,
tblStudents.ForemanClockNum, tblStudents.SchedHours,
tblStudents.SchedSessions, tblStudents.Current,
tblstudents.schedhours*tblstudents.schedsessions AS
THOURS, tblStudents.StartDate
FROM tblStudents
WHERE (((tblStudents.Current)=Yes));

Aha! The problem isn't the THOURS field; it's every *other* field!
You originally gave me these field names:

studentclocknumber
progcode
cnum
login
exemptall
thours

but most of those aren't the names of fields in your form's
recordsource. It's not just a matter of incorrect names, either -- the
only one I can map besides THOURS is studentclocknumber (=
StudentClockNum). So where are all these other fields supposed to come
from? They aren't in your form's recordsource, as far as I can see. Is
there a subform involved somewhere?

I think it's time to step back a bit. Why don't you describe what
you're doing with this form, what are the tables involved in this
process and how they are related, why you set out to write this code in
the first place, and how you envision it working. Right now, I have no
idea how to proceed with this.

 

[bob]

Thanks again Dirk.

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.

 

[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