CreateQueryDef Error 3265 (1 Viewer)

[LW_Access]

I am trying to export some data to Excel using some code I found:

Private Sub cmdExcelExport_Click()

Dim strXL_Location As String

Dim cdb As DAO.Database, qdf As DAO.QueryDef
Const tempTableName = "_tempTbl"
Set cdb = CurrentDb

strXL_Location = "X:\Access Database\Spreadsheets\test.xlsx"

On Error Resume Next

DoCmd.DeleteObject acTable, tempTableName

On Error GoTo 0

Set qdf = cdb.CreateQueryDef("")
qdf.SQL = "SELECT * INTO [" & tempTableName & "] FROM [tblStockLogin]"

qdf.Parameters("Box").Value = "AR1N"
qdf.Execute

Set qdf = Nothing
Set cdb = Nothing

DoCmd.OutputTo acOutputTable, tempTableName, acFormatXLSX, strXL_Location, True


End Sub

But this causes the error 3265 on the line:
qdf.Parameters("Box").Value = "AR1N"


I have:
Checked that tblStockLogin is spelled correctly
Checked that Box field exist
Remove the parameters line and it works correctly (ie brings ALL data back from the tblStockLogin table)

I am a newbie (both in Access and in using Forums) but I cannot find an answer (that works for me).

 

[sneuberg]

Could you post your database or a stripped down version with this problem?

 

[MarkK]

I don't see any parameters in your SQL, certainly not one named Box. Keep in mind a parameter is not a field, and if you want to filter the records returned, you need a WHERE clause. Maybe you mean to do something like . . .

qdf.SQL = _
   "SELECT * INTO [" & tempTableName & "] " & _
   "FROM [tblStockLogin] " & _
   "WHERE Box = [prmBox]"
qdf.parameters("prmBox") = "AR1N"

 

[TimTDP]

I am having the exact same problem as LW_Access
I have a attached a copy of a sample database.
It contains only one table, "tblInvoice"
I need to learn how to create a qryDef
So using the table, I need a qryDef that will return all invoices between a date range and where the field "DespatchClosed" is false.

I don't just want a select query with the criteria.
I need the qrydef in code

Many thanks in advance

 

[MarkK]

Tim. I feel like it's LW_Access's thread, (and first post, so welcome, LW_Access!). I'm going to await LW_Access's response on this first.