Command Button Assignment (2000)

Topic

New Reply

I have 3 queries (1 append and 2 delete). I would like to assign all 3 queries to run with a single button. As I was running the Command Button Wizard I see that the delete queries do not show up in the list, but the append query does.

1- If it’s possible, how do I assign a delete query to a command button?
2- If it’s possible, how can I associate all 3 of my queries to a single button?

Thanks.

Reply | Quote

Replies

Never mind. I figured out using a macro will work.

Is there a way to get rid of the “are you sure” prompts. There are 2 for each query and it’s a little annoying.

Thanks.

Reply | Quote

You want to Set Warnings Off. Set it back to On at the end of your macro or you will not receive warnings after the macro completes.

Reply | Quote

You can execute multiple queries in code too. The Wizard will create code for executing one query only, but you can then edit the code and add other instructions. For example:

Private Sub cmdButton_Click()

On Error Goto Err_cmdButton_Click

DoCmd.SetWarnings False

DoCmd.OpenQuery “qryAppend”
DoCmd.OpenQuery “qryDelete1”
DoCmd.OpenQuery “qryDelete2”

Exit_cmdButton_Click:
DoCmd.SetWarnings True
Exit Sub

Err_cmdButton_Click:
MsgBox Err.Description
Resume Exit_cmdButton_Click

End Sub

This way, SetWarnings will be turned on again even if an error occurred.

BTW, in my copy of Access 97, the Command Button Wizard displays all types of queries (selection, update, append, maketable, delete, union).

Reply | Quote

Thanks for the additional info. I’ve been trying to keep things simple and let Access do the coding since I’m not a programmer. I’ll hang onto your suggestion though. I’ve run into another problem along these lines. I have a form that contains 2 fields, start date and end date, that is used to input to my constant table which is then referenced in my append and delete queries. I enter the dates on the form, click on the button that executes the macro which in turn runs the queries to….

1) append rows to the archive table
2) delete rows from the daily table
3) delete rows from the constant table so there are no left over dates for the next run

What is happening is the macro run and executes the queries but no data is moved around. If I edit the constant table manually or if I exit the input form, return to it and click on the command button to run the macro, data will be moved correctly. This indicates to me that the constant table is not being written via the input form until I exit the form. Any suggestions on how to correct this?

Thanks,
Mike

Reply | Quote

The problem is probably that the dates entered on the form haven’t been saved to the constant table yet. You might correct this by inserting a save command before running the append query. In a macro, insert a RunCommand action with SaveRecord as command. In VBA code, use the equivalent code

RunCommand acCmdSaveRecord

Perhaps it would be easier to skip the constant table entirely. Make your form and text boxes unbound (that is, with empty RecordSource/ControlSource) and refer to the text boxes on the form directly in the append and delete queries. Say that your form is named frmDateSelect, and the text boxes are named txtStartDate and txtEndDate. The selection criteria in your queries can be

Between Forms![frmDateSelect]![txtStartDate] And Forms![frmDateSelect]![txtEndDate]

There is no need for the second delete query now, since there is no constant table.

HTH,
Hans

Reply | Quote

Thanks! That got it going for me. I put out another post under a different topic if you’d be so kind.

Mike

Reply | Quote