Error message

[PaulA]

So what else is new?

I am getting the error message

"Item not found in this collection" when running the following code.

Dim db As Database
Dim rstStaff As Recordset
Dim rstStaff As Recordset
Dim rstSubj As Recordset
Dim staffname As String
Dim dept As String
Dim startdate As Date
Dim test As String

Set db = CurrentDb
Set rstTrainLog = db.OpenRecordset("qryTrainingLog-StaffOverview")
Set rstStaff = db.OpenRecordset("qryEmployeeList")
Set rstSubj = db.OpenRecordset("SELECT * " & _
"FROM tblTrainingSubjectList WHERE (((tblTrainingSubjectList.Retired)=No)) ORDER BY TrainingSubjectName")
With rstStaff
.MoveFirst
Do While Not rstStaff.EOF
staffname = ![Employee_Name]
dept = ![Department]
startdate = ![Start_Date]


test = staffname & ", " & dept & ", " & startdate

Debug.Print test
.MoveNext

Loop

The knowledge base does not describe it. Any ideas what is causing this? It is generated through the function error handler.

I use A2K referencing DAO 3.6 objects.

Thanks.

Paul

 

[Travis]

Dim db As Database 
Dim rstStaff As Recordset 
Dim rstStaff As Recordset 
Dim rstSubj As Recordset 
Dim staffname As String 
Dim dept As String 
Dim startdate As Date 
Dim test As String 

  Set db = CurrentDb 
  Set rstTrainLog = db.OpenRecordset("qryTrainingLog-  staffOverview") 
  Set rstStaff = db.OpenRecordset("qryEmployeeList") 
  Set rstSubj = db.OpenRecordset("SELECT * " & _ 
        "FROM tblTrainingSubjectList WHERE (((tblTrainingSubjectList.Retired)=No)) ORDER BY TrainingSubjectName") 

  With rstStaff 
     .MoveFirst 
     Do While Not rstStaff.EOF 

       [b]Make sure that these field names exist in rstStaff[/b]

       staffname = ![Employee_Name] 
       dept = ![Department] 
       startdate = ![Start_Date] 

       test = staffname & ", " & dept & ", " & startdate 

       Debug.Print test 
       .MoveNext 
    Loop 

  End With

 

[PaulA]

Thanks for your response.

They do but they are from different tables that are joined for the query. Would that make a difference?

 

[casey]

If multiples of any of the following fields exist in the query from different tables:
![Employee_Name]
![[Department]
![Start_Date]

they will be deliniated by the table name. You must then include the table which they refer to in front of the field name.
(i.e. [tblTableName.Employee_Name] or [tblTableName.Department] or [tblTableName.Start_Date])

otherwise you could be referring to multiple fields when declaring a reference.