Excel Excel VBA to run Access Parameter Query Error 3265

[pedromartinez]

Hello,

I am new to VBA and have successfully got some VBA code in Excel to open and access a parameter query in Access 2003. When I run the query without defining the parameters it promptly returns all results but when I add in the query parameters I get a Run-Time error 3265 'Item not found in collection'. I've looked closely at both the field name spellings in the VBA and the Access query and they are both the same.

Here is the code. The error occurs in step 3 at the .parameter line

Sub RunParameterQuery()
'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("D:\Data\Database Tools\Marketing_Database.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("qry_parameter_test")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Gender]") = Range("D3").Value
.Parameters("[Age]") = Range("D4").Value
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'Step 5: Clear previous contents
Sheets("Sheet2").Select
ActiveSheet.Range("A6:K10000").ClearContents

'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A7").CopyFromRecordset MyRecordset

'Step 7: Add column heading names to the spreadsheet
For i = 1 To MyRecordset.Fields.Count
ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i

MsgBox "Your Query has been Run"

End Sub


Thanks in advance