Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Export Current Form to CSV - VBA question

Posted on 2010-11-08
9
Medium Priority
?
793 Views
Last Modified: 2012-05-10
I have access 2007. Capricorn1 did a great job helping me export a current form using two queries to csv, one of which was a subform.  I had a second subform in the stripped down form of the database.  Tried to modify the vba script (as seen below), but I keep getting an error, "the text file specification 'QryCSVSubform2 Export Specification' does not exist. You cannot import, export, or link using the specification.

I'm a bit of a noob, and feel like an idiot for not being able to solve this.

Attached is the modified database Cap1 worked on, as well as the script I tried to modify (which is basically adding Subform2.

Form: Assistance Request Form (vba attached to CSV button on top)
Queries: qryCSVMAIN, qryCSVSubform, qryCSVSubform2

Thank you.

Option Compare Database
Option Explicit
Private Sub cmdExportAsCSV_Click()
Dim qdMain As DAO.QueryDef, qdSub As DAO.QueryDef, qdSub2 As DAO.QueryDef, db As DAO.Database, varID As Long
Dim omSql As String, osSql As String, os2Sql As String, strWhere As String, strWhere1 As String, strWhere2 As String
omSql = "SELECT Assistance_Request.Assistance_ID, Date_Received, Received_by, Referred_By_Type, Referred_By_Name, Contact_Name, Contact_Company,"
omSql = omSql & " Contact_Address_Line1, Contact_Address_Line2, Contact_Address_City, Contact_Address_State, Contact_Address_Zip,"
omSql = omSql & " Contact_Phone, Contact_Fax, Contact_E_Mail, Requestor_Type, Facility_Agency_Interest_ID, Facility_Name,"
omSql = omSql & " Facility_Company, Facility_Address_Line1, Facility_Address_Line2, Facility_Address_City, Facility_Address_State,"
omSql = omSql & " Facility_Address_Zip, Facility_Type, Facility_County, Assistance_Types.Assistance_Type, Facility_Type1,"
omSql = omSql & " Lead_DCA, Request, Final_Response, Date_Close"
omSql = omSql & " FROM Assistance_Request INNER JOIN Assistance_Types ON Assistance_Request.Assistance_ID = Assistance_Types.Assistance_ID"
osSql = "SELECT Referred_to_Agencies.* FROM Referred_to_Agencies"
varID = Me.Assistance_ID
os2Sql = "SELECT Communication Log.* FROM Communication Log"
varID = Me.Assistance_ID
strWhere = " Where Assistance_Request.[Assistance_ID]=" & varID
strWhere1 = " Where [Assistance_ID]=" & varID
strWhere2 = " Where [Assistance_ID]=" & varID

Set db = CurrentDb
Set qdMain = db.QueryDefs("qryCSVMain")
qdMain.SQL = omSql & strWhere
Set qdSub = db.QueryDefs("qryCSVSubform")
qdSub.SQL = osSql & strWhere1
Set qdSub2 = db.QueryDefs("qryCSVSubform2")
qdSub2.SQL = os2Sql & strWhere2
DoCmd.TransferText acExportDelim, "QryCSVMain Export Specification", "qryCSVMain", CurrentProject.Path & "\main.csv", -1
DoCmd.TransferText acExportDelim, "QryCSVSubform Export Specification", "qryCSVSubform", CurrentProject.Path & "\subform.csv", -1
DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

'restore querdy def to original
qdMain.SQL = omSql
qdSub.SQL = osSql
qdSub2.SQL = os2Sql
Set qdMain = Nothing
Set qdSub = Nothing
Set qdSub2 = Nothing
db.Close
End Sub
Private Sub Command85_Click()
Dim x, topsql As String, qd As DAO.QueryDef, db As DAO.Database
x = InputBox("Enter Number Of Records")
If Len(x & "") > 0 Then
    topsql = "select top " & x & " Contact_Name,Contact_Company, Contact_Address_Line1, Contact_Address_Line2, Contact_Address_State, Contact_Address_Zip, Contact_Phone, Contact_Fax, Contact_E_Mail, Requestor_Type, Facility_Agency_Interest_ID, Facility_Type, Facility_Name, Facility_Company, Facility_Address_Line1, Facility_Address_Line2, Facility_Address_City, Facility_Address_State, Facility_Address_Zip, Facility_County, Facility_Type1"
    topsql = topsql & " FROM Assistance_Request"
    topsql = topsql & " ORDER BY Assistance_ID DESC"
End If
Set db = CurrentDb
Set qd = db.QueryDefs("qryTopX")
qd.SQL = topsql
DoCmd.OpenQuery "qryTopX"
End Sub
Private Sub Contact_Address_City_AfterUpdate()
[Contact_Address_State] = "KY"
End Sub
Private Sub Facility_Address_City_AfterUpdate()
[Facility_Address_State] = "KY"
End Sub
Private Sub Form_Load()
Application.SetOption "Default Find/Replace Behavior", 1
End Sub
Private Sub Received_by_AfterUpdate()
[Lead_DCA] = [Received_by]
End Sub
Private Sub Command82_Click()
' Print Current record
 If IsNull(Me!Assistance_ID) Then
 MsgBox "Please select a valid record", _
 vbOKOnly, "Error"
 Exit Sub
 End If
 DoCmd.OpenReport "Assistance_Request1", , , _
 "Assistance_ID = " & Me!Assistance_ID
 End Sub
 

exchange.accdb
0
Comment
Question by:flashinglemur
  • 5
  • 3
9 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34086579
you have to create the Export  Specification first..

here is how to create the export specification

1.right click on the table or query
2.select export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim or Fixed) width
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 34086584
0
 
