Runtime error 3265 Item not found on this collection (1 Viewer)

[davenport]

Hi everyone I'm new to the forum.
I'm a student working as a IT-Support for an office at my university.
I have no expericence with Microsoft Access and suddenly have to debug this error...
We had a power cut last week and there is now a problem with Microsoft-Access.

This is the code:

Sub update_Teilnehmerliste()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Teilnehmerliste")
        
    strSQL = "SELECT " & _
    " s.Nr, s.Anrede, s.Vorname," & _
    " s.Name, s.Email, s.[Matrikelnummer (Gasthörende: Bitte schreiben Sie GAST)]," & _
    " s.[Fachsemester (Gasthörende: Bitte schreiben Sie GAST)], s.[Angestrebter Abschluss]," & _
    " s.[Studiengang (Gasthörende: Bitte schreiben Sie GAST)], s.[Verwendung des Leistungsnachweises]," & _
    " s.Benotung, s.[Leistungspunkte (LP)/ECTS], s.Semester, s.Seminar, s.Dozent, s.Warteliste, s.NimmtTeil" & _
    " FROM Studenten s WHERE s.Seminar = '" & Me!Seminar.Value & "'" & _
    " AND s.Semester = '" & Me!Semester.Value & "'" & _
    " AND s.Dozent = '" & Me!Dozent.Value & "'" & _
    " ORDER BY s.NimmtTeil, s.Warteliste, IIF(s.Warteliste IS NOT NULL, s.Nr, s.Name)"
    
    qdf.SQL = strSQL
    
    Me.subform.Form.RecordSource = strSQL
    Me.subform.Form.Requery
    
    Set db = Nothing
    Set qdf = Nothing
    
    Me.refresh
    
End Sub

and the error seems to be at line "Set qdf = db.QueryDefs("Teilnehmerliste")"
As I mentioned above I have no experience with Access so I have no idea why or how did it happen, any help is much appreciated

 

[informer]

Hi Davenport

Why do you use query object especially if you assign strSQL to recordSource property?

Did you test your Sql query before assigning it to the recordsource?

 

[davenport]

hi,
I didn't write the code, someone wrote it long before I got here, so I really don't understand what it does...
can you suggest a small fix to the problem?
The thing is the code worked before, it's just after this power cut a week ago that this error appears now, so I really can't understand why

 

[arnelgp]

compact and repair your db.

 

[informer]

Hi Davenport

Considering your code

Sub update_Teilnehmerliste()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Teilnehmerliste")
        
    strSQL = "SELECT " & _
    " s.Nr, s.Anrede, s.Vorname," & _
    " s.Name, s.Email, s.[Matrikelnummer (Gasthörende: Bitte schreiben Sie GAST)]," & _
    " s.[Fachsemester (Gasthörende: Bitte schreiben Sie GAST)], s.[Angestrebter Abschluss]," & _
    " s.[Studiengang (Gasthörende: Bitte schreiben Sie GAST)], s.[Verwendung des Leistungsnachweises]," & _
    " s.Benotung, s.[Leistungspunkte (LP)/ECTS], s.Semester, s.Seminar, s.Dozent, s.Warteliste, s.NimmtTeil" & _
    " FROM Studenten s WHERE s.Seminar = '" & Me!Seminar.Value & "'" & _
    " AND s.Semester = '" & Me!Semester.Value & "'" & _
    " AND s.Dozent = '" & Me!Dozent.Value & "'" & _
    " ORDER BY s.NimmtTeil, s.Warteliste, IIF(s.Warteliste IS NOT NULL, s.Nr, s.Name)"
    
    qdf.SQL = strSQL
    
    Me.subform.Form.RecordSource = strSQL
    Me.subform.Form.Requery
    
    Set db = Nothing
    Set qdf = Nothing
    
    Me.refresh
    
End Sub

For me, the code lines below don't make any sens, so remove them

Set db = CurrentDb()
Set qdf = db.QueryDefs("Teilnehmerliste")
 ...
qdf.SQL = strSQL

Set db = Nothing
Set qdf = Nothing

Except if this query is called thereafter. So check if Teilnehmerliste query is called on other procedures in your whole project

And I'm very dubious about this part of sql script:

IIF(s.Warteliste IS NOT NULL, s.Nr, s.Name)

Did you test your Sql script? Do you know how to proceed?

 

[JHB]

...
The thing is the code worked before, it's just after this power cut a week ago that this error appears now, so I really can't understand why

Check if you've a query called "Teilnehmerliste" in your database.
If not then create a query and call it "Teilnehmerliste" or if you've a backup of your database then import the query "Teilnehmerliste".

 

[gemma-the-husky]

are you using a linked backend?

it may be that the backend has been put in a inconsistent state by the power out. Copy it first, and then try and open it.

Access will then try to repair the data.


normally, you would be aware of this at an earlier stage, but it depends how your database is launched and managed.


-----
now I read it again, I think this is less likely. Error 3265 is just that you are referring to a field that doesn't exist.

this normally occurs in an update statement, not a select.

maybe it just means that query ""Teilnehmerliste" does not exist. That would make sense, I think.