Load from Query via VBA

[Paul D. Simon]

I created a filter in the Advanced Filter/Sort grid, then did Save as
Query, naming the query "Discs Acquired". I'm now trying to write code
that will open the Advanced Filter/Sort grid, do Load From Query
(choosing the "Discs Acquired" query), and then do Apply Filter.

The following code does not work because acCmdLoadFromQuery apparently
does not accept parameters (as best as I can tell):

DoCmd.RunCommand acCmdAdvancedFilterSort
DoCmd.RunCommand acCmdLoadFromQuery, "Discs Acquired"
DoCmd.RunCommand acCmdApplyFilterSort


(I also tried the following line, but it failed as well
DoCmd.ApplyFilter "Discs Acquired", ""


If I take the errant "Discs Acquired" parameter out of the
acCmdLoadFromQuery command, the program runs fine, except that a dialog
box entitled Applicable Filter comes up from which the user must pick
"Discs Acquired" from the list.

Is there any way that I can get Load From Query to automatically load
"Discs Acquired" to avoid the extra step of having to choose it from
the list in the Applicable Filter dialog box?

Many thanks,
Paul D. Simon

 

[Dirk Goldgar]

I created a filter in the Advanced Filter/Sort grid, then did Save as
Query, naming the query "Discs Acquired". I'm now trying to write
code that will open the Advanced Filter/Sort grid, do Load From Query
(choosing the "Discs Acquired" query), and then do Apply Filter.

The following code does not work because acCmdLoadFromQuery apparently
does not accept parameters (as best as I can tell):

DoCmd.RunCommand acCmdAdvancedFilterSort
DoCmd.RunCommand acCmdLoadFromQuery, "Discs Acquired"
DoCmd.RunCommand acCmdApplyFilterSort


(I also tried the following line, but it failed as well
DoCmd.ApplyFilter "Discs Acquired", ""


If I take the errant "Discs Acquired" parameter out of the
acCmdLoadFromQuery command, the program runs fine, except that a
dialog box entitled Applicable Filter comes up from which the user
must pick "Discs Acquired" from the list.

Is there any way that I can get Load From Query to automatically load
"Discs Acquired" to avoid the extra step of having to choose it from
the list in the Applicable Filter dialog box?

I'm curious as to why this attempt ...

DoCmd.ApplyFilter "Discs Acquired", ""

or just this ...

DoCmd.ApplyFilter "Discs Acquired"

.... failed. It seems to me like the right way to do it, and it works
for me with a filter I created the same way. In what way did it fail?
Did you get an error message? If so, what was it?

 

[Paul D. Simon]

Thanks very much for responding, Dirk. Here's what happens. A dialog
box comes up telling me that "The command or action isn't available
now".

As best as I can tell, for some reason my macro stalls in the query
grid screen from which DoCmd.ApplyFilter won't work. Yet
DoCmd.RunCommand acCmdLoadFromQuery will exit that screen, but then
brings up the dialog box I mentioned above.

The thing is, I'm not trying to open the query grid screen. I don't
even want to see that screen. I just want to load the query and run
it. So I don't know why it goes into that screen in the first place.
I have DoCmd.Echo False, "" as the first line, but I guess that's not
enough to avoid it.

Unfortunately, I'm an Access novice, so I'm trying to feel my way
through all this.