Need Macro to do Basic Repetitive Tasks

[Guest]

I've been using Access to run some monthly reports, manually changing data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs each
month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to. Unfortunately I
feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd take a
class and learn more but I doubt I'll need to do another Macro anytime in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If so, how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Rick B]

You probably don't need to do any of this.

Access queries can be designed to pull dates based on the current date. In
other words, you can tell it to pull everything from "last month" without
having to tell it the month number. You can use functions to take the
current date and subtract a month, for example.

You can also include parts of dates in your report title that are dynamic.
So, each time you print the report, it will indicate the actual date range
used without the need for the user to type it in.

For example...


= "Monthly Report for " & Format(Date(),"MMMM") & " M-Z"

Would print out...

Monthly Report for May M-Z


You can use other features to tell it you want a different month.

Post back some details on what you'd like to happen and perhaps we can help
you make your report more dynamic so that you can run ONE report each month
and have it pull the data you want without having to change your queries or
reports.



--
Rick B

I've been using Access to run some monthly reports, manually changing data
in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs
each
month. I've never used a Macro before but I created one to try to
automate
the process, and it did a few of the things I wanted it to. Unfortunately
I
feel like I'm a long ways off on what I thought should be a simple
project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd take
a
class and learn more but I doubt I'll need to do another Macro anytime in
the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If so,
how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2)
so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Rick B]

One other thought, you can have a form pop up when the query is run that
asks the user to select the date to use and any other parameters you'd like.
The query can then pull the data based on the entries in that "parameter
form" rather than going in and modifying your query each time.

I'd be curious to know why you need to run several reports. Again, you
might be able to combine these all into one using the same query.

Lots of possibilities. We'd need to know more about what you are doing
though.


--
Rick B

You probably don't need to do any of this.

Access queries can be designed to pull dates based on the current date.
In other words, you can tell it to pull everything from "last month"
without having to tell it the month number. You can use functions to take
the current date and subtract a month, for example.

You can also include parts of dates in your report title that are dynamic.
So, each time you print the report, it will indicate the actual date range
used without the need for the user to type it in.

For example...


= "Monthly Report for " & Format(Date(),"MMMM") & " M-Z"

Would print out...

Monthly Report for May M-Z


You can use other features to tell it you want a different month.

Post back some details on what you'd like to happen and perhaps we can
help you make your report more dynamic so that you can run ONE report each
month and have it pull the data you want without having to change your
queries or reports.



--
Rick B

I've been using Access to run some monthly reports, manually changing
data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs
each
month. I've never used a Macro before but I created one to try to
automate
the process, and it did a few of the things I wanted it to.
Unfortunately I
feel like I'm a long ways off on what I thought should be a simple
project,
particularly the way things are automated these days. I read through
many
help screens and newsgroup items but only got a little further. I'd take
a
class and learn more but I doubt I'll need to do another Macro anytime in
the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run
through
the keystrokes and have Access enter the correct Macro protocol? If so,
how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate
year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2)
so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Guest]

Thank you for your response. BTW, I got an email from the website suggesting
I reply through the link provided to give credit to responders, etc etc ...
it goes nowhere at least on my PC and the newsgroups are coming up extremely
slowly ... it took me 5 minutes just to get to this point once I was notified
of a response. Anyway:

I definitely want a form to pop up and to manually enter the month and the
alphabet split as shown in the steps below, because I run them at different
times and sometimes multiple times and the alphabet split is not alwasy
constant ... in other words, manual input of those two parameters is
definitely best for me.

I typically run 6 reports each month. That consists of 3 separate
Query/Reports (3 different insurance policies) for each side of the alphabet
(A-L and M-Z for representatives who will use the data in the Reports and
their knowledge of the business to determine whtether a letter or phone call
is warranted). Each Query/Report draws data from 6 different Tables (because
we can only get a few bits of information out of our Company's system at a
time), and it was quite a trick to make sure things worked correctly,
especially being that I'm not a programmer. Even though I imagine I could
combine my Reports and Queries, I spent a lot of time already perfecting the
Reports and Queries and documentation on how to get the data out of our
companies system and into Access ... therefore, combining is not an option.
Regardless, I don't know why it should matter, especially if I can use a
Macro to automate the process ... it should be easy to duplicate a Macro or
replicate the code in the Macro once I'm able to figure out how to do just
one Macro correctly. Therefore, all I'm really interested in is what
keystrokes/commands/etc to use in the Macro to accomplish the steps listed in
my original email.

