Query parameter

[taw]

Hi,

I have a Product form that includes a delete button. Before deleting, I want to make sure that this is an unused product id so I have set up several stored queries to check tables for records using this product. In the queries, I am referencing the field ProductID on the current form. If I have the form open and on a particular product then run the query manually, it works fine. In my code, however, I keep getting a "No value given for one or more parameters" error. If I take the SQL from the query and hardcode it into the SELECT statement, it works if I add single quotes to enclose the reference to the form field.


The saved query named qryProductCheckBulk which works fine if I run it manually:

SELECT BulkEquipment.ProductID
FROM BulkEquipment
WHERE (((BulkEquipment.ProductID)=[forms]![frmProducts]![ProductID]));

Here is the pertinant code from the form frmProducts:

Dim qryName

Dim cmd1 As New ADODB.Command
Set cmd1 = New ADODB.Command
Dim rsProductCheck As New ADODB.Recordset

......

qryName = "qryProductCheckBulk"

.......

With cmd1
.ActiveConnection = CurrentProject.Connection
'.CommandText = "Select ProductID From (SELECT BulkEquipment.ProductID FROM BulkEquipment WHERE BulkEquipment.ProductID = '" & [Forms]![frmProducts]![ProductID] & "')"
.CommandText = "Select ProductID From " & qryName
.CommandType = adCmdText
.Execute
End With
Set rsProductCheck = New ADODB.Recordset
rsProductCheck.CursorType = adOpenKeyset
rsProductCheck.LockType = adLockOptimistic
rsProductCheck.Open cmd1



In the above code, the commented out line works, the uncommented throws the error.

Thanks for any insight.

Tom