Broken Queries: can't ref controls or functions

[kiln]

I'm really having some strange problems with an Access 2000 sp3 db; a db
I'd regard as being fairly simply by my standards. I have long exp with
Access; I've never seen any behaviors like what I'm about to report,
that I can remember.

Basically, queries in this db, or some queries, have a hard time
referencing controls on forms for param values, or getting the values
from workarounds like functions referencing global arrays. The problem
is much more general than I first expected.

For instance, here is a test sql string from a saved query:

SELECT tblContact.ContID, [forms]![frmMap]![txtAxisX] AS x, [forms]!
[frmMap]![txtAxisY] AS y, [forms]![frmOrganization]![cboOrgID] AS xorg,
[forms]![frmContact]![cboContID] AS xcont
FROM tblContact;

(The ref to tblContact is only there to get some rows to show up, it's
not used; trust me it can be ignored, all queries that utilize the refs
fail, this is for example only.)

With the sql above, with the two forms open to records, both refs to the
combos return a  (square) character. If I wrap the form refs with clng
() or cstr(), I get the expected long value (1, 2, etc). I've never ever
seen such behavior, the combos are unbound, completely typical of
hundreds of such nav combos I've implemented. That's one oddity.

In the two other form ref cases, xorg and xcont, "normally" the expected
plain text values are returned. However, during a major code routine, I
call a query that refs these form controls, the params values returned
by the form refs are empty (and I get a 3061 Too few parameters,
expected 2). Immediately before or after the code routine the query
works fine. What I'm trying is routine, so I thought, "one of these
quirky problems that will never be understood" and devised what I
thought would be a fine workaround - well in advance of the code routine
I populate a global array with the values the query will need, then made
a function that returns the array values to the query for use as params.
But amazingly this fails with the ever useless "expression typed
incorrectly or is too complex". The function that returns the array
values is never run (or at lease pointer never arrives at a breakpoint
in the function). I can call the function in the immediate window and it
returns values as expected. Yes, it's typed correctly.

So, this is really bizzare and deadly behavior. What I've tried to
workaround besides the above:

-fully qualified the query param calls
-repaired and compacted
-decompiled
-imported all to a new db
-tried on a other pc with same patch level of Access (which is the
latest)

I hope someone else has seen these problems, which while different seem
to be related, and has a fix, thanks.

 

[Allen Browne]

The first oddity has to do with JET misinterpreting the data type of the
unbound controls. This is very common in JET 4. It helps to set the Format
property of the control to Short Date or General Number of something that
gives Access a clue as to what is expected. But the real solution is to
declare your parameters in the query. In query design view, choose
Parameters from the Query menu. In the dialog, enter one each line and give
its data type, e.g.:
[forms]![frmMap]![txtAxisX] Long
[forms]![frmContact]![cboContID] Long

The 2nd oddity has to do with the way DAO interprets your code. In the
context of the interface, Access calls the Expression Service to resolve the
Forms... references in the query parameters. In the context of the code, the
ES is not available. The simplest way to work around that is just to use
literal SQL strings in your code. That also has the advantage of making the
code independent of the queries, so there are far fewer saved queries to
manage, any changes to queries does not mess up the code, and the code
module can be copied and reused elsewhere without having to figure out what
it depends on.

If you don't like that approach, you can supply the values to the parameters
before you qdf.OpenRecordset or Execute or whatever:
Dim qdf As DAO.QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[forms]![frmMap]![txtAxisX]") =
[forms]![frmMap]![txtAxisX]
etc.

Hope that clarifies the mysteries for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm really having some strange problems with an Access 2000 sp3 db; a db
I'd regard as being fairly simply by my standards. I have long exp with
Access; I've never seen any behaviors like what I'm about to report,
that I can remember.

Basically, queries in this db, or some queries, have a hard time
referencing controls on forms for param values, or getting the values
from workarounds like functions referencing global arrays. The problem
is much more general than I first expected.

For instance, here is a test sql string from a saved query:

SELECT tblContact.ContID, [forms]![frmMap]![txtAxisX] AS x, [forms]!
[frmMap]![txtAxisY] AS y, [forms]![frmOrganization]![cboOrgID] AS xorg,
[forms]![frmContact]![cboContID] AS xcont
FROM tblContact;

(The ref to tblContact is only there to get some rows to show up, it's
not used; trust me it can be ignored, all queries that utilize the refs
fail, this is for example only.)

With the sql above, with the two forms open to records, both refs to the
combos return a  (square) character. If I wrap the form refs with clng
() or cstr(), I get the expected long value (1, 2, etc). I've never ever
seen such behavior, the combos are unbound, completely typical of
hundreds of such nav combos I've implemented. That's one oddity.

In the two other form ref cases, xorg and xcont, "normally" the expected
plain text values are returned. However, during a major code routine, I
call a query that refs these form controls, the params values returned
by the form refs are empty (and I get a 3061 Too few parameters,
expected 2). Immediately before or after the code routine the query
works fine. What I'm trying is routine, so I thought, "one of these
quirky problems that will never be understood" and devised what I
thought would be a fine workaround - well in advance of the code routine
I populate a global array with the values the query will need, then made
a function that returns the array values to the query for use as params.
But amazingly this fails with the ever useless "expression typed
incorrectly or is too complex". The function that returns the array
values is never run (or at lease pointer never arrives at a breakpoint
in the function). I can call the function in the immediate window and it
returns values as expected. Yes, it's typed correctly.

So, this is really bizzare and deadly behavior. What I've tried to
workaround besides the above:

