Access VBA using Transfertext to create files with criteria

[ryandougherty]

I am creating multiple txt files from a query, and I need the VBA script to input criteria from a list into the query each time it is saved.

My issue is that the docmd.transfertext method does not SEEM to allow me to do this.

Any suggestions?

Here is the code thus far: (ignore REM'd out lines)

==============================
Public Sub cmdPublish_Click()


Dim dbs As Database, rstList
Dim LIST As String
Dim SECTION As String

'1ST SECTION

Set dbs = CurrentDb
Set rstList = dbs.OpenRecordset("q_eventlawson_cd_list")
rstList.MoveLast
rstList.MoveFirst
Do Until rstList.EOF
Let LIST = rstList!EventLawson_cd
DoCmd.TransferText acExportDelim, "Q_export_by_eventname Export Specification", "q_export_by_eventname", Forms![form_Rpt_Publishing]![txtPath] & LIST & "_Email_FY06-FY07.csv", True
'DoCmd.Close

rstList.MoveNext
Loop

End Sub

==============================

 

[JerryKlmns]

ryan

I think that you need to change parameter value of your saved query for every record you get from your rstList.
Something

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDefs
Dim rstList As DAO.Recordset
Dim SECTION As String

    '1ST SECTION
    
Set dbs = CurrentDb
Set rstList = dbs.OpenRecordset("q_eventlawson_cd_list")
Set qdf = dbs.QueryDefs("q_export_by_eventname")
With rstList
   .MoveLast
   .MoveFirst
   Do Until .EOF
      qdf.Parameters("YourParameterNameHere")= .Fields("EventLawson_cd")
      DoCmd.TransferText acExportDelim, "Q_export_by_eventname Export Specification", "q_export_by_eventname", Forms![form_Rpt_Publishing]![txtPath] & .Fields("EventLawson_cd") & "_Email_FY06-FY07.csv", True
      .MoveNext
   Loop
   .Close
End With
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
End Sub