item not found in this collection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

strSQLc = "Select * From [WorkSpaceDoctorsOrders]"
'next statement generates error "item not found in this collection"
Set rsc = CurrentDb.Recordsets(strSQLc)

Following are excerpts of my code, why is WorkSpaceDoctorsOrders suddenly
not found, when I've used it throughout this procedure, and how can I fix it?
I have properly declared strsqlc and rsc and the beginning of this code. I
am using these vars to open another query to loop through, and it is closed
prior to setting up this statement as shown in code below.

'several statements preceding this section works fine...

'this code is to delete data from a working table and it works fine
strSQLwsDO = "DELETE * From [WorkSpaceDoctorsOrders]"
DoCmd.RunSQL (strSQLwsDO)

.... more code works fine

do until rsc.eof
'... more code works fine to determine following sql vars
strSQLi = "Insert into WorkSpaceDoctorsOrders("
strSQLi = strSQLi & "[DO-FK Cert Period id], [DO-FK Visit Code], "
strSQLi = strSQLi & "[DO Occurrences], [DO-FK Type of Visit], "
strSQLi = strSQLi & "[DO Number Of Weeks], [DO Date Start Week]) "
strSQLi = strSQLi & "Values ("
strSQLi = strSQLi & stCertPeriod & ", '" & stVisitCode & "', "
strSQLi = strSQLi & stOccurrences & ",'ST',"
strSQLi = strSQLi & stWeeks & ", #" & maxDateST & "#)"

DoCmd.RunSQL (strSQLi)

rsc.MoveNext
Loop

rsc.Close
Set rsc = Nothing
Set rsp = Nothing

.... all the code above works fine
.... then when I get here I have problems

strSQLc = "Select * From [WorkSpaceDoctorsOrders]"

'the next statement generates the error item not found in this collection
Set rsc = CurrentDb.Recordsets(strSQLc)
 
The message doesn't mean that VBA can't find "WorkSpaceDoctorsOrders", it
means that VBA can't find a recordset with the contents of strSQLc as its
Name property. And the reason VBA can't find it is that it will not exist
until *after* that line of code has been executed.

Change that line to ...

Set rsc = CurrentDb.OpenRecordset(strSQLc)

.... and you'll probably be OK - though it might be safer to assign the
reference to CurrentDb to a variable first ...

Dim db As DAO.Database
Set db = CurrentDb
Set rsc = db.OpenRecordset(strSQLc)
 
I made the change to Set rsc = CurrentDb.OpenRecordset(strSQLc)
now I'm getting the error message "Method or Data Member not found"

Brendan Reynolds said:
The message doesn't mean that VBA can't find "WorkSpaceDoctorsOrders", it
means that VBA can't find a recordset with the contents of strSQLc as its
Name property. And the reason VBA can't find it is that it will not exist
until *after* that line of code has been executed.

Change that line to ...

Set rsc = CurrentDb.OpenRecordset(strSQLc)

.... and you'll probably be OK - though it might be safer to assign the
reference to CurrentDb to a variable first ...

Dim db As DAO.Database
Set db = CurrentDb
Set rsc = db.OpenRecordset(strSQLc)

--
Brendan Reynolds (MVP)


tw said:
strSQLc = "Select * From [WorkSpaceDoctorsOrders]"
'next statement generates error "item not found in this collection"
Set rsc = CurrentDb.Recordsets(strSQLc)

Following are excerpts of my code, why is WorkSpaceDoctorsOrders suddenly
not found, when I've used it throughout this procedure, and how can I fix
it?
I have properly declared strsqlc and rsc and the beginning of this code.
I
am using these vars to open another query to loop through, and it is
closed
prior to setting up this statement as shown in code below.

'several statements preceding this section works fine...

'this code is to delete data from a working table and it works fine
strSQLwsDO = "DELETE * From [WorkSpaceDoctorsOrders]"
DoCmd.RunSQL (strSQLwsDO)

... more code works fine

do until rsc.eof
'... more code works fine to determine following sql vars
strSQLi = "Insert into WorkSpaceDoctorsOrders("
strSQLi = strSQLi & "[DO-FK Cert Period id], [DO-FK Visit Code], "
strSQLi = strSQLi & "[DO Occurrences], [DO-FK Type of Visit], "
strSQLi = strSQLi & "[DO Number Of Weeks], [DO Date Start Week]) "
strSQLi = strSQLi & "Values ("
strSQLi = strSQLi & stCertPeriod & ", '" & stVisitCode & "', "
strSQLi = strSQLi & stOccurrences & ",'ST',"
strSQLi = strSQLi & stWeeks & ", #" & maxDateST & "#)"

DoCmd.RunSQL (strSQLi)

rsc.MoveNext
Loop

rsc.Close
Set rsc = Nothing
Set rsp = Nothing

... all the code above works fine
... then when I get here I have problems

strSQLc = "Select * From [WorkSpaceDoctorsOrders]"

'the next statement generates the error item not found in this collection
Set rsc = CurrentDb.Recordsets(strSQLc)
Click to expand...
Click to expand...
 
I'd need to see more of the code before I could offer any help. In
particular, the lines where the variables are declared, the lines where
values are assigned to those variables, and of course the line that raises
the error.

--
Brendan Reynolds (MVP)


tw said:
I made the change to Set rsc = CurrentDb.OpenRecordset(strSQLc)
now I'm getting the error message "Method or Data Member not found"

