Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Command Button Assignment (2000)

    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.

  2. #2
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button Assignment (2000)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button Assignment (2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command Button Assignment (2000)

    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).

  5. #5
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button Assignment (2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Command Button Assignment (2000)

    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

  7. #7
    New Lounger
    Join Date
    Aug 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Command Button Assignment (2000)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Thanks! That got it going for me. I put out another post under a different topic if you'd be so kind.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •