Assigning a Delete Query to a Button? (1 Viewer)

[DeanH]

Any idea's? I can assign a normal query to a button by using the button wizard but the delete query doesnt appear with the normal queries.

As always - any help would be great.

 

[llkhoutx]

Initially on your form in design view, create a button with the button wizard, but select Cancel when the "Command Button Wizard" opens. This leaves you a a button with no code.

To add code to the button:
1. Open the properies window for the button and
a. Give the button an appropriate name, e.g. cmdRunQuery;
b. set the caption property to an appropriate value, e.g. run Query.
2. Then close the property window.
3. Right click the button and select "Build Event," select "Code Builder" in the window that opens, then click ok.
4. Add the following code line

DoCmd.OpenQuery "YourDeleteQueryName" (include quotes)

where YourDeleteQueryName is the delete query you want to run.
5. Close this (Visual Basis) window and open your form in Normal view.
6. Clicking the button with run your query.

 

[mrbebop]

Not working for me

I tried the code, but it isn't working for me. The delete querie works but after I coded the button on the form, it didn't work. The name of the delete querie is "shpdataquerie" and the I used the code DoCmd.OpenQuery "shpdataquery" I could really use some help. Thanks and have a much good day!

 

[Peter D]

Are you getting an error message? What is it?

Try putting msgbox statements around your DoCmd.OpenQuery. Do the msgbox's display when you click the command button?

Hope this helps,

 

[dcx693]

Also, since this is an action query, do you want to have Access warn you about the records it will be deleting? If you use the DoCmd.OpenQuery method to run an action query, Access will display warning messages. You can either turn off the warning messages using DoCmd.SetWarnings = False or using Currentdb.Execute "queryname" to run the query without having to turn the warnings off (or if you're using ADO, CurrentProject.Connection.Execute "queryname").

 

[mrbebop]

No idea

I don't know how to put a message box around the DoCmd. This is a delete query. I do want the warnings. I am using W2k with Access 2000. I get no errors and nothing happens when the command button is clicked except that a small dotted green rectangle appears within the button when it is clicked. The delete query does work when I go to "Query" and choose to open it. Thanks again.

 

[dcx693]

And that DoCmd.OpenQuery code is in the On Click event of the button?

 

[mrbebop]

Yes

Yes it is on the on click event of the button. This is a copy and paste. deleteshpdata is the button name.

Private Sub deleteshpdata_Click()
DoCmd.OpenQuery "shpdataquery"
End Sub

 

[dcx693]

The behavior your are describing sounds like the query is not even running. Are you sure the code itself is running? Try adding this line to your code after the DoCmd command: MsgBox "Code run" just to see if the code actually ran. I've run into situations where I renamed objects and drove myself crazy only to realize that the code wasn't even running.

 

[mrbebop]

It isn't running!

It isn't running. How is that? How do I fix it? This is confusing¿ Thanks!!!

 

[dcx693]

Aha! I knew it!

Make sure you build the event properly. In form design view, double-click on the command button to bring up the property sheet. On the Event tab, check the line next to On Click. It should say [Event Procedure]. If not, click into the field, then click the three-dot thingee to the right of the field. Make sure it brings you to that code with the DoCmd command. If it doesn't copy and paste the commands into the right place.

Then go to the Debug menu and choose compile. It should compile just fine, otherwise fix any problems. Then close the visual basic editor window and try the button again.

 

[mrbebop]

Yes!

Thanks millions! I am truly grateful. How can I avoid this in the future?

 

[dcx693]

Do it right the first time, and be very careful when you change object names! Typically, I name objects as soon as I am relatively sure of their purpose.

 

[mrbebop]

Trying

How would you make a command button for a delete query such as mine? I appreciate the help and tutoring.

 

[mrbebop]

Worked at home but not on the job!

Compiling worked at home but not at work. The database at home was a copy of the database from work. The copy at home was unsplit and the links were removed. When I try to compile at work I receive "Compile error, Variable not found" over and over again. I do not know how to overcome the problem. Any ideas?
Thanks!!!