Re: Query Criteria Change with a Macro

Discussion in 'Microsoft Access Macros' started by Steve Schapel, Dec 10, 2003.

  1. 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"
    <> 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
     
    Steve Schapel, Dec 10, 2003
    #1
    1. Advertisements

  2. Steve Schapel

    Guest Guest

    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"
    ><> 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

    >
    >.
    >
     
    Guest, Dec 11, 2003
    #2
    1. Advertisements

  3. 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,
    <> 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"
    >><> 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

    >>
    >>.
    >>
     
    Steve Schapel, Dec 11, 2003
    #3
  4. Steve Schapel

    Michael Guest

    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,
    ><> 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"
    >>><> 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
    >>>
    >>>.
    >>>

    >
    >.
    >
     
    Michael, Dec 11, 2003
    #4
  5. Steve Schapel

    Guest Guest

    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,
    ><> 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"
    >>><> 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
    >>>
    >>>.
    >>>

    >
    >.
    >
     
    Guest, Dec 11, 2003
    #5
  6. 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,
    <> 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??
     
    Steve Schapel, Dec 11, 2003
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    1
    Views:
    317
    Steve Schapel
    Oct 16, 2003
  2. Andy

    Using a Macro to change Query Criteria....

    Andy, Apr 28, 2004, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    1,529
    Douglas J. Steele
    Apr 28, 2004
  3. Ian
    Replies:
    3
    Views:
    1,067
    Steve Schapel
    Jun 30, 2004
  4. Brandi

    Using macro to set query criteria

    Brandi, Oct 4, 2004, in forum: Microsoft Access Macros
    Replies:
    3
    Views:
    920
    Steve Schapel
    Oct 9, 2004
  5. Steve Schapel

    Re: How to tranfer a macro variable in to query criteria ?

    Steve Schapel, Apr 15, 2005, in forum: Microsoft Access Macros
    Replies:
    4
    Views:
    430
    Guest
    Apr 19, 2005
Loading...

Share This Page