Trying to update query via user input on a form and then export query

[ajaeger]

I have a form (frmExport) where the user enters a date in a field (txtExportDate) and clicks a command button (cmdExport) to 1. browse to find a file to save the exported file as, 2. create strSQL which pulls the txtExportDate, 3. run strSQL so that it modifies a query (qryExportPCTrans) and finally, 4. export the rows from qryExportPCTrans to the file the user selected in step 1.

The code is bombing after the strSQL is created but before it is run. Any suggestions would be helpful. Thanks!


Private Sub cmdExport_Click()

Dim strSQL As String
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

glInitDir = "C:" 'Change your preferred Directory
GetFileInformation (Find_File(glInitDir))
On Error GoTo Err_CantExport

strSQL = "SELECT 'S' AS TRAN_TYPE, tblCCInfo.CC_NUM, tblCCInfo.CC_EXPIRE, tblTransactions.TOTAL_DUES AS AMOUNT, tblTransactions.TRANS_BEGIN_DATE AS TRAN_DATE, tblTransactions.ID FROM tblTransactions INNER JOIN tblCCInfo ON tblTransactions.ID = tblCCInfo.ID where (((tblTransactions.Trans_Begin_Date) = #" & Forms!frmExport!txtExportDate & "#))"

'BOMBS AT THE LINE BELOW AND JUMPS TO ERR_CANTEXPORT
Set qdf = dbs.QueryDefs("qryExportPCTrans&quot
qdf.SQL = strSQL
dbs.QueryDefs.Refresh
DoCmd.Close

'Export the file
DoCmd.TransferText acExportDelim, , "qryExportPCTrans", glPath + "\" + glFileName 'THIS DOES NOT WORK
On Error GoTo Err_CantExport

MsgBox "Successfully exported " & DCount("*", "qryExportPCTrans&quot & " records."
Exit Sub

Exit_cmdExport_Click:
Exit Sub

Err_CantExport:
MsgBox "The file was not exported. Please check the file name and try the export again."
'Exit Sub
Resume Exit_cmdExport_Click

End Sub


Anna Jaeger
iMIS Database Support

http://www.axcedesolutions.com

 

[BillPower]

Hi,

You need to set the dbs to what I assume is the open DB. Haven't tested your code so there could be other errors.

'BOMBS AT THE LINE BELOW AND JUMPS TO ERR_CANTEXPORT
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryExportPCTrans&quot

It would be nice to get a thanks for using my Function in thread705-457897

Bill