Looping Issue with DoCmd.OutputTo

[Ryan]

Hello. I was hoping that someone may be able to assist with an issue
that I am experiencing. I have created an Access DB which imports an
Excel File with a particular layout and field naming. Next the user
can go into a Form which basically a dynamic query with a friendly
interface that eventually outputs a table that is stored in the DB as
well as exported to a CSV file. The CSV file is then used with a
vendor solution to fill in company information (prevents manual entry).

So in the code, I originally wrote a Do While Statement that stores all
the dynamic information that is used to generate a SQL Statement that
builds the table from the recordset. After the table is built and in
the Do While Statement, I have the following If Then Statement:

If strBuildCounter < strBuildRecords et Then
DoCmd.TransferT ext acExportDelim,, "Export_" & [strTable01],
"C:\My Data\" &
[strTable01] & ".csv", True
DoCmd.GoToRecor d , , acNext
Else
DoCmd.TransferT ext acExportDelim,, "Export_" &
[strTable01], "C:\My Data\" & [strTable01] & ".csv", True
End If

With this and the rest of the code in the event, the process works
great for building all the tables that are in the recordset. However,
after viewing the output in Excel, I learned that TransferText doesn't
keep the formatting that is in the table. After reading through some
of the articles here, I tried switching to using the following
DoCmd.Output line instead of the DoCmd.TransferT ext:

DoCmd.OutputTo acOutputTable, "Export_" & [strTable01], acFormatXLS,
"C:\My Data\" & [strTable01] & ".csv", False, ""

So the output that I get is exactly what I wanted from a formatting
perspective. However, for some reason now it is not building all of the
tables and CSV files. For example, if I have 6 records it will build
and export Table 1, Table 2, and Table 6. I am totally baffled because
the only thing that changed from is the DoCmd.TransferT ext line was
commented out and the new DoCmd.OutputTp line was added in the same
location in the If Then statement.

I threw in message boxes to see what the loop counter and total
recordset count were each time the loop ran through. For both the
DoCmd.TransferT ext and DoCmd.OutputTo line, it would say 1-6, 2-6, 3-6,
4-6, 5-6, and the 6-6. The one thing I did notice is that when I am
running the DoCmd.TransferT ext it is moving through the form records
fine. When I run the DoCmd.OutputTo, it will be on Form Record 1 and
make the Table 1. It will then advance to Form Record 2, make Table 2.
Then it will go back to Form Record 1 and remake Table 1. It will then
go to Form Record 2 again and remake Table 2. Then it will go back to
Form Record 1 and remake Table 1. Finally, it will go to Form Record 6
and make Table 6 and then be done.

If I had just done the DoCmd.OutputTo originally and never tried the
DoCmd.TransferT ext, I would assume the issue was in the looping code.
However, since it works with DoCmd.TransferT ext I am assuming that it
is something else that I am either doing wrong or a Microsoft issue.

I greatly appreciate any assistance on this.

Thanks!
Ryan

[Ryan]

Should be all set. Not sure what is causing the error but ended up
resolving it by adding a new section of code to the Do While statement.
Basically, I figure out what the name of the current record is,
FindFirst statement using the name of the current record, and then tell
it to MoveNext. What ends up happening is if I am on Form 4 it will go
to Form 1 and not run the process and then move to Form 5 and run the
process. Can't find anywhere in the code where it forces it to go to
Form 1 and why this only happens with DoCmd.OutputTo and not
DoCmd.TransferT ext. Oh well, at least it works now.

Thanks,
Ryan

Ryan wrote:

Hello. I was hoping that someone may be able to assist with an issue
that I am experiencing. I have created an Access DB which imports an
Excel File with a particular layout and field naming. Next the user
can go into a Form which basically a dynamic query with a friendly
interface that eventually outputs a table that is stored in the DB as
well as exported to a CSV file. The CSV file is then used with a
vendor solution to fill in company information (prevents manual entry).

So in the code, I originally wrote a Do While Statement that stores all
the dynamic information that is used to generate a SQL Statement that
builds the table from the recordset. After the table is built and in
the Do While Statement, I have the following If Then Statement:

If strBuildCounter < strBuildRecords et Then
DoCmd.TransferT ext acExportDelim,, "Export_" & [strTable01],
"C:\My Data\" &
[strTable01] & ".csv", True
DoCmd.GoToRecor d , , acNext
Else
DoCmd.TransferT ext acExportDelim,, "Export_" &
[strTable01], "C:\My Data\" & [strTable01] & ".csv", True
End If

With this and the rest of the code in the event, the process works
great for building all the tables that are in the recordset. However,
after viewing the output in Excel, I learned that TransferText doesn't
keep the formatting that is in the table. After reading through some
of the articles here, I tried switching to using the following
DoCmd.Output line instead of the DoCmd.TransferT ext:

DoCmd.OutputTo acOutputTable, "Export_" & [strTable01], acFormatXLS,
"C:\My Data\" & [strTable01] & ".csv", False, ""

So the output that I get is exactly what I wanted from a formatting
perspective. However, for some reason now it is not building all of the
tables and CSV files. For example, if I have 6 records it will build
and export Table 1, Table 2, and Table 6. I am totally baffled because
the only thing that changed from is the DoCmd.TransferT ext line was
commented out and the new DoCmd.OutputTp line was added in the same
location in the If Then statement.

I threw in message boxes to see what the loop counter and total
recordset count were each time the loop ran through. For both the
DoCmd.TransferT ext and DoCmd.OutputTo line, it would say 1-6, 2-6, 3-6,
4-6, 5-6, and the 6-6. The one thing I did notice is that when I am
running the DoCmd.TransferT ext it is moving through the form records
fine. When I run the DoCmd.OutputTo, it will be on Form Record 1 and
make the Table 1. It will then advance to Form Record 2, make Table 2.
Then it will go back to Form Record 1 and remake Table 1. It will then
go to Form Record 2 again and remake Table 2. Then it will go back to
Form Record 1 and remake Table 1. Finally, it will go to Form Record 6
and make Table 6 and then be done.

If I had just done the DoCmd.OutputTo originally and never tried the
DoCmd.TransferT ext, I would assume the issue was in the looping code.
However, since it works with DoCmd.TransferT ext I am assuming that it
is something else that I am either doing wrong or a Microsoft issue.

I greatly appreciate any assistance on this.

Thanks!
Ryan