How to run multiple queries through a macro?

[swb76]

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

[Arno R]

"swb76" <sw***@yahoo.comschreef in bericht news:11**********************@g12g2000prg.googlegr oups.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

[swb76]

On Jul 31, 9:20 am, "Arno R" <[e-mail]:

"swb76" <[e-mail] in berichtnews:11**********************@g12g2000prg.g ooglegroups.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,

[Arno R]

"swb76" <sw***@yahoo.comschreef in bericht news:11**********************@x35g2000prf.googlegr oups.com...

On Jul 31, 9:20 am, "Arno R" <[e-mail]:

>"swb76" <[e-mail] in berichtnews:11**********************@g12g2000prg.g ooglegroups.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