RunMacro - error when repeated

[Jennifer]

I create tables for nationwide medicare provider audits. (every doctor,
hospital or facility that provides care to medicare patients.)

The audits are based on contract number and county served.

I am currently working on an audit of 52 contracts covering over 900 counties.

I gather all of the information on a provider table (HSD2 all) and a
hosp/facilites table (HSD3 all)


I created a form that lists each contract/county combination.

From that form three tables are created and exported to excel and the form
advances to the next record. (A county/contract specific HSD2, HSD3 and a
summary HSD1)

If I need all three tables for all counties I use a macro named export all.
This works beautifully - it creates the three tables, exports them
appropriately. (all HSD1s for a contract in an excel spreadsheet for the
contract with a separate tab for each county, all HSD2s for a contract in an
excel spreadsheet for the contract with a separate tab for each county, all
HSD3s for a contract in an excel spreadsheet for the contract with a separate
tab for each county.

I have three other macros that create just an HSD1 or and HSD2 or an HSD3.
All work correctly if I run the manually press the create HSDX button. (900+
times)

When I create a macro to repeat the create any one of the individual tables
the exports are all messed up. They are all under the first contract number
and it overwrites the tabs, so I have counties from several contracts and am
missing a huge number of counties.

I have played with this for hours and can get any two tables to export
correctly, but can't just get an individual type to output correctly.

Any ideas?

 

[Ken Snell]

You'll need to give us more details about the macro's actions (do you mean
VBA code when you say macro?). Also, see my web page for various examples of
exporting to EXCEL from ACCESS, perhaps they'll provide helpful information
as well.

 

[Jennifer]

Macro: Run All HSD Tables
Open Query (10) Make HSD1 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & " HSD1.xls")
DeleteObject:=[Forms]![County with Contracts]![County]

Open Query (20) Make HSD2 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & " HSD2.xls")
DeleteObject:=[Forms]![County with Contracts]![County]

Open Query (30) Make HSD3 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & " HSD3.xls")
DeleteObject:=[Forms]![County with Contracts]![County]
GoToRecord (next)

_____________
When I use the macro "Repeat Run All HSD Tables" and Run Macro with a repeat
count of the number of counties it works perfectly.

If I try to run any two of the exports it works perfectly, When I parse my
query down to just:

Open Query (30) Make HSD3 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & " HSD3.xls")
DeleteObject:=[Forms]![County with Contracts]![County]
GoToRecord (next)

I can run the macro manually without errors, but if I use a runmacro with a
repeat count the exports are all screwed up. The exports end up in one excel
file with the first contract number and only some of the counties are in the
export.

The only thing that I have come up with is that the commands from access are
too fast for excel to execute? This is just a theory and may not have any
bearing on the real solution. Is there a way to make the repeat to pause
for a moment before repeating?

 

[Ken Snell]

You've left out some of the arguments for some actions, so I'm not clear
about what you're doing. What are you renaming in each of the three macro
actions? What object are you deleting? What object are you exporting?

Also, why are you opening a query before you do the Rename, etc. steps? You
do not need to "run" a query that is being used by other objects; those
objects will run the query when they need it (e.g., TransferSpreadsheet will
run the query to get the data to be exported).

If you have the "filtering" data in a table, which you're displaying on the
form, then you can use VBA code to loop through the data without having to
display the data on a form and "walk" through the form's data. See these
articles for examples of the VBA code:

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate EXCEL files via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSepFiles


Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate Worksheets within one EXCEL file via
TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#FilterExportSameFile


--

Ken Snell
http://www.accessmvp.com/KDSnell/

Macro: Run All HSD Tables
Open Query (10) Make HSD1 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & "
HSD1.xls")
DeleteObject:=[Forms]![County with Contracts]![County]

Open Query (20) Make HSD2 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & "
HSD2.xls")
DeleteObject:=[Forms]![County with Contracts]![County]

Open Query (30) Make HSD3 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & "
HSD3.xls")
DeleteObject:=[Forms]![County with Contracts]![County]
GoToRecord (next)

_____________
When I use the macro "Repeat Run All HSD Tables" and Run Macro with a
repeat
count of the number of counties it works perfectly.

If I try to run any two of the exports it works perfectly, When I parse
my
query down to just:

Open Query (30) Make HSD3 based pm Form)
Rename (=[Forms]![County with Contracts]![County])
Transfer Spreadsheet (="O:\Ovations\_ReoccurringProjects\CMS\_Current
Deliverables\" & [Forms]![County with Contracts]![ContractNbr] & "
HSD3.xls")
DeleteObject:=[Forms]![County with Contracts]![County]
GoToRecord (next)

I can run the macro manually without errors, but if I use a runmacro with
a
repeat count the exports are all screwed up. The exports end up in one
excel
file with the first contract number and only some of the counties are in
the
export.

The only thing that I have come up with is that the commands from access
are
too fast for excel to execute? This is just a theory and may not have any
bearing on the real solution. Is there a way to make the repeat to pause
for a moment before repeating?