Is there something screwy going on with acCmdSave?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use code to rewrite the Filter property of a Query, and then use acCmdSave
to save that filter.

When I step through the code however, the filter sometimes reverts back to
the previous version after the save command.

For instance:

CurrentDb.QueryDefs(Me.cboReport).Properties("Filter") = cboFilters
DoCmd.RunCommand acCmdSave

Testing the filter property before I run the first line, I'll get:
(StateCode = 'CA')

Then I'll set the filter and double check it in the immediate pane, and I'll
get the string I intended to set:
(StateCode = 'IN')

Then I'll let the save command process, and I'll check it again, and it's
back to the original:
(StateCode = 'CA')

But it doesn't happen all the time! The next time I run the code it keeps
the setting that I gave it. Or not, depending on how it feels.

This is not the only screwy thing I've seen with acCmdSave. Sometimes I will
get random errors completely unrelated to anything going on or anything in
question. For instance, I've gotten "Can't find the function or macro 'Forms'
referred to in your expression" when there isn't anything I've changed that
includes the "Forms" keyword. But I know that it is a bug (or seems to be a
bug) because you can just hit F5 to pick up the code where you left off, and
it does not regenerate that error.

All in all, some screwy things going on with acCmdSave, and it doesn't
appear to be corruption. I've tried it in different db's. Has anyone else
seen this, or can they point me at information regarding it?
 
Tim said:
I use code to rewrite the Filter property of a Query, and then use
acCmdSave to save that filter.

When I step through the code however, the filter sometimes reverts
back to the previous version after the save command.

For instance:

CurrentDb.QueryDefs(Me.cboReport).Properties("Filter") = cboFilters
DoCmd.RunCommand acCmdSave

Testing the filter property before I run the first line, I'll get:
(StateCode = 'CA')

Then I'll set the filter and double check it in the immediate pane,
and I'll get the string I intended to set:
(StateCode = 'IN')

Then I'll let the save command process, and I'll check it again, and
it's back to the original:
(StateCode = 'CA')

But it doesn't happen all the time! The next time I run the code it
keeps the setting that I gave it. Or not, depending on how it feels.

This is not the only screwy thing I've seen with acCmdSave. Sometimes
I will get random errors completely unrelated to anything going on or
anything in question. For instance, I've gotten "Can't find the
function or macro 'Forms' referred to in your expression" when there
isn't anything I've changed that includes the "Forms" keyword. But I
know that it is a bug (or seems to be a bug) because you can just hit
F5 to pick up the code where you left off, and it does not regenerate
that error.

All in all, some screwy things going on with acCmdSave, and it doesn't
appear to be corruption. I've tried it in different db's. Has anyone
else seen this, or can they point me at information regarding it?
Click to expand...

I think there's something wrong with your code, or the logic behind it.
For example, in this code:
CurrentDb.QueryDefs(Me.cboReport).Properties("Filter") = cboFilters
DoCmd.RunCommand acCmdSave
Click to expand...

I see no reason why you would expect the RunCommand acCmdSave statement
to have anything to do with the assignment you just made to the stored
QueryDef named by the value of cboReport. "RunCommand acCmdSave" is
going to apply to the object that is currently active, but the
assignment via the QueryDefs collection has nothing to do with the
active object, whatever that is.

So you're probably being misled by a misconception as to what ought to
be going on. Without seeing more of your code and with no explanation
of the logic behind it, I can't say more.
 
Thanks for the response, Dirk.

I guess I was operating under the impression that the acCmdSave was a
"save-anything-that-has-changed" type of command. The subform can display
multiple different queries as the source-object (one at a time), so I needed
to save the Filter with the query before I pulled up a different one (or that
one again). I hoped that the acCmdSave command would get me by the "do you
want to save the query" message when the query is released as the
SourceObject.

What about the other odd things going on with acCmdSave? The random errors
and such?
 
Tim said:
What about the other odd things going on with acCmdSave? The random
errors and such?
Click to expand...

It's hard to say. There are two main explanations I can think of for
"random errors": (1) you may be making mistakes that raise errors in
ways that would be predictable, not random, if you understood what's
really going on; or (2) your VB project may have become corrupt.
Explanation (1) is the most common, in my experience, but (2) does
happen, and sometimes gives symptoms like those you report.

If you *do* have project corruption, you can probably cure it by
decompiling your application. You can do that in the following manner:

1. With the database closed -- no users in it at all -- and (ideally)
Access not running, make a backup copy.

2. On the task bar, click Start -> Run...

3. Enter this (adapted to match your database path and name) in the Run
dialog box and click OK:

msaccess.exe /decompile "C:\My Documents\YourDBName.mdb"

You may need to include the full path to msaccess.exe, but I don't find
that to be the case when I try it. Depending on your Access version,
you may not get any sign that anything in particular happened. Your
database will probably open in Access.

4. Compact and Repair your database.

5. Open the database again, press Alt+F11 to switch to the VB Editor,
and click Debug -> Compile (your project). If any errors appear, fix
them and recompile.

6. Close the VB Editor. Compact & Repair again.

See if the problem has disappeared.
 
Back
Top