Private Sub cmdMigMvmt_Click()
On Error GoTo Err_cmdMigMvmt_Click
' Dimension the variables
Dim stDocName As String
Dim strExport As String
Dim strFileName As String
Dim strCoy As String, strDate As String, strFormat As String
strDocName = "tblMonthlyMvmt" ' What table to export
strExport = "JNLEXPO" ' What specification to use, for transfertext
strCoy = Me.cboCoy ' Assign combo box value to string
strFormat = "mmm-yy" ' Assign Format
strDate = Format(Me.cboMonth, strFormat) ' Format combobox date to the required format
' Build the file path
strFileName = "\\canw2k3sql01\Dynamics\GL Imports\" ' What is the default file path
strFileName = strFileName & Format(Me.cboMonth, "yyyy") & " FY\" ' Add the year to the default path
strFileName = strFileName & strCoy & "\" ' Add the company delimiter to the file path
strFileName = strFileName & strDate ' Add the month-year to the file path
strFileName = strFileName & "JOURNAL.csv" ' Add the file anme and tyep to the file path
DoCmd.TransferText acExportDelim, strExport, strDocName, strFileName, True ' Use the transfer text to export the data to the required filepath in a delimited format
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFileName, True ' Use the transferspreadsheet to export as a spreadsheet'
Exit_cmdMigMvmt_Click:
Exit Sub
Err_cmdMigMvmt_Click:
MsgBox Err.Description
Resume Exit_cmdMigMvmt_Click
End Sub