-fully qualified the query param calls
-repaired and compacted
-decompiled
-imported all to a new db
-tried on a other pc with same patch level of Access (which is the
latest)

I hope someone else has seen these problems, which while different seem
to be related, and has a fix, thanks.

 

[kiln]

Hi Allan, great that someone has an idea what is going on here.

I mentioned that the queries are using fully qualified parameters, just
as you've indicated, and that such didn't help (probably you missed that
detail in my tome). I've had luck with such in many cases but not here.
I've made hundreds of unbound nav combos, where the bound col is a long
value, and have never ever seen one of those square chars returned as
the value. I reported that I could still get to the long value by
wrapping the expression with clng() etc. But still, why this sudden
departure from ages old behaviors? The queries that return the square
char are simple saved queries, and the square is visible simply by
running the query, ie it's not inside a code loop or anything.

For the second issue, your idea sort of makes sense. But I'm not
convinced. I have ref'd forms so many times inside code sections, and
never had the expression service die on me. Also, as I mentioned, my
first workaround, I thought a sure thing, was to pipe the values from
the form to a global array long before any code runs; then get the
values from the array via function in the query. But that function won't
even try to run. But, at the same time that the query won't run the
function (exp too complex etc), I can use the immediate window to pull
the values out of the window painlessly.

What I don't get is that these are both rote approaches I've used
without problem for many years. Very very strange.

I've beaten both effects, so I can move the project long, but I'd like
to understand what conditions are making things fail.

Thanks again for your input!

The first oddity has to do with JET misinterpreting the data type of the
unbound controls. This is very common in JET 4. It helps to set the Format
property of the control to Short Date or General Number of something that
gives Access a clue as to what is expected. But the real solution is to
declare your parameters in the query. In query design view, choose
Parameters from the Query menu. In the dialog, enter one each line and give
its data type, e.g.:
[forms]![frmMap]![txtAxisX] Long
[forms]![frmContact]![cboContID] Long

The 2nd oddity has to do with the way DAO interprets your code. In the
context of the interface, Access calls the Expression Service to resolve the
Forms... references in the query parameters. In the context of the code, the
ES is not available. The simplest way to work around that is just to use
literal SQL strings in your code. That also has the advantage of making the
code independent of the queries, so there are far fewer saved queries to
manage, any changes to queries does not mess up the code, and the code
module can be copied and reused elsewhere without having to figure out what
it depends on.

If you don't like that approach, you can supply the values to the parameters
before you qdf.OpenRecordset or Execute or whatever:
Dim qdf As DAO.QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("MyQuery")
qdf.Parameters("[forms]![frmMap]![txtAxisX]") =
[forms]![frmMap]![txtAxisX]
etc.

Hope that clarifies the mysteries for you.

 

[david epsom dot com dot au]

In my recent experience with this problem, it went away
when I stopped using DISTINCT and changed to a table
that had only one record.

Your description of using Clng() on the cbo references
appears to match Allen's explanation that Access is having
trouble with the parameter type.

Unfortunately, my general experience with A2K is that
sometimes even explicitly setting the parameter type is
not enough, but you might want to just check that again.

For me, the "invalid data from a control reference" problem
has never been associated with any kind of problem referencing
functions. I do think that those are likely to be unrelated
problems.

(david)

I'm really having some strange problems with an Access 2000 sp3 db; a db
I'd regard as being fairly simply by my standards. I have long exp with
Access; I've never seen any behaviors like what I'm about to report,
that I can remember.

Basically, queries in this db, or some queries, have a hard time
referencing controls on forms for param values, or getting the values
from workarounds like functions referencing global arrays. The problem
is much more general than I first expected.

For instance, here is a test sql string from a saved query:

SELECT tblContact.ContID, [forms]![frmMap]![txtAxisX] AS x, [forms]!
[frmMap]![txtAxisY] AS y, [forms]![frmOrganization]![cboOrgID] AS xorg,
[forms]![frmContact]![cboContID] AS xcont
FROM tblContact;

(The ref to tblContact is only there to get some rows to show up, it's
not used; trust me it can be ignored, all queries that utilize the refs
fail, this is for example only.)

With the sql above, with the two forms open to records, both refs to the
combos return a  (square) character. If I wrap the form refs with clng
() or cstr(), I get the expected long value (1, 2, etc). I've never ever
seen such behavior, the combos are unbound, completely typical of
hundreds of such nav combos I've implemented. That's one oddity.

In the two other form ref cases, xorg and xcont, "normally" the expected
plain text values are returned. However, during a major code routine, I
call a query that refs these form controls, the params values returned
by the form refs are empty (and I get a 3061 Too few parameters,
expected 2). Immediately before or after the code routine the query
works fine. What I'm trying is routine, so I thought, "one of these
quirky problems that will never be understood" and devised what I
thought would be a fine workaround - well in advance of the code routine
I populate a global array with the values the query will need, then made
a function that returns the array values to the query for use as params.
But amazingly this fails with the ever useless "expression typed
incorrectly or is too complex". The function that returns the array
values is never run (or at lease pointer never arrives at a breakpoint
in the function). I can call the function in the immediate window and it
returns values as expected. Yes, it's typed correctly.

So, this is really bizzare and deadly behavior. What I've tried to
workaround besides the above:

-fully qualified the query param calls
-repaired and compacted
-decompiled
-imported all to a new db
-tried on a other pc with same patch level of Access (which is the
latest)

I hope someone else has seen these problems, which while different seem
to be related, and has a fix, thanks.