QueryDefs error

  • Thread starter Thread starter Alan T
  • Start date Start date
A

Alan T

I got this runtime error in the form's code:
Item not found in this collection.

These are my code:
Private Function InitialiseNewInvoiceItems(InvoiceID As Long)
Dim invDB As Database
Dim qryInitialiseInvoiceItems As QueryDef
........
.......


Set invDB = DBEngine.Workspaces(0).Databases(0)
Set qryInitialiseInvoiceItems =
invDB.QueryDefs("qryInitialiseNewInvoiceItems") <----- error's here
....
....
End Function

I checked I have created a query has the same name
"qryInitialiseNewInvoiceItems".
Why it can't find it?

Thanks
 
What are you putting in the querydef? Your are referencing it correct but you
have to fill it with something like:

invDB.QueryDefs("qryInitialiseNewInvoiceItems").SQL= [you fill it with this]

After this point the query "qryInitialiseNewInvoiceItems" is filled with
your source.
The querydef is a defenition which you fill with a source which you have
designed first.

So in the above statement you create a SQL-statement which you supply to the
querydef. This will be placed in your predefined query.
 
And the error message is indicating that the qeury you are referencing which
is "qryInitialiseNewInvoiceItems" doesn't exists...

So first create the "qryInitialiseNewInvoiceItems" save the query and then
fill it appropriately
 
Alan said:
I got this runtime error in the form's code:
Item not found in this collection.

These are my code:
Private Function InitialiseNewInvoiceItems(InvoiceID As Long)
Dim invDB As Database
Dim qryInitialiseInvoiceItems As QueryDef
........
.......


Set invDB = DBEngine.Workspaces(0).Databases(0)
Set qryInitialiseInvoiceItems =
invDB.QueryDefs("qryInitialiseNewInvoiceItems") <----- error's here
....
....
End Function

I checked I have created a query has the same name
"qryInitialiseNewInvoiceItems".
Why it can't find it?

Thanks
Click to expand...

Looks okay to me (assuming you don't have a spelling error in the query name).

There are particular circumstances where Workspaces(0).Databases(0) will not
point to the database object that you expect it to. Do you get the same error
with CurrentDB?
 
Hi,
I have created the query "InitialiseNewInvoiceItems" and checked the
spelling is correct.
And also filled in the SQL statement at design time.

The strange thing is I use the same format in another sub function has no
problem:
Set invDB = DBEngine.Workspaces(0).Databases(0)
Set qryXYZ = invDB.QueryDefs("qryABC")

I scratched my head for quite a while.
 
How to find the CurrentDB?

Alan T said:
Hi,
I have created the query "InitialiseNewInvoiceItems" and checked the
spelling is correct.
And also filled in the SQL statement at design time.

The strange thing is I use the same format in another sub function has no
problem:
Set invDB = DBEngine.Workspaces(0).Databases(0)
Set qryXYZ = invDB.QueryDefs("qryABC")

I scratched my head for quite a while.
Click to expand...
 
You must log in or register to reply here.

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

Concencate String from items in Multiselect List 5
Querydef Timeout 9
Invalid Operation on a querydef 2
Lookup Mod Code Question 4
Combo Box / Query 7
QueryDef vs. Recordset Errors 11
visual studio 2013 database connection 0
QueryDef Headache 5

Back
Top