For example, in Step 1, if I'm in Macro design mode, what action options
should I use to make a pop-up window occur to collect the Month and Alphabet
information. Then, in Step 3, how do I make the Macro spit that information
out onto the criteria portion of the "Client Code" field?

Hope that helps identify the problem better.


-------------------------------------

One other thought, you can have a form pop up when the query is run that
asks the user to select the date to use and any other parameters you'd like.
The query can then pull the data based on the entries in that "parameter
form" rather than going in and modifying your query each time.

I'd be curious to know why you need to run several reports. Again, you
might be able to combine these all into one using the same query.

Lots of possibilities. We'd need to know more about what you are doing
though.


--
Rick B

You probably don't need to do any of this.

Access queries can be designed to pull dates based on the current date.
In other words, you can tell it to pull everything from "last month"
without having to tell it the month number. You can use functions to take
the current date and subtract a month, for example.

You can also include parts of dates in your report title that are dynamic.
So, each time you print the report, it will indicate the actual date range
used without the need for the user to type it in.

For example...


= "Monthly Report for " & Format(Date(),"MMMM") & " M-Z"

Would print out...

Monthly Report for May M-Z


You can use other features to tell it you want a different month.

Post back some details on what you'd like to happen and perhaps we can
help you make your report more dynamic so that you can run ONE report each
month and have it pull the data you want without having to change your
queries or reports.



--
Rick B

I've been using Access to run some monthly reports, manually changing
data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs
each
month. I've never used a Macro before but I created one to try to
automate
the process, and it did a few of the things I wanted it to.
Unfortunately I
feel like I'm a long ways off on what I thought should be a simple
project,
particularly the way things are automated these days. I read through
many
help screens and newsgroup items but only got a little further. I'd take
a
class and learn more but I doubt I'll need to do another Macro anytime in
the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run
through
the keystrokes and have Access enter the correct Macro protocol? If so,
how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate
year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2)
so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Larry Daugherty]

" I'd take a class and learn more but I doubt I'll need to do another
Macro anytime in the next few years. Any help you can provide would
be greatly appreciated".

In short, it's not worth YOUR time and effort to help yourself but
would someone else please donate their time and effort so that you
don't have to do so.

Given your attitude, I won't spend much time on this. However, I will
give you some good advice and point you in the right direction.

Access does not have a macro recorder.

Don't use Access macros for anything beyond a test of concept. If
the concept is right, do it over in VBA. As you learn VBA, get rid of
all macros except Autoexec and your menu stuff. You can't step
through macros and, therefore, disallow one of the most useful
debugging tools.

For the report issues. Find or google search on the Access groups on
my name and "launcher". Using the information you find you should be
able to resolve your report issues.

HTH
--
-Larry-
--

I've been using Access to run some monthly reports, manually changing data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs each
month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to. Unfortunately I
feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd take a
class and learn more but I doubt I'll need to do another Macro anytime in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If so, how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[tina]

you can't change a query's SQL statement with a macro, you'd have to do it
in VBA. but there's no need anyway; instead, you can set your query criteria
to refer to a control on a form, and the query will use whatever is typed in
the control. you should be able to use the same technique in the textbox
control on the report - set the ControlSource to refer to the control on the
form, as

=Forms!FormName!ControlName

and in report preview the control will display whatever is typed in the form
control.

hth

 

[Guest]

You are correct. It is not worth my time to learn VBA programming just to do
one macro. That's why we have Newsgroups; otherwise everyone should just
spend hours and hours in classes to learn to do simple tasks and stop asking
Q's in Newsgroups. My question was not put forward so that someone would
spend hours and hours trying to figure out an answer for me. Rather, I was
hoping there were a couple simple steps that could be taken, maybe even 9
lines of code that could be handed over with 2 minutes of effort.

Anyway, I do appreciate your straightforward responses which were very
valuable to me. If I'm wrong in concluding from your response, that it's a
lot more complicated than a few simple keystrokes, please let me know.
Sounds like I better learn VBA, continue doing the reports manually, or hire
someone to program it. On the third point, any idea what that might cost and
where I might find interested (and highly competent) programmers for that?

Thanks
----------------------------------------------

" I'd take a class and learn more but I doubt I'll need to do another
Macro anytime in the next few years. Any help you can provide would
be greatly appreciated".

In short, it's not worth YOUR time and effort to help yourself but
would someone else please donate their time and effort so that you
don't have to do so.

Given your attitude, I won't spend much time on this. However, I will
give you some good advice and point you in the right direction.

