尝试为访问中的查询设置 QueryDefs 参数的 VBA 错误

VBA Error trying to set QueryDefs parameters for a query in access

提问by Codejoy

I have this qry in access, if I go into its design it has a criteria (which as I understand it is a parameter).

我可以访问这个 qry，如果我进入它的设计，它有一个标准（据我所知，它是一个参数）。

The Report this qry is based off of works great, click on it a little thing pops up asks the required info and off it goes. In code I am trying to do this and get a

此 qry 的报告基于效果很好，单击它会弹出一个小东西，询问所需的信息，然后就可以了。在代码中，我正在尝试执行此操作并获得

Run-time error '424'
Object Required

the offending line:

违规行：

 qdf.Parameters("Insurance Name").Value = inputStr

Lines before it:

它之前的行：

Set qfd = CurrentDb.QueryDefs("qryInsGrpRoster")

 Dim inputStr As String
 inputStr = InputBox("Enter Insurance")
 'Supply the parameter value
 qdf.Parameters("Insurance Name").Value = inputStr

inputStrdefinitely equals the value, it fails though.

inputStr绝对等于价值，但它失败了。

The criteria line in the qry is:

qry 中的标准行是：

Like "*" & [Insurance Name] & "*"

Do I need the likes and all that to set that parameter?

我是否需要喜欢和所有这些来设置该参数？

采纳答案by HK1

The parameters property of an Access Query is read only.

Access 查询的参数属性是只读的。

You have basically two options here that I can think of right off.

您基本上有两种选择，我可以立即想到。

The first is to just completely rewrite the SQL of the saved query each time you need to use it. You can see an example of this here: How to change querydef sql programmatically in MS Access

第一个是在每次需要使用它时完全重写保存的查询的 SQL。你可以在这里看到一个例子：How to change querydef sql programmatically in MS Access

The second option is to manually set the RecordSource of the report each time it opens. Using this method you will not use a saved query at all. You'll need to set/store the entire SQL statement in your code when the report opens, ask for any input from the user and append the input you get to your SQL statement. You could setup a system where the base SQL is stored in a table instead but, for simplicity, that's not necessary to achieve what you're trying to do here.

第二个选项是每次打开报表时手动设置它的 RecordSource。使用此方法，您根本不会使用已保存的查询。当报告打开时，您需要在代码中设置/存储整个 SQL 语句，询问用户的任何输入并将您获得的输入附加到 SQL 语句中。您可以设置一个系统，将基本 SQL 存储在表中，但为简单起见，这不是实现您在此处尝试执行的操作所必需的。

MS Access does allow you to use parametrized queries in the manner you're attempting here (not the same code you have), but as far as I know, it would require you to use Stored Procedures in MS SQL Server or MySQL and then you'd need to use ADO. One big downside is that Access reports cannot be bound to ADO recordsets so this isn't really an option for what you're trying to do in this particular instance.

MS Access 确实允许您以您在这里尝试的方式使用参数化查询（与您拥有的代码不同），但据我所知，它需要您在 MS SQL Server 或 MySQL 中使用存储过程，然后您需要使用ADO。一个很大的缺点是 Access 报告不能绑定到 ADO 记录集，因此这不是您在此特定实例中尝试执行的操作的真正选择。

回答by HarveyFrench

in Access 2010 and 2013

在 Access 2010 和 2013 中

This uses DAO and might be of interest

这使用了 DAO 并且可能会引起人们的兴趣

DIM MyQryDef as querydef
Dim a as string 

a = ""

a = a & "PARAMETERS Parameter1 INT, Parameter2 INT; "

a = a & "SELECT f1, f2 FROM atable WHERE "
a = a & "f3 = [Parameter1] AND f4 = [Parameter2] "
a = a & ";"

Set MyQryDef = currentdb().CreateQueryDef("MyQueryName", a)

MyQryDef.Parameters("Parameter1").Value = 33
MyQryDef.Parameters("Parameter2").Value = 2

' You could now use MyQryDef with DAO recordsets

' to use it with any of OpenQuery, BrowseTo , OpenForm, OpenQuery, OpenReport, or RunDataMacro


DoCmd.SetParameter "Parameter1", 33
DoCmd.SetParameter "Parameter2", 2
DoCmd.Form YourFormName

' or

' 或者

DoCmd.SetParameter "Parameter1", 33
DoCmd.SetParameter "Parameter2", 2
DoCmd.OpenQuery MyQryDef.Name

See here: https://msdn.microsoft.com/en-us/library/office/ff194182(v=office.14).aspx

请参阅此处：https: //msdn.microsoft.com/en-us/library/office/ff194182(v=office.14).aspx

Harvey

哈维

回答by haf

Seems like a typo. You're creating the object named 'qfd', and trying to use the object named 'qdf' Set qfd =... and then qdf.Para...

好像打错字了。您正在创建名为“qfd”的对象，并尝试使用名为“qdf”的对象 Set qfd =……然后 qdf.Para……

I like to put Option Explicitin my modules to help me find these types of issues.

我喜欢放入Option Explicit我的模块来帮助我找到这些类型的问题。