How to change criteria in a query using a macros

[Guest]

I have aprrox 50 queries that all have one field on common (Division). I need
to run each query for each division (10 of them). Fortunately, I was ably to
write VBA code in Excel that automatically runs and down loads the queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have to stop
and go in to each individual query and change the division number to set it
up for the next run of 50. What I need is macro/code that will change the
criteria for all 50 queries in one shot. The criteria must be physically
changed in the query. Referencing a form will not work because the excel code
runs the queries in the background.

Can anyone help?

 

[Guest]

Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

 

[tina]

that's a good, standard solution, Eric. but in this case, the post-er said
"Referencing a form will not work because the excel code runs the queries in
the background." so an alternate solution is required.

hth

Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

I have aprrox 50 queries that all have one field on common (Division). I need
to run each query for each division (10 of them). Fortunately, I was ably to
write VBA code in Excel that automatically runs and down loads the queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have to stop
and go in to each individual query and change the division number to set it
up for the next run of 50. What I need is macro/code that will change the
criteria for all 50 queries in one shot. The criteria must be physically
changed in the query. Referencing a form will not work because the excel code
runs the queries in the background.

Can anyone help?

 

[Guest]

I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could download all
divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data. many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

that's a good, standard solution, Eric. but in this case, the post-er said
"Referencing a form will not work because the excel code runs the queries in
the background." so an alternate solution is required.

hth

Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

I have aprrox 50 queries that all have one field on common (Division). I need
to run each query for each division (10 of them). Fortunately, I was ably to
write VBA code in Excel that automatically runs and down loads the queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have to stop
and go in to each individual query and change the division number to set it
up for the next run of 50. What I need is macro/code that will change the
criteria for all 50 queries in one shot. The criteria must be physically
changed in the query. Referencing a form will not work because the excel code
runs the queries in the background.

Can anyone help?

 

[tina]

okay, perhaps i misunderstood the post-er's situation. what i got out of the
original post is that: he has the data, and the 50 queries, in an Access
database. and he's using code in Excel to run the queries in Access and
output the resulting datasets to Excel spreadsheets. his concern is that he
has to manually change the criteria in each Access query before running the
Excel code to extract the data. since he nixed the form reference idea, i
suggested updating the queries using a loop in VBA. perhaps there's no way
to call an Access VBA procedure from Excel, i don't know anything about
using VBA in Excel.

hth

I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could download all
divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data. many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

that's a good, standard solution, Eric. but in this case, the post-er said
"Referencing a form will not work because the excel code runs the queries in
the background." so an alternate solution is required.

hth

Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

:

I have aprrox 50 queries that all have one field on common

(Division). I
need

to run each query for each division (10 of them). Fortunately, I

was
ably to

write VBA code in Excel that automatically runs and down loads the queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have

to
stop

and go in to each individual query and change the division number to

set
it

up for the next run of 50. What I need is macro/code that will

change
the

criteria for all 50 queries in one shot. The criteria must be physically
changed in the query. Referencing a form will not work because the

excel
code

runs the queries in the background.

Can anyone help?

 

[Guest]

Tina I agree with you that Chas is probably running the queries from excel.
What I would do is to run the 50 queries selecting all the divisions into
different sheet(s). Then uses excel to parse to break out the divisions.
Perhaps Chas can explain his requirements.

okay, perhaps i misunderstood the post-er's situation. what i got out of the
original post is that: he has the data, and the 50 queries, in an Access
database. and he's using code in Excel to run the queries in Access and
output the resulting datasets to Excel spreadsheets. his concern is that he
has to manually change the criteria in each Access query before running the
Excel code to extract the data. since he nixed the form reference idea, i
suggested updating the queries using a loop in VBA. perhaps there's no way
to call an Access VBA procedure from Excel, i don't know anything about
using VBA in Excel.

hth

I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could download all
divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data. many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

that's a good, standard solution, Eric. but in this case, the post-er said
"Referencing a form will not work because the excel code runs the queries in
the background." so an alternate solution is required.

hth


Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

:

I have aprrox 50 queries that all have one field on common (Division). I
need
to run each query for each division (10 of them). Fortunately, I was
ably to
write VBA code in Excel that automatically runs and down loads the
queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have to
stop
and go in to each individual query and change the division number to set
it
up for the next run of 50. What I need is macro/code that will change
the
criteria for all 50 queries in one shot. The criteria must be
physically
changed in the query. Referencing a form will not work because the excel
code
runs the queries in the background.

Can anyone help?

 

[tina]

well, he's never posted back to this thread at all; maybe my first answer
was no good for him, and he just gave up the thread. probably somebody else
already helped him in another thread - at least we can hope so.

Tina I agree with you that Chas is probably running the queries from excel.
What I would do is to run the 50 queries selecting all the divisions into
different sheet(s). Then uses excel to parse to break out the divisions.
Perhaps Chas can explain his requirements.

okay, perhaps i misunderstood the post-er's situation. what i got out of the
original post is that: he has the data, and the 50 queries, in an Access
database. and he's using code in Excel to run the queries in Access and
output the resulting datasets to Excel spreadsheets. his concern is that he
has to manually change the criteria in each Access query before running the
Excel code to extract the data. since he nixed the form reference idea, i
suggested updating the queries using a loop in VBA. perhaps there's no way
to call an Access VBA procedure from Excel, i don't know anything about
using VBA in Excel.

