Delete query on Switchboard

[Guest]

I'm only been using Access for one week so apologise if this is a stupid
question!

I have a database which imports data from a template in Excel and leaves
lots of empty records. To get rid of these I've created a delete query which
works perfectly if run manually. However, I would like to add this query to
the Switchboard but can't figure it out. Is it even possible?

If I look at the Switchboard Items Table in Design mode I can see that to
open/create a report the command is "4" and to open a form the command is "3"
(I originally created the Switchboard from Switchboard Manager). Is there a
command to run a query?

Thanks in advance experts...!

 

[TC]

Add a command button to your switchboard. Create the Click event
procedure for that button. Put the following line of code, in that
procedure:

docmd.openquery "ZZZZ"

where ZZZZ is the name of the delete query.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Guest]

Switchboards work by storing the data regarding their actions in a table. The
numbers refer to rows in that table. You can execute your query from a
switchboard either by running a macro or by running code. For the former you
first create a macro to execute the query, You can use the OpenQuery action
in the macro as this executes an 'action' query as well as opening a normal
Select query. To run code you'd create a public function in a standard
module with a line of code to call the OpenQuery method of the DoCmd object.

Assuming you use a macro you then simply have to select the Run Macro action
for a new switchboard button using the built in switchboard manager and enter
the name of the macro. If you use a function you select the Run Code action
and enter the function name.

In your case using the OpenQuery action should be fine and is very easy tom
set up. You will find that you get the usual confirmation dialogues. If you
want to suppress these call the SetWarnings action in the macro both before
and after the OpenQuery action, setting them off in the first case and back
on in the second.

I don't imagine it applies in your case, but for more complex update
operations executing queries in code gives you far more control. You can
detect if the query has been successful for instance. This is particularly
important where interrelated queries are being executed as you can run them
within a single Transaction and roll back the transaction if necessary, e.g.
if moving money from one bank account to another you would not want it to be
debited from one if for some reason there was an error which prevented the
application crediting it to the other!

Ken Sheridan
Stafford, England

 

[Guest]

Thanks guys I'm getting there slowly! TC - I can get this working, but I
have another question. I have a main menu on my Switchboard and then a
couple of sub menus - I only want this command button to appear on one of the
sub menus, and at present it's on all of them. Is there a way around this?

 

[Ron2005]

Not from the apporach that TC gave you. You will have to use Ken's
approach.

Create a macro
In the macro
setwarnings No
openquery and select the delete query

Now in the switchboard manager
go to the menu you want it to appear on and select runmacro for the
action and then select the recently defined delete macro.


Ron

 

[Guest]

Perfect! Thank you all so much