Cannot jump to variable definition

  • Thread starter Thread starter teddysnips
  • Start date Start date
T

teddysnips

I've been asked to take over maintenance of a database. The client
has also requested that I split the database into Front End/Back End.
I've just taken delivery of the code and I've run it and it all seems
to work well. The code contains a function to create, copy and delete
queries on the fly. For these all to be available to all users I have
taken a look at the current code to do the copying.

And I've run up against a weird problem. There is a line in the code:

DoCmd.CopyObject , TempString, acQuery, QryID

The definition for QryID is not in the function, nor in the form's
module, so I right-clicked on it and selected "Definition". This
produces a message that states:

"Cannot jump to 'QryID' because it is in the library 'C:\Projects
\ThisProject\ThisDatabase.mdb' which is not currently referenced."

Needless to say, I am working in 'C:\Projects\ThisProject
\ThisDatabase.mdb. I've looked in Tools, References and there are no
missing references. I've tried adding a reference and recompiling and
no luck. I did notice that the name of the database in the properties
pane when selecting the top node in the Project Explorer in the Code
window was Access9.mdb, but I've changed it to ThisDatabase.mdb and
still the same error.

Thoughts?

Edward
 
You can't do that with a varialbe definition. It only works when you want to
see the code behind a sub or function.
All you can do is use the seach option to find the occurance of QryID in the
code.
Looking at the code, I would guess that somewhere prior to the CopyObject
line, you will find a line that populates the variable QryID with a string
value.
 
You can't do that with a varialbe definition.  It only works when you want to
see the code behind a sub or function.
All you can do is use the seach option to find the occurance of QryID in the
code.
Looking at the code, I would guess that somewhere prior to the CopyObject
line, you will find a line that populates the variable QryID with a string
value.
Click to expand...

Actually, you're wrong! Or I haven't made myself clear.

Copy the following code into a module:

Public Sub test()

Dim str As String

DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
DoEvents
str = "hello world"

End Sub

Right click on str in the line

str = "hello world"

and select "Definition"

The cursor will more to the line

Dim str As String

and highlight "str"

Edward
 
On 2 Jun said:
What is the value of TempString and what is the value of QryID?  Is
QryID passed to the function or is it assigned a value like
        QryID = "SomeTableOrQueryName"

Here's an example from help.
DoCmd.CopyObject, "Employees Copy", acTable, "Employees"- Hide quoted text-
Click to expand...

The values are irrelevant. I'm just trying to find out where QryID is
declared. It's used in the function and elsewhere in the code behind
the form, but I can't find where it's declared. My current suspicion
is that it's actually a hidden control on the form's design surface,
and the developer hasn't prepended it with the Me keyword.

Edward
 
Sorry, I did misstate that; however, it will not show you where the variable
is populated, only where it is dimmed. But, if Option Explicit is not
declared for the module and the original developer did not Dim it explicitly,
you will still have to seach for the value assignment.
 
Let's compare your line and Access Help's example

Yours:
DoCmd.CopyObject , TempString, acQuery, QryID

It seems QryId must be "a string expression that's the valid name of
an object of the type selected by the sourceobjecttype argument."

Access Example:
DoCmd.CopyObject, "Employees Copy", acTable, "Employees"

Let's check in Northwinds;
DoCmd.CopyObject , "new query", acQuery, "Inventory" - works
DoCmd.CopyObject , "new query", acQuery, Inventory - error:
Compile error:
Expected variable or procedure, not module