hth

I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could

download
all

divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data. many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

:

that's a good, standard solution, Eric. but in this case, the

post-er
said

"Referencing a form will not work because the excel code runs the queries in
the background." so an alternate solution is required.

hth


Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

:

I have aprrox 50 queries that all have one field on common (Division). I
need
to run each query for each division (10 of them). Fortunately,

I
was

ably to
write VBA code in Excel that automatically runs and down loads the
queries to
an excel sheets. Unfortunately, each time I run 50 queries, I

have
to

stop
and go in to each individual query and change the division

number to
set

it
up for the next run of 50. What I need is macro/code that will change
the
criteria for all 50 queries in one shot. The criteria must be
physically
changed in the query. Referencing a form will not work because

the
excel

code
runs the queries in the background.

Can anyone help?

 

[Guest]

I have seen your postings before and you are very knowledgable with MS
Access. I look forwar to your future responses.

well, he's never posted back to this thread at all; maybe my first answer
was no good for him, and he just gave up the thread. probably somebody else
already helped him in another thread - at least we can hope so.

Tina I agree with you that Chas is probably running the queries from excel.
What I would do is to run the 50 queries selecting all the divisions into
different sheet(s). Then uses excel to parse to break out the divisions.
Perhaps Chas can explain his requirements.

okay, perhaps i misunderstood the post-er's situation. what i got out of the
original post is that: he has the data, and the 50 queries, in an Access
database. and he's using code in Excel to run the queries in Access and
output the resulting datasets to Excel spreadsheets. his concern is that he
has to manually change the criteria in each Access query before running the
Excel code to extract the data. since he nixed the form reference idea, i
suggested updating the queries using a loop in VBA. perhaps there's no way
to call an Access VBA procedure from Excel, i don't know anything about
using VBA in Excel.

hth


I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could download
all
divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data.
many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

:

that's a good, standard solution, Eric. but in this case, the post-er
said
"Referencing a form will not work because the excel code runs the
queries in
the background." so an alternate solution is required.

hth


Create a form with a combo box listing tour divisions. In your query
reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

:

I have aprrox 50 queries that all have one field on common
(Division). I
need
to run each query for each division (10 of them). Fortunately, I
was
ably to
write VBA code in Excel that automatically runs and down loads the
queries to
an excel sheets. Unfortunately, each time I run 50 queries, I have
to
stop
and go in to each individual query and change the division number to
set
it
up for the next run of 50. What I need is macro/code that will
change
the
criteria for all 50 queries in one shot. The criteria must be
physically
changed in the query. Referencing a form will not work because the
excel
code
runs the queries in the background.

Can anyone help?

 

[tina]

thank you, Eric! <bows>

I have seen your postings before and you are very knowledgable with MS
Access. I look forwar to your future responses.

well, he's never posted back to this thread at all; maybe my first answer
was no good for him, and he just gave up the thread. probably somebody else
already helped him in another thread - at least we can hope so.

Tina I agree with you that Chas is probably running the queries from excel.
What I would do is to run the 50 queries selecting all the divisions into
different sheet(s). Then uses excel to parse to break out the divisions.
Perhaps Chas can explain his requirements.

:

okay, perhaps i misunderstood the post-er's situation. what i got

out of
the

original post is that: he has the data, and the 50 queries, in an Access
database. and he's using code in Excel to run the queries in Access and
output the resulting datasets to Excel spreadsheets. his concern is

that
he

has to manually change the criteria in each Access query before

running
the

Excel code to extract the data. since he nixed the form reference

idea,
i

suggested updating the queries using a loop in VBA. perhaps there's

no
way

to call an Access VBA procedure from Excel, i don't know anything about
using VBA in Excel.

hth


I am not sure if you are in excel or Access. The solution I gave before
applies to if you are in Access. If you are in Excel you could download
all
divisions to one sheet(s) and use lookup formulas to populate the sheets
based on division. I assume that is how your are dividing your data.
many
times I have used a hiden data sheet or seperate linked spreadsheet to
accomplish what it appears you are doing.

:

that's a good, standard solution, Eric. but in this case, the post-er
said
"Referencing a form will not work because the excel code runs the
queries in
the background." so an alternate solution is required.

hth


Create a form with a combo box listing tour divisions. In

your
query

reference the form for the division criteria like
[Forms]![NameOfForm]![NameOfcombobox]

run the macro from a command button

:

I have aprrox 50 queries that all have one field on common
(Division). I
need
to run each query for each division (10 of them).

Fortunately,
I

was
ably to
write VBA code in Excel that automatically runs and down

loads
the

queries to
an excel sheets. Unfortunately, each time I run 50 queries,

I
have

to
stop
and go in to each individual query and change the division number to
set
it
up for the next run of 50. What I need is macro/code that will
change
the
criteria for all 50 queries in one shot. The criteria must be
physically
changed in the query. Referencing a form will not work

because
the

excel
code
runs the queries in the background.

Can anyone help?