Command Object

[murthyspd]

Hi,
I am passing a parameter to a query in MS Access using. I am doing this way

Set rsFetch = New ADODB.Recordset
Set cmdDetails = New ADODB.Command
With cmdDetails
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = "SELECT tblDOMS_Orders.Customer_Number, tblDOMS_Orders.Company_number, tblDOMS_Orders.Order_Number, tblFunding_Details.Dealer_Number, tblFunding_Details.FSL_Number, tblFunding_Details.Funding_Company, tblDOMS_Orders.PO_Number, tblDOMS_Orders.Amount, tblDOMS_Orders.sno" & _
" FROM tblDOMS_Orders INNER JOIN tblFunding_Details ON tblDOMS_Orders.Customer_Number = tblFunding_Details.Customer_Number" & _
" WHERE (((tblDOMS_Orders.Company_number)=[CompanyNumber]))" & _
" ORDER BY tblFunding_Details.Funding_Company, tblDOMS_Orders.Customer_Number, tblDOMS_Orders.PO_Number"

End With

Above, I am directly passing the SQL. I have a query created in MS Access. How to pass parameter to that ? As of now I am passing as below
Set rsFetch = cmdDetails.Execute(, lblCompanyNumber.Caption)


but when I try using parameter object, it is giving error that parameter details are not enough. Am I missing something.

' Set prmDetails = cmdDetails.CreateParameter("CompanyNumber", adChar, adParamInput)
' cmdDetails.Parameters.Append prmDetails
' prmDetails.Value = lblCompanyNumber.Caption

 

[KeithG]

You need to include the value in the WHERE clause

WHERE (((tblDOMS_Orders.Company_number)=" & me.lblCompanyNumber.Caption & "))"

Your WHERE clause should look like the above. Also I notice you didn't reference you control properly so I assumed this is on the forms module and used the ME key word.

 

[murthyspd]

Thanks for the answer. Suppose I create a query in MS Access for the SQL that I have given, what should be my CommandType ?