Access does not have a macro recorder.

Don't use Access macros for anything beyond a test of concept. If
the concept is right, do it over in VBA. As you learn VBA, get rid of
all macros except Autoexec and your menu stuff. You can't step
through macros and, therefore, disallow one of the most useful
debugging tools.

For the report issues. Find or google search on the Access groups on
my name and "launcher". Using the information you find you should be
able to resolve your report issues.

HTH
--
-Larry-
--

I've been using Access to run some monthly reports, manually changing data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs each
month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to. Unfortunately I
feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd take a
class and learn more but I doubt I'll need to do another Macro anytime in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If so, how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Guest]

Thank you for your response and I'll try your suggestion. BTW, I get these
e-mails suggesting that I rate responses but when I click on the links and
look around, I can't find where to thank/rate responders. If you care to
respond or think it's important, any clues on this are appreciated.

Lung
---------------------------------------------------------------------

you can't change a query's SQL statement with a macro, you'd have to do it
in VBA. but there's no need anyway; instead, you can set your query criteria
to refer to a control on a form, and the query will use whatever is typed in
the control. you should be able to use the same technique in the textbox
control on the report - set the ControlSource to refer to the control on the
form, as

=Forms!FormName!ControlName

and in report preview the control will display whatever is typed in the form
control.

hth

I've been using Access to run some monthly reports, manually changing data in
a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different runs each
month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to. Unfortunately I
feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd take a
class and learn more but I doubt I'll need to do another Macro anytime in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If so, how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[BruceM]

Rating responses is a function of the web-based newsreader, I believe.
For the other part of your question, try doing a Google groups search for:
Microsoft Access form specify report parameter (or something like that).
Using a form to specify report parameters is a fairly common situation, and
the methods for doing so are well-documented. I will just mention that the
method described in Access Help may not be the best one.
I would suggest that the best approach to solving a problem of this sort is
to describe the desired end result. A summary of what you have tried may
help show that you made an effort to solve the problem before you posted,
but there's no need for a lot of detail about what doesn't work. Everything
you have described in your hypothetical macro can be accomplished through
the use of a parameter form, but you seem almost to have gotten locked into
the idea of using a macro.

Thank you for your response and I'll try your suggestion. BTW, I get
these
e-mails suggesting that I rate responses but when I click on the links and
look around, I can't find where to thank/rate responders. If you care to
respond or think it's important, any clues on this are appreciated.

Lung
---------------------------------------------------------------------

you can't change a query's SQL statement with a macro, you'd have to do
it
in VBA. but there's no need anyway; instead, you can set your query
criteria
to refer to a control on a form, and the query will use whatever is typed
in
the control. you should be able to use the same technique in the textbox
control on the report - set the ControlSource to refer to the control on
the
form, as

=Forms!FormName!ControlName

and in report preview the control will display whatever is typed in the
form
control.

hth

I've been using Access to run some monthly reports, manually changing
data in
a Query and the title in a Report (which utilizes the Query) each
month.
That works fine except it's a bit repetitive since I do 6 different
runs each
month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to.
Unfortunately I
feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through
many
help screens and newsgroup items but only got a little further. I'd
take a
class and learn more but I doubt I'll need to do another Macro anytime
in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run
through
the keystrokes and have Access enter the correct Macro protocol? If
so, how
do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate
year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step
2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[Larry Daugherty]

There's quite a difference in your and my take on the purpose and use
of these technical newsgroups. In my perception, these newsgroups
exist so that people who are or who aspire to be developers can get
the help of others after they have made every effort to use the
resources available to them learn how to solve the problem at hand.
All assistance provided in these newsgroups is from volunteers.

Your perception seems to be that when you don't want to make an effort
you'll just ask for a free solution. You're not alone. But, to your
credit, you are more open and honest about it than most who behave
that way.

VBA is huge so no one is going to learn it in a single class. VBA is
in all of the MS Office products and lots of third party products. It
is usually learned in conjunction with one of the development
platforms such as Word, Access or Excel, and using the object model of
that platform.

If your data is not highly confidential you could decode my address
and send me a copy of your application in a Zip file. I'll have a
look.

HTH
--
-Larry-
--

You are correct. It is not worth my time to learn VBA programming just to do
one macro. That's why we have Newsgroups; otherwise everyone should just
spend hours and hours in classes to learn to do simple tasks and stop asking
Q's in Newsgroups. My question was not put forward so that someone would
spend hours and hours trying to figure out an answer for me. Rather, I was
hoping there were a couple simple steps that could be taken, maybe even 9
lines of code that could be handed over with 2 minutes of effort.

