(Solved) – Run-time error ‘3265’: Item cannot be found in the collection corresponding to the request

(solved)-–-run-time-error-‘3265’:-item-cannot-be-found-in-the-collection-corresponding-to-the-request

hi i have 3 tables that i want to join. Staff_Session_A, Staff_Session_B, Staff_Profile.
Staff_Session_A table is from other database named [LOCDB1] and both Staff_Session_B and Staff_Profile are from database named [LOCDB].

MYSQL = "SELECT T1.[StaffID], SUM(T1.[PtsEarned]) AS PtsErnd, T2.[FirstName], T2.[Surname], T2.[Card Type] FROM dbo.Staff_Session_A T1 "
MYSQL = MYSQL & "INNER JOIN [LOCDB].[dbo].[Staff_Profile] T2 ON T1.[StaffID]=T2.[Legacy StaffID] "
MYSQL = MYSQL & "INNER JOIN (SELECT SUM([Pts Earned]) AS TGPtsErnd, [Legacy StaffID] FROM [LOCDB].[dbo].[Staff_Session_B] WHERE (CAST([Session Date] AS DATETIME) CAST(CONVERT(datetime, CONVERT(varchar(10), [End time] / 100)  ':'   CONVERT(varchar(10), [End time] % 100)) AS TIME) between '" & TempDateFrom & "' AND '" & TempDateTo & "') GROUP BY [Legacy StaffID]) T3 ON T1.[StaffID]=T3.[Legacy StaffID] "
MYSQL = MYSQL & "WHERE (T1.[EndTime] between '" & TempDateFrom & "' AND '" & TempDateTo & "')" & TempSubSQL & " GROUP BY T1.[StaffID], T2.[FirstName], T2.[Surname], T2.[Card Type]"

Set sqlrs = New ADODB.Recordset
sqlrs.Open MYSQL, conn, adOpenKeyset, adLockReadOnly


If sqlrs.EOF Then
Else
    Do Until sqlrs.EOF = True
        Set li = Me.lvAvList.ListItems.Add(, , sqlrs.Fields("StaffID"))
            li.SubItems(1) = sqlrs.Fields("FirstName") & " " & sqlrs.Fields("Surname")
            li.SubItems(2) = sqlrs.Fields("Card Type")
            li.SubItems(3) = 0
            If IsNull(sqlrs.Fields("PtsErnd")) = True Then
                li.SubItems(3) = 0
            Else
                li.SubItems(3) = Format(sqlrs.Fields("PtsErnd"), "###,###,###,##0.#0")
            End If

            If IsNull(sqlrs.Fields("TGPtsErnd")) = True Then <-- this gives me error Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal
                li.SubItems(4) = 0
            Else
                li.SubItems(4) = Format(sqlrs.Fields("TGPtsErnd"), "###,###,###,##0.#0")
            End If
        sqlrs.MoveNext
    Loop

End If

Leave a Reply

Your email address will not be published. Required fields are marked *