PC Review


Reply
Thread Tools Rate Thread

Re: Query Criteria Change with a Macro

 
 
Steve Schapel
Guest
Posts: n/a
 
      11th Dec 2003
Michael,

I don't think it is a good idea to think of changing the query
criteria via a macro. Instead, you could try it like this...
1. Use a table or query to return the list of customers/groups
(sorry, I am not 100% clear on your exact meaning).
2. Make a form based on this table/query
3. Use the applicable control(s) on this form as the criteria for the
query that the report is based on, using syntax such as
[Forms]![NameOfForm]![NameOfControl]
4. Make a macro to do the distribution of the report, followed by a
GoToRecord,Next action
5. Make another macro, using the RunMacro action, to run the first
macro, and in the RepeatCount argument of the RunMancro action, enter
the number of customers/distribution lists.

Hope this might give you an idea you can build on.

- Steve Schapel, Microsoft Access MVP


On Wed, 10 Dec 2003 10:05:38 -0800, "Michael"
<(E-Mail Removed)> wrote:

>Scenario:
>I have a single sales query for groups of customers. I
>generate a usage report for each customer in a group.
>The query basically list out customer by name and group
>and all the sales details. I would like to have a macro
>that opens the query / / changes the criteria to match a
>customer in a certain group / close the query / email the
>report to the corresponding distribution list (based on
>Customer name)/ go to next customer in group.
>
>The list of customer is static and each report will go to
>one distribution list. I have set up the e-mail side to
>single users but I can't figure out how to macro a change
>in the Query criteria. Applyfilter doesn't seem to work
>for me. Any help would be greatly apprectiated.
>
>Thanks


 
Reply With Quote
 
 
 
 
Guest
Posts: n/a
 
      11th Dec 2003
Okay Thanks... This is exactly what I think I need to do.
If I may impose a bit more because I am not an Access
guru in the least.

I am going to try and develop this in stages so I can
actually learn what it is I am doing.

FYI
The end result is e-mailing or ftp'ing several standard
reports to groups of customers. I have 14 customers that
belong to my first group(Parent company). I have 3
reports for each customer.

Queries:
Sales Data (Contains all info on all sales for all groups
and all customers)

Group Query (Contains a list of all Groups with
corresponding customers)

Report Query (Contains the Data to be output via a Access
Report)

Okay.. Now.. I need a little more of a kickstart on the
forms. I have only used forms to have a way to enter
data in the past. I have my Macro that can email my
reports, and I believe that I can set up the RunMacro.

How does the form tie in. # 2 and # 3 Is where I am
really at a loss.

I am going to be trial and erroring with it but any
additional guidance will be appreciated.

Thanks

Michael

>-----Original Message-----
>Michael,
>
>I don't think it is a good idea to think of changing the

query
>criteria via a macro. Instead, you could try it like

this...
>1. Use a table or query to return the list of

customers/groups
>(sorry, I am not 100% clear on your exact meaning).
>2. Make a form based on this table/query
>3. Use the applicable control(s) on this form as the

criteria for the
>query that the report is based on, using syntax such as
>[Forms]![NameOfForm]![NameOfControl]
>4. Make a macro to do the distribution of the report,

followed by a
>GoToRecord,Next action
>5. Make another macro, using the RunMacro action, to

run the first
>macro, and in the RepeatCount argument of the RunMancro

action, enter
>the number of customers/distribution lists.
>
>Hope this might give you an idea you can build on.
>
>- Steve Schapel, Microsoft Access MVP
>
>
>On Wed, 10 Dec 2003 10:05:38 -0800, "Michael"
><(E-Mail Removed)> wrote:
>
>>Scenario:
>>I have a single sales query for groups of customers. I
>>generate a usage report for each customer in a group.
>>The query basically list out customer by name and group
>>and all the sales details. I would like to have a

macro
>>that opens the query / / changes the criteria to match

a
>>customer in a certain group / close the query / email

the
>>report to the corresponding distribution list (based on
>>Customer name)/ go to next customer in group.
>>
>>The list of customer is static and each report will go

to
>>one distribution list. I have set up the e-mail side

to
>>single users but I can't figure out how to macro a

change
>>in the Query criteria. Applyfilter doesn't seem to

work
>>for me. Any help would be greatly apprectiated.
>>
>>Thanks

>
>.
>

 
Reply With Quote
 
 
 
 
Steve Schapel
Guest
Posts: n/a
 
      11th Dec 2003
Michael,

Yes, "trial and erroring" is definitely the way to go! :-)

I am not 100% clear whether your report is being sent to each
Customer, or to each Group. If it is going to the Group, it would be
good to have a field which lists all the email addresses of all the
Customers in each group.

