access 2007 bug - intermittent query parameter prompts

views:

answers:

access 2007 bug - intermittent query parameter prompts

I am working on an Access 2007 application that was created by someone else. It has a strange, intermittent bug in which it prompts the user for query parameters when the main form is opened. The query parameters are clearly not necessary, because the error does not always occur.

The very strange "fix" to this problem is to open and close a particular module before opening the main form. Then the form opens without parameter prompts. However, of course I can't ask end users to open and close modules.

I tried using a macro to open and close the module when the database is opened. That fixes the bug, but leaves the VBA code window open, so that's no good.

Has anyone run into anything like this before? Any suggested solutions, workarounds, debugging tips, etc?

Have a look for global variables in the module that needs to be opened and closed or any variable that is referenced in the module belonging to the form.

Remou 2010-07-26 18:24:00

??? I don't believe inaccessible variables ever produce a parameter prompt. They'd only give you a compile error.

David-W-Fenton 2010-07-28 19:48:59

The OP says "the solution is to open and close a particular module" this points to a variable being defined in that module. It could be used in a way that leads to a missing parameter.

Remou 2010-07-28 19:57:07

I am still not seeing how a module could lead to a parameter prompt, unless the module has a function in it that is not declared publicly. SQL statements cannot access variables directly, so I just don't see the scope of public variables as being the cause of parameter prompts.

David-W-Fenton 2010-07-30 21:31:38

It seems to me that opening a module would correct the problem only if the module contained a global variable. Say, just for kicks, it contained the name of a field to include that was improperly named in another module.

Remou 2010-07-30 22:02:04

I think opening the module is likely misidentified as the solution. The only thing in a module that could be accesible from a query is a public function or a public function that uses a variable from that module. But VBA will load the module with the public variable definition as soon as a function needs it. In fact, post-2000, I think the whole VBA project is loaded in the first place, so I just don't see that as a plausible reason why things would work. The OP doesn't seem interested in providing more details, so we may never know.

David-W-Fenton 2010-08-02 00:38:03

The prompt occurs when a form is opened, if the form uses an SQL string for the record source with an incorrectly named field, you will get a parameter prompt.

Remou 2010-08-02 07:53:04

Well, sure. That's why everybody has asked for the specifics of the prompt. But the OP seems to have lost interest, so we don't know anything at all. I just don't see any way for a global variable to be the cause of a parameter prompt. Then again, maybe it's not a parameter prompt at all, but some other error message.

David-W-Fenton 2010-08-02 19:47:28

@Remou I searched within the module that needs to be opened and closed for the name of the combo box that appears in the parameter prompt. However, it was not found. Are there other variables that I should search for? If so, please be as specific as you can. Thanks.

LCountee 2010-08-13 13:25:46

Are there any variables defined at the top of the module?

Remou 2010-08-13 14:50:48

Yes - it implements an interface module and then declares some variables, but they are all private in scope.

LCountee 2010-08-13 15:05:45

So does the interface module have anything relevant in it?

Remou 2010-08-13 15:13:22

I don't think so - all of the variables it declares are private also.

LCountee 2010-08-13 15:17:57

Forgive me, I do not know how familiar you are with VBA, but does it implement something that is used or referenced on the form? http://msdn.microsoft.com/en-us/library/aa165323(office.10).aspx

Remou 2010-08-13 15:26:32

Ha ha, it's funny, I was just reading through that exact page. I am not very familiar with VBA. I am a C# .NET web developer being dragged into this Access VBA project. But, I am learning a lot. I will figure out the answer to your question and comment again.

LCountee 2010-08-13 16:06:20

No, I don't see any variables in the interface, or the module that implements the interface, that are used or referenced in the main form. It is really weird that opening and closing the module that implements the interface prevents the parameter prompts from appearing. Basically, the interface sets up functions to update Excel files and move them to different file locations, and updates a database to display file names within different subforms.

LCountee 2010-08-13 20:27:15

I give up :( sorry. If you want to post parts of the mdb for downloading, I will look at it, but probably to no better effect.

Remou 2010-08-13 21:01:51

+1 A:

If you use the "Database Documenter" feature and check "yes" to all the options, you will obtain an exhaustive report that should let you trap your problem parameter. Export this report as an .rtf or .pdf document, so it is searchable. Identify a keyword from the dialog prompt, and search on that.

Once you check the query objects using the Documenter, check your VBA code. You'll do this by stepping through code in the IDE. If the main form has subforms, they are opened with (within) the main form. And they load before the main form.

Identify those subforms.
Sprinkle breakpoints in their code modules (if you find a Load function, that is highly relevant).
If the main form has a code module, do the same there.

Smandoli 2010-08-13 00:43:11

I did this and searched on the name of the combo box from the prompt dialog. It appears in nine queries. It also appears in the code for the main form and one of the subforms. Not sure how to proceed from here. What should I be looking for?

LCountee 2010-08-13 15:32:26

Sometimes a temporary parameter gets "stuck" in a query -- something casual, like a sort, fails to clear itself and then makes things hang. The Documenter will help you find that -- then you can open the query and remove it. A more likely case is that code in your forms is being called in some manner you don't expect. (A subform is loaded before the parent form, which can make for interesting behavior.) You know how to set a breakpoint, I trust?

Smandoli 2010-08-13 22:25:03

I will look at the queries more closely. Yes, I know how to set a breakpoint - is there something in particular I should be checking? The main form is opened by clicking a button with an embedded macro. All that macro does is open the main form. So I don't see how any subforms could be loading first. Is there a way to verify that?

LCountee 2010-08-16 14:44:19

@ 'I will look at the queries more closely' -- Don't attempt this through the Access interface; rather, examine those 9 search results from the Documenter. Access stashes query parameters in some odd locations, but Documenter exposes all of them.

Smandoli 2010-08-16 15:39:30

ansaurus

tags:

views:

answers:

access 2007 bug - intermittent query parameter prompts

related questions