Vb error no. 3265 "Items cannot be found in this collection"

Whenever l select a record from the database l get the above error of 3265. I am using access database and l have four databases hence my four select statements. The error only occurs when records are read from the database. Below is my code.

SQLStr = "Select PlannerManager1.[LM_Fore_Name], PlannerManager1.[LM_Initial], PlannerManager1.[LM_Last_Name], PlannerManager1.[LM_OUC] from UnrecordedEstimates, PlannerManager1 where UnrecordedEstimates.[SentMailStatus] = 2 and UnrecordedEstimates.[Status] = 'K' and UnrecordedEstimates.[FIRST_NAME]= PlannerManager1.[Forename] and UnrecordedEstimates.[SURNAME_PLANNING]= PlannerManager1.[Surname] and UnrecordedEstimates.[JOB_PLNG_ETG_OUC]= PlannerManager1.[OUC]" 'Louise"
Dim dbs As Database
Dim Rs As Recordset
Dim Finalstr As String

Select Case mDBName
Case LASERDB_B 'this is the Bromley database
Set dbs = OpenDatabase("C:\Laser\Bromley\Laser(b).mdb")
Set Rs = dbs.OpenRecordset(SQLStr, dbOpenSnapshot, dbReadOnly)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
frmMail.ManagerList.Clear
While Not Rs.EOF
Finalstr = ""
Finalstr = Finalstr & Rs!LM_Last_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Initial
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Fore_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_OUC
frmMail.ManagerList.Clear
frmMail.tbsProcess.Tabs(2).Selected = True
frmMail.Frame2.ZOrder
frmMail.ManagerList.AddItem (Finalstr)
Rs.MoveNext
Wend
Else
MsgBox "No records found."
End If
Rs.Close
dbs.Close
Set Rs = Nothing
Set dbs = Nothing
Case LASERDB_C
Set dbs = OpenDatabase("C:\Laser\Croydon\Laser(c).mdb") 'this is the Croydon database
Set Rs = dbs.OpenRecordset(SQLStr, dbOpenSnapshot, dbReadOnly)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
frmMail.ManagerList.Clear
While Not Rs.EOF
Finalstr = ""
Finalstr = Finalstr & Rs!LM_Last_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Initial
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Fore_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_OUC
frmMail.ManagerList.Clear
frmMail.tbsProcess.Tabs(2).Selected = True
frmMail.Frame2.ZOrder
frmMail.ManagerList.AddItem (Finalstr)
Rs.MoveNext
Wend
Else
MsgBox "No records found."
End If
Rs.Close
dbs.Close
Set Rs = Nothing
Set dbs = Nothing
Case LASERDB_P
Set dbs = OpenDatabase("C:\Laser\Portsmouth\Laser(p).mdb") 'this is the Croydon database
Set Rs = dbs.OpenRecordset(SQLStr, dbOpenSnapshot, dbReadOnly)
If Rs.RecordCount > 0 Then
frmMail.lstMailPlanners.Clear
Rs.MoveFirst
frmMail.ManagerList.Clear
While Not Rs.EOF
Finalstr = ""
Finalstr = Finalstr & Rs!LM_Last_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Initial
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Fore_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_OUC
frmMail.tbsProcess.Tabs(2).Selected = True
frmMail.ManagerList.ZOrder
frmMail.ManagerList.AddItem (Finalstr)
frmMail.Frame2.ZOrder
Rs.MoveNext
Wend
Else
MsgBox "No records found."
End If

Rs.Close
dbs.Close
Set Rs = Nothing
Set dbs = Nothing
Case LASERDB_S
Set dbs = OpenDatabase("C:\Laser\Croydon\Laser(c).mdb") 'this is the Croydon database
Set Rs = dbs.OpenRecordset(SQLStr, dbOpenSnapshot, dbReadOnly)
If Rs.RecordCount > 0 Then
Rs.MoveFirst
frmMail.ManagerList.Clear
While Not Rs.EOF
Finalstr = ""
Finalstr = Finalstr & Rs!LM_Last_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Initial
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_Fore_Name
Finalstr = Finalstr & ","
Finalstr = Finalstr & Rs!LM_OUC
frmMail.ManagerList.Clear
frmMail.tbsProcess.Tabs(2).Selected = True
frmMail.Frame2.ZOrder
frmMail.ManagerList.AddItem (Finalstr)
Wend
Else
MsgBox "No records found."
End If
Rs.Close
dbs.Close
Set Rs = Nothing
Set dbs = Nothing
End Select

[amcrae]

The first thing to do would be to find which of the four databases are causing the problem. Then based on the error you are getting, I would say that you have misspelt a table or column name or used capitals where you shouldn't have.

[PaulLewis]

Is it on each of the OpenRecordset lines?

or is it on each of the MoveFirst lines?

Or somewhere else?

Regards
Paul Lewis

[amcrae]

to be honest I don't know, and would have to run the code to find out. As I said in my last post, try to isolate where the error is occuring. put some extra lines of code in like


Debug.Print 1
.
. some lines of existing code
.

Debug.Print 2
.
. some more existing code
.

Debug.Print 3


when it crashes see which was the last number to be printed, you will find the error after that print statement and before the next one

I relise that it is not theanswer you want, but it should help you to find the error

[PaulLewis]

But you see, I too was offerring MUSEYL some advice. hehe

We'll have to wait and see if MUSEYL is still around...Hopefully the MUSEYL has solved the problem already

Cheers
Paul Lewis

[amcrae]

ahh yes I see, sorry about that