This function will work.
Code:
Option Compare Database
Option Explicit
Function ExportProducts() As Long
'Purpose: Export a file for each product.
Dim db As DAO.Database 'This database
Dim rsName As DAO.Recordset
Dim rsProduct As DAO.Recordset 'Each product
Dim strSql As String 'SQL statement
Dim strFile As String 'Export file name
Dim lngCount As Long 'Number of products
Dim sqlName As String
Const strcPath = "C:\EmbDatabases\" 'Path to export to.
Const strcQuery4Export = "selWIP" 'Name of query
Const strcTail = ") ORDER BY PHSOTO;"
Const strcStub = "SELECT PHSOTO, PHSHNM, CHCASN " & _
"FROM tblWIP WHERE (PHSOTO = "
'Initialize
Set db = CurrentDb()
sqlName = "SELECT DISTINCT PHSOTO,PHSHNM FROM tblWIP " & _
" WHERE PHSOTO Is Not Null;"
Set rsName = db.OpenRecordset(sqlName)
rsName.MoveLast: rsName.MoveFirst
'Loop through distinct products, exporting each.
Do While Not rsName.EOF
strSql = strcStub & rsName!PHSOTO & strcTail
Debug.Print strSql
db.QueryDefs(strcQuery4Export).SQL = strSql
strFile = strcPath & rsName!phshnm & ".txt"
DoCmd.TransferText acExportDelim, , strcQuery4Export, strFile
lngCount = lngCount + 1
rsName.MoveNext
Loop
'Clean up
rsName.Close
Set rsName = Nothing
Set db = Nothing
'Return count of products exported.
ExportProducts = lngCount
End Function