Have you heard of this?

[Guest]

I originally posted this in the Forms forum. But since it also involves vba
code, I am reposting here for widest visibility.
---------------------------------------------------------------------------------------------

I have a form with several text boxes/combo boxes into which data is entered
or from which options are selected.

I have a query that selects data from a table with selection criteria based
on the data entered in the text boxes or options selected from the combo
boxes.

After filing in the text boxes and/or selecting the options in the combo
boxes, if I just go to the database window and double-click the query, it
runs perfectly. The data returned is exactly as I am expecting it to be to
include correct summed amounts.

However, if I click the "Run Query" button I have on the form, executing vba
code which determines which query to process, I get an error. I place a
breakpoint on the first line of code -- Public Sub cmdRunQuery_Click() -- and
go over to the database window and double-click the query, it returns an
error (whether I breakpoint it or let the code run, the same error is
returned).

Error statement: "This expression is typed incorrectly, or it is too complex
to be evaluated ..."

The query will run if there is no active vba running, but will return an
error if there is vba code active ... ?

Absolutely confounding! Has anyone heard of this? Any recommended solutions?

 

[Tim Ferguson]

I have a query that selects data ....

However, if I click the "Run Query" button

You don't say what line the code stops on; but if it's a DoCmd.RunSQL then
it'll fail on a SELECT query. Try a DoCmd.OpenQueryDef instead or, better,
open a form in DataSheet or Continuous Forms mode based on the query.

Aside from that, it's guess work. Best of luck


Tim F

 

[Guest]

Actually ... I stop it with a breakpoint in the vba code on the entry point.
Then I go over to the database window to run the query object (it still pulls
data from the form, though).

It isn't necessarily the OpenQuery vs. RunSQL vs. OpenQueryDef that is the
problem.

Thanx ...

 

[Guest]

Bob,

Yes I have experienced this. I have also experienced the problem that a
query which will run fine from in line code will not open (same message as
you're getting) if I try and open it in debug mode. The only way I have found
around this is to simplify the query somehow. I do this either by creating
sub-queries, or in extreme cases by an preliminary write to a temporary table
and then basing my required query on the temp table instead.

HTH,

Jon.