Brendan Reynolds said:
The message doesn't mean that VBA can't find "WorkSpaceDoctorsOrders", it
means that VBA can't find a recordset with the contents of strSQLc as its
Name property. And the reason VBA can't find it is that it will not exist
until *after* that line of code has been executed.

Change that line to ...

Set rsc = CurrentDb.OpenRecordset(strSQLc)

.... and you'll probably be OK - though it might be safer to assign the
reference to CurrentDb to a variable first ...

Dim db As DAO.Database
Set db = CurrentDb
Set rsc = db.OpenRecordset(strSQLc)

--
Brendan Reynolds (MVP)


tw said:
strSQLc = "Select * From [WorkSpaceDoctorsOrders]"
'next statement generates error "item not found in this collection"
Set rsc = CurrentDb.Recordsets(strSQLc)

Following are excerpts of my code, why is WorkSpaceDoctorsOrders
suddenly
not found, when I've used it throughout this procedure, and how can I
fix
it?
I have properly declared strsqlc and rsc and the beginning of this
code.
I
am using these vars to open another query to loop through, and it is
closed
prior to setting up this statement as shown in code below.

'several statements preceding this section works fine...

'this code is to delete data from a working table and it works fine
strSQLwsDO = "DELETE * From [WorkSpaceDoctorsOrders]"
DoCmd.RunSQL (strSQLwsDO)

... more code works fine

do until rsc.eof
'... more code works fine to determine following sql vars
strSQLi = "Insert into WorkSpaceDoctorsOrders("
strSQLi = strSQLi & "[DO-FK Cert Period id], [DO-FK Visit Code], "
strSQLi = strSQLi & "[DO Occurrences], [DO-FK Type of Visit], "
strSQLi = strSQLi & "[DO Number Of Weeks], [DO Date Start Week]) "
strSQLi = strSQLi & "Values ("
strSQLi = strSQLi & stCertPeriod & ", '" & stVisitCode & "', "
strSQLi = strSQLi & stOccurrences & ",'ST',"
strSQLi = strSQLi & stWeeks & ", #" & maxDateST & "#)"

DoCmd.RunSQL (strSQLi)

rsc.MoveNext
Loop

rsc.Close
Set rsc = Nothing
Set rsp = Nothing

... all the code above works fine
... then when I get here I have problems

strSQLc = "Select * From [WorkSpaceDoctorsOrders]"

'the next statement generates the error item not found in this
collection
Set rsc = CurrentDb.Recordsets(strSQLc)
Click to expand...
Click to expand...
Click to expand...
 
thanks, I found the problem...

I had
Set rsc = CurrentDb.OpenRecordsets(strSQLc)

should have been
Set rsc = CurrentDb.OpenRecordset(strSQLc)

Brendan Reynolds said:
I'd need to see more of the code before I could offer any help. In
particular, the lines where the variables are declared, the lines where
values are assigned to those variables, and of course the line that raises
the error.

--
Brendan Reynolds (MVP)


tw said:
I made the change to Set rsc = CurrentDb.OpenRecordset(strSQLc)
now I'm getting the error message "Method or Data Member not found"

Brendan Reynolds said:
The message doesn't mean that VBA can't find "WorkSpaceDoctorsOrders", it
means that VBA can't find a recordset with the contents of strSQLc as its
Name property. And the reason VBA can't find it is that it will not exist
until *after* that line of code has been executed.

Change that line to ...

Set rsc = CurrentDb.OpenRecordset(strSQLc)

.... and you'll probably be OK - though it might be safer to assign the
reference to CurrentDb to a variable first ...

Dim db As DAO.Database
Set db = CurrentDb
Set rsc = db.OpenRecordset(strSQLc)

--
Brendan Reynolds (MVP)


strSQLc = "Select * From [WorkSpaceDoctorsOrders]"
'next statement generates error "item not found in this collection"
Set rsc = CurrentDb.Recordsets(strSQLc)

Following are excerpts of my code, why is WorkSpaceDoctorsOrders
suddenly
not found, when I've used it throughout this procedure, and how can I
fix
it?
I have properly declared strsqlc and rsc and the beginning of this
code.
I
am using these vars to open another query to loop through, and it is
closed
prior to setting up this statement as shown in code below.

'several statements preceding this section works fine...

'this code is to delete data from a working table and it works fine
strSQLwsDO = "DELETE * From [WorkSpaceDoctorsOrders]"
DoCmd.RunSQL (strSQLwsDO)

... more code works fine

do until rsc.eof
'... more code works fine to determine following sql vars
strSQLi = "Insert into WorkSpaceDoctorsOrders("
strSQLi = strSQLi & "[DO-FK Cert Period id], [DO-FK Visit Code], "
strSQLi = strSQLi & "[DO Occurrences], [DO-FK Type of Visit], "
strSQLi = strSQLi & "[DO Number Of Weeks], [DO Date Start Week]) "
strSQLi = strSQLi & "Values ("
strSQLi = strSQLi & stCertPeriod & ", '" & stVisitCode & "', "
strSQLi = strSQLi & stOccurrences & ",'ST',"
strSQLi = strSQLi & stWeeks & ", #" & maxDateST & "#)"

DoCmd.RunSQL (strSQLi)

rsc.MoveNext
Loop

rsc.Close
Set rsc = Nothing
Set rsp = Nothing

... all the code above works fine
... then when I get here I have problems

strSQLc = "Select * From [WorkSpaceDoctorsOrders]"

'the next statement generates the error item not found in this
collection
Set rsc = CurrentDb.Recordsets(strSQLc)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top