CODEWITHSUNDEEP
ms-accessvbams-access-2010
Codejoy
Codejoy

Reputation: 3816

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

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

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

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

inputStr definitely equals the value, it fails though.

The criteria line in the qry is:

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

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

Upvotes: 2

Views: 13093

Answers (3)

haf
haf

Reputation: 21

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...

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

Upvotes: 1

HarveyFrench
HarveyFrench

Reputation: 4568

in Access 2010 and 2013

This uses DAO and might be of interest

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

Harvey

Upvotes: 3

HK1
HK1

Reputation: 12210

The parameters property of an Access Query is read only.

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

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.

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.

Upvotes: 1

Related Questions