By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,685 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,685 IT Pros & Developers. It's quick & easy.

Stored query erased to select after used 3 times

P: 3
I have a form with a listbox that I manually set the rowsource to in VBA. I have 2 filter buttons that changes the rowsource and requery the list box. This works great for a filter.. If a users choose a filter for a third time, the first stored query is erased to "select;"

I noticed this because after the third query, no results show up in the list box. When I went to the queries and I opened up my query, it was erased.

I question is how does this happen? I didn't even know VBA could store a query. My code doesn't try to 'save' a query as a stored procedure.
Feb 15 '14 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 1,240
First, I suggest you run more tests to verify it is happening as you say it is. I think there may be more to the puzzle.

Further, we're going to need to look at your VBA code.

When the user closes the form, does Access ask if you want to save the modifications to it? As if the user had been editing the form, or a query or something else?

Jim
Feb 15 '14 #2

100+
P: 294
Royunderwood, like Jim states - we need to see the VBA. Make sure you paste it within the mandatory [CODE/] blocks.

Second, set a breakpoint at the beginning of the VBA code that is triggered when you initiate the code. AfterUpdate or whatever it may be.

You can then "step through" the code line by line and analyze results. Using F8 I believe. If not it's F5.
Feb 15 '14 #3

NeoPa
Expert Mod 15k+
P: 31,769
Clearly you want help with your code Roy. For that to happen you need at least to include the code in your question.
Feb 16 '14 #4

P: 3
Thank you for a quick response. I have been searching for 3 day snow to get back to this page. I didn't book mark it. I have traced down the error to the following line of code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputQuery, DLookup("myrowsourcel", "texcel"), acFormatXLS, "Teacher-Continued-Learning.xls", True
So when I use this output to XLS, the query gets erased to
select;

Any thoughts?
Feb 20 '14 #5

NeoPa
Expert Mod 15k+
P: 31,769
Let's start with the first point. You can bookmark this page and never have to worry again about losing track of anything you've posted on Bytes :
http://bytes.com/subscription.php?do...ost&order=desc

The next bit I'm going to say as a helpful tip rather than criticism. This is new for you I understand, so expecting you to know without being told would be unreasonable. Anyway, when posting code it's always important to get the amount of code right and to write the code in as clear a way as possible. I still have work yet to do in order to determine if a single line on its own without the context of the procedure will cause a serious problem. Sometimes it's enough. Sometimes it is almost meaningless. This you need to consider before posting (In this case, after checking through, this one line seems plenty).

You may (or may not) know about formatting code to be readable or about named parameters. Both help when posting code. One long line of code that wraps to a new line and with numerous parameters leaves a lot of work just to determine what the question is. Remember, it's generally down to the poster to express the question. Experts will try to find solutions for you, but would normally prefer not to have to determine the question part too.

If you can remember those points in future posts all will work better :-)

Your code, for those that are interested, could have been written as (See how much easier that is to understand and work with.) :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo ObjectType:=acOutputQuery _
  2.              , ObjectName:=DLookup("myrowsourcel", "texcel") _
  3.              , OutputFormat:=acFormatXLS _
  4.              , OutputFile:="Teacher-Continued-Learning.xls" _
  5.              , AutoStart:=True
Now, the nitty-gritty. If I understand you correctly the problem is that when you run this code for the 2nd or 3rd time with the same ObjectName parameter the QueryDef referred to (IE. the one with the name that matches the returned value of DLookup("myrowsourcel", "texcel")) gets updated to a SQL value of "SELECT;". This is weird. I've done a little research and not found anything that refers to this strange behaviour.

All of which boils down to the fact that, at this time at least, I can't help you - I'm sorry to say.
Feb 20 '14 #6

P: 3
Neopa,

The only code I have linked to that button is the one line I posted in the question, therefore I am unable to post any other code.

I have been able to resolve my problem.

It seems with a bit of googling, there are several people who have experienced this very same issue.

The Following CODE will sometimes erase your stored query
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acQuery, "{Put Query Name Here}", "MicrosoftExcel(*.xls)", "{Put Excel File Name Here}", True, ""
  2.  
Ive switched to using this: (and it works great)
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, 3, "[query name here]","Newemps.wk3", True, "A1:G12"
  2.  
Feb 20 '14 #7

100+
P: 294
Glad you got it working.
Feb 20 '14 #8

NeoPa
Expert Mod 15k+
P: 31,769
I'm pleased you got it working. I admit I spent a fair while looking but couldn't find anything on it. I usually use the TransferSpreadsheet() method myself, and from my experience of answering questions, most others do too. That's probably why there's (relatively) so little out there on it. Well done for finding what you did. I'm much better at Access than I am at finding things by searching. I get by, but I'm no expert.

As a quick aside, referring to your comment about posting the one line. I agree (and did so in my earlier post you may notice) that in this case a single line was all that was necessary, although generally it helps to see the procedure header as well (In this case it would not have added anything). I left the comment in because it's a valid point that many other readers could benefit from understanding.

As a last comment, we appreciate that you made the effort to come back and post the solution you found. Not everybody does that and it's really helpful. We all learn that way and the many members of the public that find the thread can go away with a solution, rather than simply finding another thread with the same question but no solution.
Feb 21 '14 #9

Post your reply

Sign in to post your reply or Sign up for a free account.