Somewhere in some type library VBA has an object named Inventory and
it's a module, and VBA expects a string in the place Inventory is
being used (or a function [procedure] the returns a string, so it
can't use a module.

When I ask for the definition of Inventory it opens and shows the
Module called "Inventory", not the Query named "Inventory"

So what if it said it points to the something in the Library DB
Esmerelda. That would mean that at sometime somewhere there was a
Library DB named Library Esmerelda referenced by this DB and that the
type library still thinks this Library is still pertinent. But in your
case the Library is the DB itself. Perhaps, in days gone by, this DB
was a Library servicing another DB and VBA got confused. VBA often
gets confused. A recompile may de-confuse VBA.

But you can't do a recompile while QryId is undefined.

So what to do?

If you can see what string QryID is supposed to be, you have only to
declare QryId and point it at the string.

Then recompile. That may lead you to other problems.

If you can't see what string it is, you could comment out the line and
recompile and see where that leads and tell us.

BTW, I'm trying to imagine a situation where one would want to write a
utility to copy queries, but I can't. IMO If you have a crap db, no
amount of bandaiding will make it a good db. Start Over.
 
and select "Definition"
Click to expand...
The cursor will more to the line
Click to expand...


that will only occur if you using option explicit. also, if you do think
supposed to be prefixed by a "me.", then do so. Then, do a debug->compile
(in fact, while we at this, does debug-compile work.

Furthermore, does a global search in the code editor yield anything?
(highlight the value, and then hit ctrl-if...you see an option to search the
entire project, including forms+report code modules in one shot).

so, if your application compiles, and placing "me" in front of the value
does not, then we at a "option explicit" road crossing (or lack of option
explicit).

so, 1st, try compiling, and then try the "me."...if that don't work, then it
a stay var due to lack of option explicit.
 
Let's compare your line and Access Help's example

Yours:
DoCmd.CopyObject , TempString, acQuery, QryID

It seems QryId must be "a string expression that's the valid name of
an object of the type selected by the sourceobjecttype argument."

Access Example:
DoCmd.CopyObject, "Employees Copy", acTable, "Employees"

Let's check in Northwinds;
DoCmd.CopyObject , "new query", acQuery, "Inventory" - works
DoCmd.CopyObject , "new query", acQuery, Inventory - error:
Compile error:
Expected variable or procedure, not module

Somewhere in some type library VBA has an object named Inventory and
it's a module, and VBA expects a string in the place Inventory is
being used (or a function [procedure] the returns a string, so it
can't use a module.

When I ask for the definition of Inventory it opens and shows the
Module called "Inventory", not the Query named "Inventory"

So what if it said it points to the something in the Library DB
Esmerelda. That would mean that at sometime somewhere there was a
Library DB named Library  Esmerelda referenced by this DB and that the
type library still thinks this Library is still pertinent. But in your
case the Library is the DB itself. Perhaps, in days gone by, this DB
was a Library servicing another DB and VBA got confused. VBA often
gets confused. A recompile may de-confuse VBA.

But you can't do a recompile while QryId is undefined.

So what to do?

If you can see what string QryID is supposed to be, you have only to
declare QryId and point it at the string.

Then recompile. That may lead you to other problems.

If you can't see what string it is, you could comment out the line and
recompile and see where that leads and tell us.

BTW, I'm trying to imagine a situation where one would want to write a
utility to copy queries, but I can't. IMO If you have a crap db, no
amount of bandaiding will make it a good db. Start Over.
Click to expand...

I've found out what QryID is - it's a text box on a subform. There is
a good business reason for the utility to copy queries, believe it or
not. Thanks to all for the help.

Edward
 
Just as a matter of interest, if you can do so without revealing company
confidential information, I'd be eager to learn the good business reason for
copying queries.

Almost all valid business needs can be satisfied in multiple ways in
Access... so there might just be another way to accomplish the purpose that
is safer, or easier.

By the way, consistent use of a good naming convention might have made
finding QryID easier. "txtQryID", in my apps, would immediately be
identified as a Text Box, and "strQryID" as a string variable. A
widely-accepted naming convention in the Access world is the Reddick Naming
Convention, which you'll find documented at
http://www.xoc.net/standards/default.asp.

Larry Linson
Microsoft Office Access MVP

Let's compare your line and Access Help's example

Yours:
DoCmd.CopyObject , TempString, acQuery, QryID

It seems QryId must be "a string expression that's the valid name of
an object of the type selected by the sourceobjecttype argument."

Access Example:
DoCmd.CopyObject, "Employees Copy", acTable, "Employees"

Let's check in Northwinds;
DoCmd.CopyObject , "new query", acQuery, "Inventory" - works
DoCmd.CopyObject , "new query", acQuery, Inventory - error:
Compile error:
Expected variable or procedure, not module

Somewhere in some type library VBA has an object named Inventory and
it's a module, and VBA expects a string in the place Inventory is
being used (or a function [procedure] the returns a string, so it
can't use a module.

When I ask for the definition of Inventory it opens and shows the
Module called "Inventory", not the Query named "Inventory"

So what if it said it points to the something in the Library DB
Esmerelda. That would mean that at sometime somewhere there was a
Library DB named Library Esmerelda referenced by this DB and that the
type library still thinks this Library is still pertinent. But in your
case the Library is the DB itself. Perhaps, in days gone by, this DB
was a Library servicing another DB and VBA got confused. VBA often
gets confused. A recompile may de-confuse VBA.

But you can't do a recompile while QryId is undefined.

So what to do?

If you can see what string QryID is supposed to be, you have only to
declare QryId and point it at the string.

Then recompile. That may lead you to other problems.

If you can't see what string it is, you could comment out the line and
recompile and see where that leads and tell us.

BTW, I'm trying to imagine a situation where one would want to write a
utility to copy queries, but I can't. IMO If you have a crap db, no
amount of bandaiding will make it a good db. Start Over.
Click to expand...

I've found out what QryID is - it's a text box on a subform. There is
a good business reason for the utility to copy queries, believe it or
not. Thanks to all for the help.

Edward
 
Just as a matter of interest, if you can do so without revealing company
confidential information, I'd be eager to learn the good business reason for
copying queries.

Almost all valid business needs can be satisfied in multiple ways in
Access... so there might just be another way to accomplish the purpose that
is safer, or easier.

By the way, consistent use of a good naming convention might have made
finding QryID easier. "txtQryID", in my apps, would immediately be
identified as a Text Box, and "strQryID" as a string variable. A
widely-accepted naming convention in the Access world is the Reddick Naming
Convention, which you'll find documented athttp://www.xoc.net/standards/default.asp.
Click to expand...

You're actually preaching to the choir - as I mentioned in my original
post I've been asked to take over the maintenance of this database.
It's not feasible to rename objects as I would name them (although the
tables are named with the tbl- prefix, which is a start). As an
aside, our company has decided to move away from the naming
convention, which is a right royal PITA when using SQL Server, as
prepending all your user objects with tbl- for tables, vw- for views,
stp- or sp- for SPROCs, fn- for UDFs means that they're all in one
place in the window.

But I digress. The reason for copying queries is because the database
is used by a small number of people of differing technical ability who
wish to share information in a quick and dirty way. Let's say you
decide you want a query that shows all customers who purchased widgets
in May 2007, the application allows you to do this and to save the
query. Now let's say that you want to know who purchased widgets in
June 2007, you just copy the query, rename it, and amend it to show
the new date range.

NB: I WOULDN'T HAVE DONE IT THIS WAY!!! But I didn't develop it, and
there's no budget to back out this modus operandi and do it
differently. Actually, it's one of the very few lacunae in Access -
the ability to build queries on the fly for non-techie users in code
(it does it ok using the wizards).

Edward
 
Back
Top