VBA - Passing a parameter to a query

[andyb7901]

Hello!

I have recently started to get back into VBA after a while out, and am having problems at the first hurdle.

I have the below simple query with a Parameter which asks for an ID.


PARAMETERS PearlSearch Double;
SELECT tmp_Pearl.ID, tmp_Pearl.MILESTONE
FROM tmp_Pearl
WHERE tmp_Pearl.ID = [PearlSearch]

I am then using VBA to try and pass the parameter from my Form into the query using the below code;


Private Sub cmdSearchValue_Click()


Dim myDB As DAO.Database
Dim myQDF As DAO.QueryDef
Dim rst As DAO.Recordset


Dim myQueryName As String
Dim myParameter As Double


myQueryName = "qryPearlSearch"
myParameter = cmbID.Value


Set myDB = CurrentDb()
Set myQDF = myDB.QueryDefs(myQueryName)
myQDF.Parameters("PearlSearch") = myParameter

Set rst = myQDF.OpenRecordset()

If rst.EOF = False Then
Me.txtMilestone.Value = rst("MILESTONE")
Else
Me.txtMilestone.Value = "Not Found"
End If

rst.Close
Set rst = Nothing


End Sub


If I run the query manually, my results are returned perfectly. However, when I run in VBA I get an error 3265 - Item not found in this collection on the below line.


myQDF.Parameters("PearlSearch") = myParameter

I have tried all sorts but cant get it working. Any help would be appreciated.

[jonh]

I can't see anything wrong with it. Check your spelling.
Instead of giving the parameter name you could just try the index e.g. parameters(0)

my simplified version


Dim rst As DAO.Recordset

With CurrentDb.QueryDefs("qryPearlSearch")
.Parameters("PearlSearch") = cmbID.Value
Set rst = .OpenRecordset
End With


If rst.EOF = False Then
Me.txtMilestone.Value = rst("MILESTONE")
Else
Me.txtMilestone.Value = "Not Found"
End If
rst.Close