LVL 1

Author Comment

by:flashinglemur
ID: 34086964
Capricorn1, you explained it nicely.  Problem and question...

When exporting the text file, it only indicates .txt, not .csv.  Do I just rename my txt file as csv? My exported files name from this process is qryCSVSubform2.txt

These are the current DoCmd lines.  The first two added by you, and the third by me:

DoCmd.TransferTcExportDelim, "QryCSVMain Export Specification", "qryCSVMain", CurrentProject.Path & "\main.csv", -1

DoCmd.TransferText acExportDelim, "QryCSVSubform Export Specification", "qryCSVSubform", CurrentProject.Path & "\subform.csv", -1

DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

The following is the line you suggest adding - actually replacing my subform2 line ...

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True

So ...

Is my current subform2 line not correct?

Also, now that I have exported the qryCSVSubform2.txt (csv) file, what am I supposed to do with that file?  I didn't see the two other files that must have been generated when you created main.csv and subform.csv.

As things are now, it's still giving me the same error, "the text file  specification 'QryCSVSubform2 Export Specification' does not exist. You  cannot import, export, or link using the specification."

Thank you again.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34087037
with this error

<As things are now, it's still giving me the same error, "the text file  specification 'QryCSVSubform2 Export Specification' does not exist. >

you probably missed a step in the procedure i posted above..

the suggestion from the step posted above

   DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myCsv.csv", True

is just a format that you have to follow..



your transfer text  code for subform2 format is correct

DoCmd.TransferText acExportDelim, "QryCSVSubform2 Export Specification", "qryCSVSubform2", CurrentProject.Path & "\subform2.csv", -1

except that the export specification is incorrect..

try to do it again, reading and following the steps carefully..

0
 
LVL 1

Author Comment

by:flashinglemur
ID: 34087387
The attachment contains screen captures of the steps I followed for the export specification.

Still getting same error.

I am not sure why you said the export specification was not correct for subform2.  It's the same format as subform and main, which you successfully created and worked beautifully.

Finally, the file I generated for export specification - after creation is it necessary to keep it or refer to it?

Sorry...
steps.zip
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34087487
i meant to say  "you are getting the error because the export spec for subform2 does not exists."
0
 
LVL 1

Assisted Solution

by:flashinglemur
flashinglemur earned 0 total points
ID: 34087815
Ugh.  I found the problem.  I had a table name with a space, "communication log".  Apparently that's not good.  Changed the table name to "communication_log", made corresponding changes on the command line, and now it works like a charm. Still, full points to Capricorn1.
0
 
LVL 1

Author Comment

by:flashinglemur
ID: 34087907
Capricorn1 guided me to the answer.  With further research I found I could only have one name for the table, not two (communication log)
0
 
LVL 1

Author Closing Comment

by:flashinglemur
ID: 34126709
communication log table should be one word instead of two.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question