Can't find syntax to reference recordset values

[Laurel]

I have a select statement in my code that references a query. My problem is
that I can't find any syntax that will allow me to get at the Student_id and
Att_Date values. Everything I've tried results in the error "Item not
found in collection." If I try various forms in the Immediate window
(print irst_students.tblattendance.student_id", etc., the error is -
"Compile Error - Method or data member not found."
Student_ID must appear in both the ls_sql Select and in
qryGradesForAbsentStudents, because, as you see, I'm testing for null values
in qryGradesForAbsentStudents.

There are two rows in the recordset.

I've tried irst_Students!tblAttendance.Student_id,
irst_Students![tblAttendance].[Student_id],
irst_students![tblattendance.student_id]. HELP!

This is my code for opening the recordset.
Set irst_Students = CurrentDb.OpenRecordset(ls_sql)

This is the value of ls_sql:

SELECT tblAttendance.Student_id, tblAttendance.Att_Date,
qryGradesForAbsentStudents.*, fncAbsent([tblattendance].[student_id]) AS
Expr1 FROM tblAttendance LEFT JOIN qryGradesForAbsentStudents ON
(tblAttendance.Att_Date = qryGradesForAbsentStudents.Assessment_Date) AND
(tblAttendance.Student_ID =
qryGradesForAbsentStudents.tblStudents.Student_id) WHERE
(((fncAbsent([tblattendance].[student_id]))=True) and
(qryGradesForAbsentStudents.tblAcademics.Student_id is null)) ORDER BY
qryGradesForAbsentStudents.Last_Name, qryGradesForAbsentStudents.First_Name

This is the syntax for qryGradesForAbsentStudents

SELECT tblAcademics.*, [last_name] & ", " & [first_name] AS Expr1,
tblStudents.*
FROM tblAcademics INNER JOIN tblStudents ON tblAcademics.Student_id =
tblStudents.Student_ID
WHERE (((tblAcademics.Subject)='Literacy') AND
((tblStudents.Class_Code)='Test'))
ORDER BY [last_name] & ", " & [first_name];

 

[Ken Snell \(MVP\)]

Try

irst_Students![tblAttendance.Student_id]

or

irst_Students.Fields("tblAttendance.Student_id")

 

[Marshall Barton]

I have a select statement in my code that references a query. My problem is
that I can't find any syntax that will allow me to get at the Student_id and
Att_Date values. Everything I've tried results in the error "Item not
found in collection." If I try various forms in the Immediate window
(print irst_students.tblattendance.student_id", etc., the error is -
"Compile Error - Method or data member not found."
Student_ID must appear in both the ls_sql Select and in
qryGradesForAbsentStudents, because, as you see, I'm testing for null values
in qryGradesForAbsentStudents.

There are two rows in the recordset.

I've tried irst_Students!tblAttendance.Student_id,
irst_Students![tblAttendance].[Student_id],
irst_students![tblattendance.student_id]. HELP!

This is my code for opening the recordset.
Set irst_Students = CurrentDb.OpenRecordset(ls_sql)

This is the value of ls_sql:

SELECT tblAttendance.Student_id, tblAttendance.Att_Date,
qryGradesForAbsentStudents.*, fncAbsent([tblattendance].[student_id]) AS
Expr1 FROM tblAttendance LEFT JOIN qryGradesForAbsentStudents ON
(tblAttendance.Att_Date = qryGradesForAbsentStudents.Assessment_Date) AND
(tblAttendance.Student_ID =
qryGradesForAbsentStudents.tblStudents.Student_id) WHERE
(((fncAbsent([tblattendance].[student_id]))=True) and
(qryGradesForAbsentStudents.tblAcademics.Student_id is null)) ORDER BY
qryGradesForAbsentStudents.Last_Name, qryGradesForAbsentStudents.First_Name

This is the syntax for qryGradesForAbsentStudents

SELECT tblAcademics.*, [last_name] & ", " & [first_name] AS Expr1,
tblStudents.*
FROM tblAcademics INNER JOIN tblStudents ON tblAcademics.Student_id =
tblStudents.Student_ID
WHERE (((tblAcademics.Subject)='Literacy') AND
((tblStudents.Class_Code)='Test'))
ORDER BY [last_name] & ", " & [first_name];

You should list the individal fields in the select caluse
instead of using table.*

This way, you can either eliminate the duplicate field or
alias one of them so a field name is not ambiguous.