SQL Queries Dissapearing

[Spydermine]

This one's a bit weird and hard to describe. On my forms (any form) when I select the criteria and click the button to perform this line:

DoCmd.OutputTo acQuery, "ManOrVolQuery", "MicrosoftExcel(*.xls)", "\\fsss253001-w\ow\users\dsilva_k\KD LEAP Database\Test\SelectAllExcel.xls", True, ""

Once in a while (I can't see a pattern to it) it errors with this:

http://img453.imageshack.us/img453/4350/error1za2.jpg

Clicking Debug leads to this:

http://img453.imageshack.us/img453/2176/error2gd2.jpg

When I go to check the SQL statement, it's empty:

http://img453.imageshack.us/img453/9242/error6id1.jpg

Any suggestions will help, thanks in advance!

 

[Remou]

Are you building the query programatically? Have you gone through your code to check where "ManOrVolQuery" is used?

 

[Spydermine]

I'm not sure what programatically is. I assume you're asking if its built during runtime.

SELECT tblCLIENTS.*
FROM tblCLIENTS
WHERE tblCLIENTS.ManOrVol=Forms![Monthly Report]!cmbManOrVol;

The "Forms![Monthly Report]!cmbManOrVol" is a drop down box with 2 values, MAN and VOL, which gets selected during runtime.

"ManOrVolQuery" is used in the button click event of the btnManOrVol.

This problem isn't exclusive to ManOrVolQuery, it happens with all the queries. The logic and everything works fine, the sql statement just randomly dissapears -_-

 

[Remou]

When I say 'programmatically', I mean in your code, that is, have you searched for 'ManOrVol in your code to see if it is used anywhere? Also, are you using any macros?

 

[Spydermine]

It isn't used anywhere outside of the button click event. And no I'm not using macros.

 

[Remou]

Ok. I have no idea. I suppose it might be worth repairing the database, but I have never encountered a problem like this, so that is just a guess.

 

[Spydermine]

Aw man. Thanks anyway though, Hopefully someone has encountered this and knows how to deal with it.

 

[dhookom]

How about "start over from scratch" ;-)

What do you get if you open the immediate window (press Ctrl+G) and enter

?CurrentDb.QueryDefs("ManOrVolQuery").SQL

You might need a reference to the DAO object library.

Rather than using a parameter/criteria in the query, I would consider writing a little DAO code to update the SQL property of the saved query. Then you know the query has some SQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Spydermine]

The immediate window with
?CurrentDb.QueryDefs("ManOrVolQuery").SQL
brought up this:

SELECT tblCLIENTS.*
FROM tblCLIENTS
WHERE tblCLIENTS.ManOrVol=Forms![Monthly Report]!cmbManOrVol;

Instead of .* it showed all the fields being retrived, but I thought I'd save post space.

I'm going to look into the DAO thing, but I'd prefer to not start from scratch -_^

 

[Spydermine]

Ok, I found a solution in case anyone else runs into this problem. It's not the best solution, and kinda weird, but it works.

I found that opening the query in access, AND THEN outputting it to excel stop the random error.

DoCmd.OpenQuery ("ReasonANDYearQuery")
DoCmd.OutputTo acOutputQuery, "ReasonANDYearQuery", acFormatXLS, "ReasonANDYearQuery.xls", True

Hope that helps ^_^