pass a variable to parameter query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to pass var1 to a paramater query to automate the export of multiple files.

I know var1 is picking up the correct values and that when I run the query and type the parameter manually it works ok.

Can anyone see issues with the code?

Dave

Sub DataExport_loop()

Dim strfile, expdir As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_Company", dbOpenTable)

expdir = CurrentProject.Path & "\Export\"
strfile = (Dir(expdir & "*.txt"))

With rst
Do While Not .EOF

MsgBox (rst!ASXCode.Value)
var1 = rst!ASXCode.Value

DoCmd.SetWarnings False
DoCmd.TransferText acExportDelim, "ImportSpec", "qry_ASXData_Export", expdir & strfile, False

MsgBox ("Step 1: Data Exported")
rst.MoveNext
Loop
End With
End Sub
 
If you have a form open as you do this you may be able to place a hidden
textbox on the form, place the value of var1 into the textbox, and refer to
the textbox in the criteria of the query. Another option would be to use a
SQL statement in the code instead of the query. Concatenate in the value of
var1 when you create the SQL statement.

--
Wayne Morgan
Microsoft Access MVP


Dave said:
I am trying to pass var1 to a paramater query to automate the export of multiple files.

I know var1 is picking up the correct values and that when I run the query
Click to expand...
and type the parameter manually it works ok.
Can anyone see issues with the code?

Dave

Sub DataExport_loop()

Dim strfile, expdir As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_Company", dbOpenTable)

expdir = CurrentProject.Path & "\Export\"
strfile = (Dir(expdir & "*.txt"))

With rst
Do While Not .EOF

MsgBox (rst!ASXCode.Value)
var1 = rst!ASXCode.Value

DoCmd.SetWarnings False
DoCmd.TransferText acExportDelim, "ImportSpec",
Click to expand...
"qry_ASXData_Export", expdir & strfile, False
 
Back
Top