Execute query with parameters

[JN]

I have 5 queries that have [Start Date] and [End Date] parameters in them.
I want to make several different forms that can fire up a variety of
combinations of these queries so I can't hard code the queries' criteria
parameters to be [formsname].[controlname].

I need to have code on each form that will call the query and pass it the
parameters. How would I do that programatically?

The queries are rather long and complex so I don't want to convert them to
SQL statements and lose the ability to edit them in the builder. It is
because of the length and how often I am asked to add or change something
here and there.

 

[Allen Browne]

Perhaps this style of thing:
Set qdf = db.QueryDefs("Query1")
qdf.Paramters(0) = Forms!Form1!Text0
qdf.Paramters("SomeName") = 99
qdf.Execute, dbFailOnError

Personally, I prefer to patch the WHERE clause into the SQL statement:
Const strcStub = "INSERT INTO ... WHERE "
Const strcTail = vbCrLf & "ORDER BY SomeField;"
Dim strWhere As String
strWhere = "SomeField = 99"
db.Execute strcStub & strWhere & strcTail, dbFailOnError

And for getting the SQL statement from the query window into code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html