So, let's say for example you have a form based on your Group Query,
which is called GroupForm, and the focus is on the first record, i.e.
Parent Company. And let's say one of the fields in the Group Query is
GroupName. And let's say the Report Query also contains the GroupName
field, and the report to go to the Customers in the Parent Company
group needs to be restricted to the data for the Parent Company
Customers. OK, so if you put this in the criteria of the GroupName
field in the Report Query...
[Forms]![GroupForm]![GroupName]
.... then the report you generate will be for the Customers in the
Parent Company group. If the current record on the GroupForm form
then moved to the next record, the Report Query will then produce the
report for the second Group.

Probably this example does not exactly match your requirements, but
hopefully point you in the right direction.

- Steve Schapel, Microsoft Access MVP


On Thu, 11 Dec 2003 09:44:11 -0800,
<(E-Mail Removed)> wrote:

>Okay Thanks... This is exactly what I think I need to do.
>If I may impose a bit more because I am not an Access
>guru in the least.
>
>I am going to try and develop this in stages so I can
>actually learn what it is I am doing.
>
>FYI
>The end result is e-mailing or ftp'ing several standard
>reports to groups of customers. I have 14 customers that
>belong to my first group(Parent company). I have 3
>reports for each customer.
>
>Queries:
>Sales Data (Contains all info on all sales for all groups
>and all customers)
>
>Group Query (Contains a list of all Groups with
>corresponding customers)
>
>Report Query (Contains the Data to be output via a Access
>Report)
>
>Okay.. Now.. I need a little more of a kickstart on the
>forms. I have only used forms to have a way to enter
>data in the past. I have my Macro that can email my
>reports, and I believe that I can set up the RunMacro.
>
>How does the form tie in. # 2 and # 3 Is where I am
>really at a loss.
>
>I am going to be trial and erroring with it but any
>additional guidance will be appreciated.
>
>Thanks
>
>Michael
>
>>-----Original Message-----
>>Michael,
>>
>>I don't think it is a good idea to think of changing the

>query
>>criteria via a macro. Instead, you could try it like

>this...
>>1. Use a table or query to return the list of

>customers/groups
>>(sorry, I am not 100% clear on your exact meaning).
>>2. Make a form based on this table/query
>>3. Use the applicable control(s) on this form as the

>criteria for the
>>query that the report is based on, using syntax such as
>>[Forms]![NameOfForm]![NameOfControl]
>>4. Make a macro to do the distribution of the report,

>followed by a
>>GoToRecord,Next action
>>5. Make another macro, using the RunMacro action, to

>run the first
>>macro, and in the RepeatCount argument of the RunMancro

>action, enter
>>the number of customers/distribution lists.
>>
>>Hope this might give you an idea you can build on.
>>
>>- Steve Schapel, Microsoft Access MVP
>>
>>
>>On Wed, 10 Dec 2003 10:05:38 -0800, "Michael"
>><(E-Mail Removed)> wrote:
>>
>>>Scenario:
>>>I have a single sales query for groups of customers. I
>>>generate a usage report for each customer in a group.
>>>The query basically list out customer by name and group
>>>and all the sales details. I would like to have a

>macro
>>>that opens the query / / changes the criteria to match

>a
>>>customer in a certain group / close the query / email

>the
>>>report to the corresponding distribution list (based on
>>>Customer name)/ go to next customer in group.
>>>
>>>The list of customer is static and each report will go

>to
>>>one distribution list. I have set up the e-mail side

>to
>>>single users but I can't figure out how to macro a

>change
>>>in the Query criteria. Applyfilter doesn't seem to

>work
>>>for me. Any help would be greatly apprectiated.
>>>
>>>Thanks

>>
>>.
>>


 
Reply With Quote
 
Michael
Guest
Posts: n/a
 
      11th Dec 2003
I am cooking with fire now. Working out some minor
issues but THANKS!!!!

Excellent Advise..
>-----Original Message-----
>Michael,
>
>Yes, "trial and erroring" is definitely the way to

go! :-)
>
>I am not 100% clear whether your report is being sent to

each
>Customer, or to each Group. If it is going to the

Group, it would be
>good to have a field which lists all the email addresses

of all the
>Customers in each group.
>
>So, let's say for example you have a form based on your

Group Query,
>which is called GroupForm, and the focus is on the first

record, i.e.
>Parent Company. And let's say one of the fields in the

Group Query is
>GroupName. And let's say the Report Query also contains

the GroupName
>field, and the report to go to the Customers in the

Parent Company
>group needs to be restricted to the data for the Parent

Company
>Customers. OK, so if you put this in the criteria of

the GroupName
>field in the Report Query...
>[Forms]![GroupForm]![GroupName]
>.... then the report you generate will be for the

Customers in the
>Parent Company group. If the current record on the

GroupForm form
>then moved to the next record, the Report Query will

then produce the
>report for the second Group.
>
>Probably this example does not exactly match your

