QueryDefs error

[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

 

[Maurice]

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.

 

[Maurice]

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

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.

 

[Alan T]

How to find the 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.

 

[Rick Brandt]

How to find the CurrentDB?

Instead of...

Set invDB = DBEngine.Workspaces(0).Databases(0)

....use...

Set invDB = CurrentDB