Problem using query in rs.open in report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that works fine. It has a WHERE clause "WHERE ID =
[Forms]![frmForm]![RecID]"
When I open a report and do this
strSQL = "SELECT * From qryMyQuery"
rs.open strSQL currentproject.connection
I get a parameter error.
It seems I cannot use the query as SQL for an rs.open ??
When I copied in the query SQL source and changed to
"WHERE ID =" & [Forms]![frmForm]![RecID].
it worked.
Is there any way I can use my query as is in the rs.open statement ???
 
Something like the following should work:

Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim prm As New ADODB.Parameter

cmd.CommandText = "SELECT * FROM Query11"
cmd.CommandType = adCmdText
prm.Name = "Forms!Form1!RecID"
prm.Direction = adParamInput
prm.Type = adInteger
prm.Value = CInt(Forms!frmForm!RecID)
cmd.Parameters.Append prm
cmd.ActiveConnection = CurrentProject.Connection

rs.Open cmd

Obviously, you will need to make the appropriate adjustments to for the
paramater data type, etc.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a query that works fine. It has a WHERE clause "WHERE ID =
[Forms]![frmForm]![RecID]"
When I open a report and do this
strSQL = "SELECT * From qryMyQuery"
rs.open strSQL currentproject.connection
I get a parameter error.
It seems I cannot use the query as SQL for an rs.open ??
When I copied in the query SQL source and changed to
"WHERE ID =" & [Forms]![frmForm]![RecID].
it worked.
Is there any way I can use my query as is in the rs.open statement ???
 
Back
Top