requirements, but
>hopefully point you in the right direction.
>
>- Steve Schapel, Microsoft Access MVP
>
>
>On Thu, 11 Dec 2003 09:44:11 -0800,
><(E-Mail Removed)> wrote:
>
>>Okay Thanks... This is exactly what I think I need to

do.
>>If I may impose a bit more because I am not an Access
>>guru in the least.
>>
>>I am going to try and develop this in stages so I can
>>actually learn what it is I am doing.
>>
>>FYI
>>The end result is e-mailing or ftp'ing several standard
>>reports to groups of customers. I have 14 customers

that
>>belong to my first group(Parent company). I have 3
>>reports for each customer.
>>
>>Queries:
>>Sales Data (Contains all info on all sales for all

groups
>>and all customers)
>>
>>Group Query (Contains a list of all Groups with
>>corresponding customers)
>>
>>Report Query (Contains the Data to be output via a

Access
>>Report)
>>
>>Okay.. Now.. I need a little more of a kickstart on the
>>forms. I have only used forms to have a way to enter
>>data in the past. I have my Macro that can email my
>>reports, and I believe that I can set up the RunMacro.
>>
>>How does the form tie in. # 2 and # 3 Is where I am
>>really at a loss.
>>
>>I am going to be trial and erroring with it but any
>>additional guidance will be appreciated.
>>
>>Thanks
>>
>>Michael
>>
>>>-----Original Message-----
>>>Michael,
>>>
>>>I don't think it is a good idea to think of changing

the
>>query
>>>criteria via a macro. Instead, you could try it like

>>this...
>>>1. Use a table or query to return the list of

>>customers/groups
>>>(sorry, I am not 100% clear on your exact meaning).
>>>2. Make a form based on this table/query
>>>3. Use the applicable control(s) on this form as the

>>criteria for the
>>>query that the report is based on, using syntax such as
>>>[Forms]![NameOfForm]![NameOfControl]
>>>4. Make a macro to do the distribution of the report,

>>followed by a
>>>GoToRecord,Next action
>>>5. Make another macro, using the RunMacro action, to

>>run the first
>>>macro, and in the RepeatCount argument of the

RunMancro
>>action, enter
>>>the number of customers/distribution lists.
>>>
>>>Hope this might give you an idea you can build on.
>>>
>>>- Steve Schapel, Microsoft Access MVP
>>>
>>>
>>>On Wed, 10 Dec 2003 10:05:38 -0800, "Michael"
>>><(E-Mail Removed)> wrote:
>>>
>>>>Scenario:
>>>>I have a single sales query for groups of customers.

I
>>>>generate a usage report for each customer in a

group.
>>>>The query basically list out customer by name and

group
>>>>and all the sales details. I would like to have a

>>macro
>>>>that opens the query / / changes the criteria to

match
>>a
>>>>customer in a certain group / close the query / email

>>the
>>>>report to the corresponding distribution list (based

on
>>>>Customer name)/ go to next customer in group.
>>>>
>>>>The list of customer is static and each report will

go
>>to
>>>>one distribution list. I have set up the e-mail side

>>to
>>>>single users but I can't figure out how to macro a

>>change
>>>>in the Query criteria. Applyfilter doesn't seem to

>>work
>>>>for me. Any help would be greatly apprectiated.
>>>>
>>>>Thanks
>>>
>>>.
>>>

>
>.
>

 
Reply With Quote
 
Guest
Posts: n/a
 
      11th Dec 2003
The Sendto command is not going to work because of the
security issues. Is there a way for me to save the file
with the current [Account_Name] as the file name
ie.
Current form Record is 1 Account_Name = XYZ Company
OutputTo c:\[Account_Name].xls

or something like that??
>-----Original Message-----
>Michael,
>
>Yes, "trial and erroring" is definitely the way to

go! :-)
>
>I am not 100% clear whether your report is being sent to

each
>Customer, or to each Group. If it is going to the

Group, it would be
>good to have a field which lists all the email addresses

of all the
>Customers in each group.
>
>So, let's say for example you have a form based on your

Group Query,
>which is called GroupForm, and the focus is on the first

record, i.e.
>Parent Company. And let's say one of the fields in the

Group Query is
>GroupName. And let's say the Report Query also contains

the GroupName
>field, and the report to go to the Customers in the

Parent Company
>group needs to be restricted to the data for the Parent

Company
>Customers. OK, so if you put this in the criteria of

the GroupName
>field in the Report Query...
>[Forms]![GroupForm]![GroupName]
>.... then the report you generate will be for the

Customers in the
>Parent Company group. If the current record on the

GroupForm form
>then moved to the next record, the Report Query will

then produce the
>report for the second Group.
>
>Probably this example does not exactly match your

