write data from report to CSV file

[Brad_512]

Hello, what I'm trying to do is write data from a report (manifest report) that was generated. The report is ever changing, so I use the unique manifest number generated that's part of the report to determine what data I need to write to the CSV file. Also, in this CSV file, I am trying to use a query that pulls different fields from 3 separate tables based on join properties (union query). This query is also takes all this data from last manifest number generated. I am opening an ADO connection and am afraid this is causing the problem that's won't let me open a variable because it's already closed.

Here's what I have so far:

Sub ManifestCSV()

Dim objRS As ADODB.Recordset
Dim cnObj As cnOne
Dim cnDB As Variant
Dim zSQL As String
On Error GoTo ErrorHandler

'[Open connection to database]
Set objRS = New ADODB.Recordset
Set cnObj2 = New cnOne
Set cnDB = cnObj2.cnOpenDB2

Open "C:\EagleProject\EagleCustApp\ConvertedCSV\" & txtIBManifest & ".csv" For Output As #1

zSQL = ""
zSQL = zSQL & "SELECT DISTINCT K95_Template.K95_ID, K95_Template.K95_PRONUM, K95_Template.K95_JOBID_MOTHER_PALLET, "
zSQL = zSQL & "K95_Template.K95_MOTHER_PALLET_ID, K95_Template.K95_SHIPPER, Consignee_Facility.FACILITY_NAME, "
zSQL = zSQL & "K95_Template.K95_NUMBER_OF_COPIES, K95_Template.K95_WEIGHT, K95_Template.K95_UNIQUECONTAINERID_MPAL, "
zSQL = zSQL & "MANIFEST_MASTER.MM_IB_MANIFEST, MANIFEST_MASTER.MM_TRIP_NUMBER, K95_Template.K95_JOBNAME_VERSION "
zSQL = zSQL & "FROM (MANIFEST_MASTER INNER JOIN K95_Template ON MANIFEST_MASTER.MM_MASTER_ID = K95_Template.K95_ID) "
zSQL = zSQL & "LEFT JOIN Consignee_Facility ON K95_Template.K95_CONSIGNEE = Consignee_Facility.CONSIGNEE "
zSQL = zSQL & "WHERE MM_IB_MANIFEST = " & txtIBManifest & " ORDER BY K95_Template.K95_PRONUM;"



If objRS.State = 1 Then objRS.Close
'cnObj2 was formally defined as tempcnObj2 (check ado module for)
objRS.Open zSQL, cnDB, adOpenDynamic, adLockOptimistic '[LOCAL DB]

objRS.MoveFirst
Do Until objRS.EOF 'it sends it to end of sub right here because it is closed

'*Write #1, objRS![K95_Template.K95_PRONUM], objRS![K95_Template.K95_JOBID_MOTHER_PALLET], objRS![K95_Template.K95_MOTHER_PALLET_ID], objRS![K95_Template.K95_SHIPPER], objRS![Consignee_Facility.FACILITY_NAME], objRS![K95_Template.K95_NUMBER_OF_COPIES], objRS![K95_Template.K95_WEIGHT], objRS![K95_Template.K95_UNIQUECONTAINERID_MPAL], objRS![MANIFEST_MASTER.MM_IB_MANIFEST], objRS![MANIFEST_MASTER.MM_TRIP_NUMBER], objRS![K95_Template.K95_JOBNAME_VERSION]

objRS.MoveNext
Loop

Close #1


Set objRS = Nothing
Set cnDB = Nothing

Exit Sub
ErrorHandler:

End Sub


When I get to the Do Loop, it states that the operation is now allowed when the object is closed. How can I open it to where I can get inside the do loop and write the data to my file? Any help would be much appreciated!