Looping through Macro

[Guest]

I have a loop within a loop in a macro. It is working, however this causes an
error if it loops more than the allowed 20 times. I am not a coder, but may
have to do this with code. Can someone help with code? Or maybe I am just
doing this all wrong. Need to run report for each monthly period for each of
5 or more waste types.

Parameter inputs on a form:
FromDate = 1/1/06
ToDate = 3/30/07

Set ProcessedDates for 1st month (1/1/06 - 1/31/06)
Count records in WasteType and set RecCount
Set ProcessedCount = 1
Read first record in WasteType Table and set Waste Type

First loop:
Process and append data to table (for later export to Excel)
Run macro (Second Loop) to select next WasteType
If we have run all Waste types for the month, ( RecCount =ProcessedCount)
Increment the month, start and end dates
Set Waste Type to first record
And go to top of First Loop
Else go to next statement
If we have processed all requested months (ToDate = ProcessedDate)
Stop Macro


Second Loop:
If not at EOF (ProcessedCount = RecCount)
Read next record in Waste Type Table to get next WasteType
Increment ProcessedCount by 1
Go to First Loop
Else
Go to First Record in WasteType Table
Set ProcessCount = 1
Go to First Loop

Hope this makes sense. Thanks for any help.

 

[Steve Schapel]

GeorgieGirl,

Can you give the details of the actions you have in your macro? And can
you explain what you mean by "the allowed 20 times"?

 

[Guest]

Application is for automotive service stations. There are five different
types of wastes that are picked up on a scheduled basis. A history of these
waste pickups is stored in a table. There is also a table that contains a
list of all sites. A query will first find all sites that DID have waste
pickups for each of the different waste types for a specific month. The
result of that query is then used to run against the master list of sites to
determine which, if any, sites DID NOT have any waste pickups of the chosen
type, for the chosen month. We then select the next waste type for that
month and repeat the process until all waste types have been processed. Then
the month is incremented and the process is repeated for the next month.
This continues until the selected range of months have been processed. The
result of each query run is appended to a table and when all processing is
complete it then creates an Excel spreadsheet.

Other actions in the loop are things such as bumping counters, setting the
parameters for the next run of the query, etc.

The logic all runs fine if they select a up to a two month range. If more
than a two month range is selected than the macro aborts with a message
"Macro's may call themselves a total of 20 times."

Hope this is the information you are looking for and clarifies what I am
trying to do.

 

[Steve Schapel]

GeorgieGirl,

Thanks. Well, your explanation did not help me to answer your
question... for that I need the details of your macro, for example the
exact name of the macro, the exact Actions of the macro, and Conditions
in the macro, and the Arguments of each macro action.

It is interesting about the "20 times" message. I have never seen this
before. I guess it is very unusual to have a macro "call itelf".
Normally a RunMacro action would be calling a separate macro, in which
case there is definitley no limit to the number of recursions.

But anyway, your explanation did alert me to the fact that you are
probaqbly doing this way more complicated that it needs to be. If you
like, we can divert off to an exploration of other options. On the
basis of what you have said so far, it seems to me that you could make a
single query to return the data you want. Can you let me know the
details of the table structure? ("Details" means names of tables, names
of fields, data types, relationships between tables, and meaning of data
if it is not obvious from the field name).

 

[Guest]

Steve, I am more than happy to supply any information that will aid in your
help and simplification of my need. Here is the info you requested on the
table involved. I am not including any macro information as you have
suggested the exploration of a simpler way to do this chore.

Tables:

tbl_Facilities (A list of all facilities)
Customer Text
Facility Number Text (This identifier can include letters and
must have
allow leading zeros)
Facility Name Text
Closed Yes/No
Primary key = Combination of Customer & FacilityNumber

tbl_TotalWasteHistory
Customer Text
FacilityNumber Text
ServDate Date (Date Waste was picked up)
WasteType Number (Identifier of type of waste picked up)
Gallons Number (amount by gallon of waste pickedup)
Primary key = Combination of FacilityNumber,ServDate,WasteType (insures
that no duplicate entries will occur)

