Access 97 module using DoCmd 1

[swaimdw]

Programmer thrust into Access using VB module.
I need to create a one step process of exporting a .txt file using a querry and filtering by one argument. I am working in unfamiliar territory with this one. I have been using the DoCmd to achieve this, but maybe there is a better alternative. My code is below. Everthing works but the DoCmd.ApplyFilter. I have tried many variations.
The error includes:
______________________
Run-time error '2501':
The ApplyFilter action was canceled.
You used a method of th DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in the dialog box.
_____________________________________________________________
Obviously I did not
Thanks for anyone's help.
Darrell
*************************************************************
Option Compare Database
Option Explicit

Private Sub Export()
Dim strYear As String
Dim strMessage As String
Dim intYear As Integer

Dim strM As String ' Month
Dim strD As String ' Day
Dim strY As String ' Year
Dim strDate As String ' for mm_dd_yyyy
Dim strFileNameA As String ' A drive file name

Dim strFilter As String

strYear = DatePart("yyyy", Date) ' year function
strMessage = "ENTER A FOUR DIGIT YEAR" & vbCrLf & vbCrLf & _
"Only records of this date will be included in this export." & vbCrLf & _
"Enter only one year for this procedure."
intYear = InputBox(strMessage, "YEAR ?", strYear)

strM = DatePart("m", Date) ' month function
strD = DatePart("d", Date) ' day function
strY = DatePart("yyyy", Date) ' year function
strDate = strM & "_" & strD & "_" & strY
strFileNameA = "A:\XTmHst.txt" ' A drive file name

strFilter = "Training_Year = " & intYear

DoCmd.OpenQuery "Export"
DoCmd.SelectObject acQuery, "Export"

' Here is the problem ********
DoCmd.ApplyFilter "Export", strFilter
' Without this line the module runs fine with no filter
' ****************************

DoCmd.TransferText acExportFixed, "ExportSpec", "Export", strFileNameA
DoCmd.Close acQuery, "Export", acSaveNo

End Sub

 

[chrissie1]

i would replace this with this

DoCmd.OpenQuery "Export"
DoCmd.SelectObject acQuery, "Export"

' Here is the problem ********
DoCmd.ApplyFilter "Export", strFilter
' Without this line the module runs fine with no filter
' ****************************



on error resume next 'or something to like that if the table export doesnt exist
docmd.runsql "DROP TABLE tbl_export"
docmd.runsql "SELECT INTO tbl_export FROM export WHERE training_year = " & int_year
DoCmd.TransferText acExportFixed, "ExportSpec", "tbl_Export", strFileNameA

or

docmd.setwarnings false
docmd.runsql "DROP TABLE tbl_export"
docmd.runsql "SELECT INTO tbl_export FROM export WHERE training_year = " & int_year
DoCmd.TransferText acExportFixed, "ExportSpec", "tbl_Export", strFileNameA
docmd.setwarnings true Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[swaimdw]

Thanks Chrissie1
I'll give it a try.
I have progressed somewhat... here's a question for all.

I have a working code, but the system overlooks the variable for year and repeatedly asks for it again.
The code follows.
[I hear Louis singing in my head]
Good weekend!
Darrell
*************************************************************
'Get Year from the user
strYear = DatePart("yyyy", Date) ' year function
strMessage = "ENTER A FOUR DIGIT YEAR" & vbCrLf & vbCrLf & _
"Only records of this date will be included in this export." & vbCrLf & _
"Enter only one year for this procedure."
intYear = InputBox(strMessage, "YEAR ?", strYear)

' Return reference to current database.
Set dbsDatabase = CurrentDb
strSQL = "SELECT * FROM Export WHERE [Training_Year] = intYear"

' Create new query.
Set qdfstrQname = dbsDatabase.CreateQueryDef(strQname, strSQL)

With DoCmd
.TransferText acExportFixed, "ExportSpec", strQname, strFileNameA
.TransferText acExportFixed, "ExportSpec", strQname, strFileNameC
.DeleteObject acQuery, strQname
End With

Set dbsDatabase = Nothing

 

[chrissie1]

strSQL = "SELECT * FROM Export WHERE [Training_Year] = intYear"

needs to be this

strSQL = "SELECT * FROM Export WHERE [Training_Year] = " & intYear

if intyear is numeric Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[swaimdw]

Outstanding!
Thanks Chrissie1, that was the key.
Darrell