Anyway, I do appreciate your straightforward responses which were very
valuable to me. If I'm wrong in concluding from your response, that it's a
lot more complicated than a few simple keystrokes, please let me know.
Sounds like I better learn VBA, continue doing the reports manually, or hire
someone to program it. On the third point, any idea what that might cost and
where I might find interested (and highly competent) programmers for that?

Thanks
----------------------------------------------

" I'd take a class and learn more but I doubt I'll need to do another
Macro anytime in the next few years. Any help you can provide would
be greatly appreciated".

In short, it's not worth YOUR time and effort to help yourself but
would someone else please donate their time and effort so that you
don't have to do so.

Given your attitude, I won't spend much time on this. However, I will
give you some good advice and point you in the right direction.

Access does not have a macro recorder.

Don't use Access macros for anything beyond a test of concept. If
the concept is right, do it over in VBA. As you learn VBA, get rid of
all macros except Autoexec and your menu stuff. You can't step
through macros and, therefore, disallow one of the most useful
debugging tools.

For the report issues. Find or google search on the Access groups on
my name and "launcher". Using the information you find you should be
able to resolve your report issues.

HTH
--
-Larry-
--

I've been using Access to run some monthly reports, manually changing data in
a Query and the title in a Report (which utilizes the Query)

each
month.

That works fine except it's a bit repetitive since I do 6

different
runs each

month. I've never used a Macro before but I created one to try

to
automate

the process, and it did a few of the things I wanted it to. Unfortunately I
feel like I'm a long ways off on what I thought should be a

simple
project,

particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further.

I'd
take a

class and learn more but I doubt I'll need to do another Macro anytime in the
next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just

run
through

the keystrokes and have Access enter the correct Macro protocol?

If
so, how

do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and

change
the

alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step 2) so
that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.

 

[tina]

well, you're welcome re the suggested solution. re the rating responses
issue, i've no idea; i use Outlook Express as my newsreader for these
groups. i don't speak for anyone else, but personally it's not important to
me that my answers get rated; if i'm able to help someone then that person
knows it, and if they post a "thanks" to the thread, then i know it too -
and that's good enough for me.

Thank you for your response and I'll try your suggestion. BTW, I get these
e-mails suggesting that I rate responses but when I click on the links and
look around, I can't find where to thank/rate responders. If you care to
respond or think it's important, any clues on this are appreciated.

Lung
---------------------------------------------------------------------

you can't change a query's SQL statement with a macro, you'd have to do it
in VBA. but there's no need anyway; instead, you can set your query criteria
to refer to a control on a form, and the query will use whatever is typed in
the control. you should be able to use the same technique in the textbox
control on the report - set the ControlSource to refer to the control on the
form, as

=Forms!FormName!ControlName

and in report preview the control will display whatever is typed in the form
control.

hth

I've been using Access to run some monthly reports, manually changing

data
in

a Query and the title in a Report (which utilizes the Query) each month.
That works fine except it's a bit repetitive since I do 6 different

runs
each

month. I've never used a Macro before but I created one to try to automate
the process, and it did a few of the things I wanted it to.

Unfortunately
I

feel like I'm a long ways off on what I thought should be a simple project,
particularly the way things are automated these days. I read through many
help screens and newsgroup items but only got a little further. I'd

take
a

class and learn more but I doubt I'll need to do another Macro anytime

in
the

next few years. Any help you can provide would be greatly appreciated.

Is there a Macro Record feature in Access whereby you can just run through
the keystrokes and have Access enter the correct Macro protocol? If

so,
how

do I get to it?

If none exists, here's what I want to do.
1) Pop open a entry screen so I can enter the appropriate month number
(e.g., 2 = February) of the Report I need to run, and the appropriate year
(e.g. 2006).
2) Open up the "Test" Query in design mode.
3) Select the criteria box of the "Client Code" field, and change the
alphabet listing so that it reads ... Like "[M-Z]*"
4) Select the criteria box of the "Month" field, and change the month
number so that it reads ... Like "02*"
5) Save the Query, Close the Query, and Open up the "Test" Report.
6) Select the text field "Text16" and change it to show the correct
alphabet and month (hopefully automatically based on my input in step

2)
so

that it reads ... "Monthly Report for February 2006 M-Z"
7) Print the report
8) Save and/or Close the Report.
9) Repeat steps 2 to 9 for the other reports.