requirements, but
>hopefully point you in the right direction.
>
>- Steve Schapel, Microsoft Access MVP
>
>
>On Thu, 11 Dec 2003 09:44:11 -0800,
><(E-Mail Removed)> wrote:
>
>>Okay Thanks... This is exactly what I think I need to

do.
>>If I may impose a bit more because I am not an Access
>>guru in the least.
>>
>>I am going to try and develop this in stages so I can
>>actually learn what it is I am doing.
>>
>>FYI
>>The end result is e-mailing or ftp'ing several standard
>>reports to groups of customers. I have 14 customers

that
>>belong to my first group(Parent company). I have 3
>>reports for each customer.
>>
>>Queries:
>>Sales Data (Contains all info on all sales for all

groups
>>and all customers)
>>
>>Group Query (Contains a list of all Groups with
>>corresponding customers)
>>
>>Report Query (Contains the Data to be output via a

Access
>>Report)
>>
>>Okay.. Now.. I need a little more of a kickstart on the
>>forms. I have only used forms to have a way to enter
>>data in the past. I have my Macro that can email my
>>reports, and I believe that I can set up the RunMacro.
>>
>>How does the form tie in. # 2 and # 3 Is where I am
>>really at a loss.
>>
>>I am going to be trial and erroring with it but any
>>additional guidance will be appreciated.
>>
>>Thanks
>>
>>Michael
>>
>>>-----Original Message-----
>>>Michael,
>>>
>>>I don't think it is a good idea to think of changing

the
>>query
>>>criteria via a macro. Instead, you could try it like

>>this...
>>>1. Use a table or query to return the list of

>>customers/groups
>>>(sorry, I am not 100% clear on your exact meaning).
>>>2. Make a form based on this table/query
>>>3. Use the applicable control(s) on this form as the

>>criteria for the
>>>query that the report is based on, using syntax such as
>>>[Forms]![NameOfForm]![NameOfControl]
>>>4. Make a macro to do the distribution of the report,

>>followed by a
>>>GoToRecord,Next action
>>>5. Make another macro, using the RunMacro action, to

>>run the first
>>>macro, and in the RepeatCount argument of the

RunMancro
>>action, enter
>>>the number of customers/distribution lists.
>>>
>>>Hope this might give you an idea you can build on.
>>>
>>>- Steve Schapel, Microsoft Access MVP
>>>
>>>
>>>On Wed, 10 Dec 2003 10:05:38 -0800, "Michael"
>>><(E-Mail Removed)> wrote:
>>>
>>>>Scenario:
>>>>I have a single sales query for groups of customers.

I
>>>>generate a usage report for each customer in a

group.
>>>>The query basically list out customer by name and

group
>>>>and all the sales details. I would like to have a

>>macro
>>>>that opens the query / / changes the criteria to

match
>>a
>>>>customer in a certain group / close the query / email

>>the
>>>>report to the corresponding distribution list (based

on
>>>>Customer name)/ go to next customer in group.
>>>>
>>>>The list of customer is static and each report will

go
>>to
>>>>one distribution list. I have set up the e-mail side

>>to
>>>>single users but I can't figure out how to macro a

>>change
>>>>in the Query criteria. Applyfilter doesn't seem to

>>work
>>>>for me. Any help would be greatly apprectiated.
>>>>
>>>>Thanks
>>>
>>>.
>>>

>
>.
>

 
Reply With Quote
 
Steve Schapel
Guest
Posts: n/a
 
      11th Dec 2003
Michael,

I am assuming you will be using a SendObject macro action. In the
Object Name argument, you will need something like...
="C:\" & [Forms]![GroupForm]![Account_Name] & ".xls"

- Steve Schapel, Microsoft Access MVP


On Thu, 11 Dec 2003 13:41:40 -0800,
<(E-Mail Removed)> wrote:

>The Sendto command is not going to work because of the
>security issues. Is there a way for me to save the file
>with the current [Account_Name] as the file name
>ie.
>Current form Record is 1 Account_Name = XYZ Company
>OutputTo c:\[Account_Name].xls
>
>or something like that??


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Criteria: evaluating criteria as a group =?Utf-8?B?V2lsZGx5SGFycnk=?= Microsoft Access 3 13th Sep 2007 10:11 PM
Can a query ignore criteria when the criteria is blank? =?Utf-8?B?UGVycGxleGVkaW5LWQ==?= Microsoft Access Reports 2 18th Feb 2007 01:10 AM
Query with criteria for long data type but criteria is double =?Utf-8?B?THluZGE=?= Microsoft Access Queries 1 30th Jan 2007 02:24 AM
Using a Macro to change Query Criteria.... Andy Microsoft Access Macros 1 28th Apr 2004 11:09 PM
HELP! Change color of a control if criteria meets criteria in an unbound box Aileen Microsoft Access Forms 1 26th Sep 2003 07:49 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:14 PM.