How Do I run a parameter Query Using Access VBA and then assign the result set to a..

[keithaul]

I want to run a parameter query using Access 2016 VBA and then assign the result of a certain column to a variable. Below, between the = signs is my code and after the code are my questions about an error I'm getting:

=============================

Dim MyDb As DAO.Database
Dim Myqdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim MyQueryName As String
Dim strEquipName As String

MyQueryName = "GetQtyByEquipName"
strEquipName = Me.cboEquipName.Value

Set MyDb = CurrentDb()
Set Myqdf = MyDb.QueryDefs(MyQueryName)

Myqdf.Parameters("Equipment Name") = strEquipName

Set rst = Myqdf.OpenRecordset()


If rst.EOF = False Then
MsgBox rst!Quantity

End If

rst.Close
Myqdf.Close
MyDb.Close

==============================================

I'm getting the following error on this line of code - Myqdf.Parameters("Equipment Name") = strEquipName

Runtime error 3265
Item not found in collection

I am not sure what 'item not found in collection' is referring to

Here is my query, SQL view:


SELECT Equipment.EquipName, Equipment.Quantity
FROM Equipment
WHERE (((Equipment.EquipName)=[Equipment Name:]));


Why am I getting this error? and am I accessing the value that is returned by the query correctly?

Thanks
Keith

[SamT]

Kieth,
The # icon on the toolbar will insert CODE Formatting Tags into your post. You can insert them, then place your code between them

OR

Place your code in the post, select all of it, then click the Icon.

I am not familiar with Queries, but try

Myqdf.Parameters(Equipment.EquipName) = strEquipName

Or maybe, (but I doubt it)

Myqdf.Parameters(.EquipName) = strEquipName


I lean towards

Myqdf.Parameters(Equipment.EquipName:=strEquipName)


Whether I am right or wrong, this post will bump your question to the top again and somebody will be along who knows what they are doing. :D

[keithaul]

I just want to let you know that I tried your solution but it didn't work. However in the meantime I had posted the same question elsewhere and someone provided a solution and it worked. Please go to:

https://social.msdn.microsoft.com/Forums/office/en-US/9fa87fb4-26d7-4bdf-83ec-3776f1ac5558/how-do-i-run-a-parameter-query-using-access-vba-and-then-assign-the-result-set-to-a-variable?forum=accessdev&prof=required

I was using the wrong syntax.

Thanks again

[SamT]

You're welcome.