+ Reply to Thread
Results 1 to 4 of 4

How to run multiple queries through a macro?

  1. How to run multiple queries through a macro?

    Hi,

    I have 6 queries in Access that run great. They need to be run in
    sequence with the first 5 queries writing to tables and the sixth one
    pops up the final results in datasheet view. Now, how can i automate
    this process? I started with the simple macro builder and entered all
    6 queries perfectly fine. However, i want to prompt the user if they
    wish to run queries 1 & 2, if they say no, it should move to 3 and run
    the remaining ones.
    The simple msgbox in the macro builder does not do the trick. Does
    anyone know if there is a creative way of solving the problem?

    Your help is greatly appreciated,

    Thanks,

    - Sam


  2. Re: How to run multiple queries through a macro?


    "swb76" schreef in bericht news:1185887452.072705.120340@g12g2000prg.googlegroups.com...
    > Hi,
    >
    > I have 6 queries in Access that run great. They need to be run in
    > sequence with the first 5 queries writing to tables and the sixth one
    > pops up the final results in datasheet view. Now, how can i automate
    > this process? I started with the simple macro builder and entered all
    > 6 queries perfectly fine. However, i want to prompt the user if they
    > wish to run queries 1 & 2, if they say no, it should move to 3 and run
    > the remaining ones.
    > The simple msgbox in the macro builder does not do the trick. Does
    > anyone know if there is a creative way of solving the problem?
    >
    > Your help is greatly appreciated,
    >
    > Thanks,
    >
    > - Sam


    You could use an Inputbox in the conditions-column of the macro.
    (If you don't see this column, first look how to show it)
    But I don't like Inputboxes...(nor macro's)
    If I needed to use a macro, I would show a form with checkboxes and use the value of the checkboxes in the conditions-column.

    HTH
    Arno R



  3. Re: How to run multiple queries through a macro?

    On Jul 31, 9:20 am, "Arno R" wrote:
    > "swb76" schreef in berichtnews:1185887452.072705.120340@g12g2000prg.googlegroups.com...
    >
    >
    >
    >
    >
    > > Hi,

    >
    > > I have 6 queries inAccessthat run great. They need to be run in
    > > sequence with the first 5 queries writing to tables and the sixth one
    > > pops up the final results in datasheet view. Now, how can i automate
    > > this process? I started with the simple macro builder and entered all
    > > 6 queries perfectly fine. However, i want to prompt the user if they
    > > wish to run queries 1 & 2, if they say no, it should move to 3 and run
    > > the remaining ones.
    > > The simple msgbox in the macro builder does not do the trick. Does
    > > anyone know if there is a creative way of solving the problem?

    >
    > > Your help is greatly appreciated,

    >
    > > Thanks,

    >
    > > - Sam

    >
    > You could use an Inputbox in the conditions-column of the macro.
    > (If you don't see this column, first look how to show it)
    > But I don't like Inputboxes...(nor macro's)
    > If I needed to use a macro, I would show a form with checkboxes and use the value of the checkboxes in the conditions-column.
    >
    > HTH
    > Arno R- Hide quoted text -
    >
    > - Show quoted text -


    I see.. so you are saying design a form with input fields and a 'Run
    Button' that zooms through the user inputs and run those queries
    accordingly...thats a very good idea..how easy it is to draw a form
    with a checkboxes and stuff and then run the entire thing with a
    'button' - can you guide me a bit on the VB part..

    Thanks,




  4. Re: How to run multiple queries through a macro?


    "swb76" schreef in bericht news:1185899540.419899.270310@x35g2000prf.googlegroups.com...
    > On Jul 31, 9:20 am, "Arno R" wrote:
    >> "swb76" schreef in berichtnews:1185887452.072705.120340@g12g2000prg.googlegroups.com...
    >>
    >>
    >>
    >>
    >>
    >> > Hi,

    >>
    >> > I have 6 queries inAccessthat run great. They need to be run in
    >> > sequence with the first 5 queries writing to tables and the sixth one
    >> > pops up the final results in datasheet view. Now, how can i automate
    >> > this process? I started with the simple macro builder and entered all
    >> > 6 queries perfectly fine. However, i want to prompt the user if they
    >> > wish to run queries 1 & 2, if they say no, it should move to 3 and run
    >> > the remaining ones.
    >> > The simple msgbox in the macro builder does not do the trick. Does
    >> > anyone know if there is a creative way of solving the problem?

    >>
    >> > Your help is greatly appreciated,

    >>
    >> > Thanks,

    >>
    >> > - Sam

    >>
    >> You could use an Inputbox in the conditions-column of the macro.
    >> (If you don't see this column, first look how to show it)
    >> But I don't like Inputboxes...(nor macro's)
    >> If I needed to use a macro, I would show a form with checkboxes and use the value of the checkboxes in the conditions-column.
    >>
    >> HTH
    >> Arno R- Hide quoted text -
    >>
    >> - Show quoted text -

    >
    > I see.. so you are saying design a form with input fields and a 'Run
    > Button' that zooms through the user inputs and run those queries
    > accordingly...thats a very good idea..how easy it is to draw a form
    > with a checkboxes and stuff and then run the entire thing with a
    > 'button' - can you guide me a bit on the VB part..
    >
    > Thanks,
    >
    >


    The code-way:
    Something like this code on a form (FrmUserChoice) with a checkbox called chkRun12

    Private Sub BtnRunQuerys()
    If Me!ChkRun12=True then
    CurrentDb.execute "Query1", dbFailOnError
    CurrentDb.execute "Query2, dbFailOnError
    end if
    CurrentDb.execute "Query3, dbFailOnError
    CurrentDb.execute "Query4, dbFailOnError
    CurrentDb.execute "Query5, dbFailOnError
    CurrentDb.execute "Query6, dbFailOnError
    End Sub

    You would need to add errorhandling yourself.
    That is the big advantage of code over macro's here.
    You can trap for errors and take action accordingly.

    If you don't know about code and errorhandling you could go the 'macro-way' like:
    In the conditions column for the first two query's:
    Forms!FrmUserChoice!ChkRun12= true

    Arno R

+ Reply to Thread