item not found in this collection

T

tw

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)
 
B

Brendan Reynolds

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)
 
T

tw

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...
 
B

Brendan Reynolds

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...
 
T

tw

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

problem with query in vba 1
Item not found in this collection error 1
item not found in this collection 7
item not found in this collection 1
problem with query in vba 4
supressing "item not found" error 1
Item not found in this collection 0
name not found in this collection 1

Top