tbl_WasteType
WasteType Text (oil, water, etc.)
WasteNumber Number (industry specific number for a waste type, not
sequential numers)
Primary key = WasteNumber

Relationships:
tbl_Facilities has a one to many relationship with tbl_TotalWasteHistory
based on Customer and FacilityNumber

Additional table currently being used as a parameter table that has a single
record keyed into a form by the user:

tbl_WstZeroParams
FromDate Date (Beginning of period that user requests -
validated to
be the first day of the month)
ToDate Date End of period that the user requests -
validated to
be the last day of the month)
WasteNumber Text The type of Waste the user is requesting, or an "*"
to select each type for each month.
Note: this field
becomes text here instead of a number
field to allow
the input of an "*" to select all.
ProcessFrom Date Hidden field on input form to store and
increment ToDate
during processing uing DateAdd("m",1"
ProcessTo Date Hidden field on input form to store and
increment
FromDate during processisng
RecCount Number Hidden field on the form that contains the
current number
of waste types. This is currently 5
but did not want to
hard code this in case a new waster
type needed to be
added. A query counts the records in
the tbl_WasteTypes
and sets this value to the result
CurrCount Nunmber Hidden field on the form that is incremented
after each
waste type is processed for the
current month being
processed.

Briefly the requirements: Build an Access table to be exported to Excel
that contains a list of any facility that did not have any waste pickup
during a monthly period for one specific or all waste types. User can input
a range of dates, i.e.,
1/1/05 - 8/31/06, but report must be by month and waste type. No data is
kept on the basis of facilities without waste pickup. Therefore, we must
back into this using a query of "Facilities without WasteHistory for each
WasteType" for each sselected month within the input range.

Hope this provides what you need to help me. I can't thank you enough for
your patience.

 

[Steve Schapel]

GeorgieGirl,

Thanks for the clarification. It helps a lot.

I am puzzled by the requirement "Build an Access table ..." Are you
aware that you can export to an Excel workbook (I assume you would be
using a TransferSpreadsheet action in your macro?), directly from a
query. I.e. you don't need a table. I guess that is what I was
thinking all along. Make a query to return the data you want, and then
just export it. You mentioned the "Facilities without WasteHistory for
each WasteType" query. It sounds like you have already built this query
- is this correct? So, doesn't this query give you the data that you
need? Why not just export this query, and that's that?

 

[Guest]

Yes, Steve, I do have the query working that will give me data for one month
and one waste type. Instead of exporting the results of the query, I append
it to a table, set the next parameters for the query, (i.e, next waste type,
or next month and first waste type), run the query, append it, set next
parameters, etc., until I have all the requested months with all the waste
types. I then use a TransferDatasheet action in my Macro to export all of
the data sorted by Date, Facility, and WasteType.

Have you been saying that there is a way to get this data with a single
query by month and waste type within month?

 

[Steve Schapel]

GeorgieGirl,

Ok, thanks again for the further clarification. So, your existing query
only does one month and one waste type. Can you give details of what
you have so far? It should be pretty easy to modify it to cover all
your required data. But at least now I start to understand why you were
thinking to use looping code to achieve your purpose .

 

[Guest]

Steve,

I have completely re-worked this routine and iwas able to simplfy it
greatly. It is now working like a charm. Two things that were causing
trouble that I have corrected.
1) At the end of my loop, I was calling a macro to bump the dates. That
macro then called my original loop and this set up the problem that created
the error "A macro can only call itself 20 times." I know have the routine
to bump the date as part of the loop, but it will only run when we have
reached the end of all the waste types (If currec > reccount as a condition
of the macro action.
2) I was thinking of the RunMacro expression as being a "run until" instead
of a "run while" expression and so I had the logic backwards.

Thanks so much for your help and patience.