Save a dynamic query with fixed parameters

[jodamo5 via AccessMonster.com]

I have an unbound form that I use to choose parameters for queries. There
are four different paramaters that I can enter into the form, and when I
click a command to open the query, the query is based on what values were
selected in the form. This is working well.

But my challenge comes when I want to save the query - with those fixed
parameters.
At the moment when I click "save as" on the query, the query's parameters are
still being pulled from the values in my form. So the query results change
whenever I change the values on the form.

But what I need to happen is to save that query with those fixed parameters
in then.
Ideally I want to run this from a command button - "save query" on the form,
so it will look at what values I have selected and automatically put those
fixed values in the parameters and then pop up with a "save as" box for me to
type the name of the new query.

If the solution requires VBA that's fine, but please give me step by step as
I'm very much a copy-paste programmer!

Any help would be appreciated. Thanks!

 

[Marshall Barton]

I have an unbound form that I use to choose parameters for queries. There
are four different paramaters that I can enter into the form, and when I
click a command to open the query, the query is based on what values were
selected in the form. This is working well.

But my challenge comes when I want to save the query - with those fixed
parameters.
At the moment when I click "save as" on the query, the query's parameters are
still being pulled from the values in my form. So the query results change
whenever I change the values on the form.

But what I need to happen is to save that query with those fixed parameters
in then.
Ideally I want to run this from a command button - "save query" on the form,
so it will look at what values I have selected and automatically put those
fixed values in the parameters and then pop up with a "save as" box for me to
type the name of the new query.

If the solution requires VBA that's fine, but please give me step by step as
I'm very much a copy-paste programmer!

You need to construct the query with the parameter values in
the SQL statement. For an example, see
http://allenbrowne.com/ser-62.html

 

[jodamo5 via AccessMonster.com]

thanks for the link, but I found the instructions on that page a bit
complicated.
All of the parameters I am filtering by are text values, so that should make
it easier.

From what I read it looks like I need to do things in two steps:
1) Build an SQL query statement, using the values on the form as parameters
2) Have some code save it as a query - with the fixed values in there (so the
query is no longer looking at the form for it's parameters, but instead has
the parameters hard coded).

my sql query needs to be along the lines of,
if [department1] equals [Forms]![Filter_Departments]![parameterA] OR [Forms]!
[Filter_Departments]![paramterB] then select that record
OR
if [department2] equals [Forms]![Filter_Departments]![parameterA] OR [Forms]!
[Filter_Departments]![paramterB] then select that record

If I can have detailed instructions/code for these steps that would be great.

 

[John W. Vinson/MVP]

But what I need to happen is to save that query with those fixed parameters
in then.

Why? What will you do with this query? Will you be accumulating an
endless set of such queries - and if so how will users be able to tell
which is which?

It's not TOO hard to do what you want (I'd probably build a SQL string
in code rather than opening the paramter query), but I really have to
wonder if you're on the wrong track!

 

[Marshall Barton]

thanks for the link, but I found the instructions on that page a bit
complicated.
All of the parameters I am filtering by are text values, so that should make
it easier.

From what I read it looks like I need to do things in two steps:
1) Build an SQL query statement, using the values on the form as parameters
2) Have some code save it as a query - with the fixed values in there (so the
query is no longer looking at the form for it's parameters, but instead has
the parameters hard coded).

my sql query needs to be along the lines of,
if [department1] equals [Forms]![Filter_Departments]![parameterA] OR [Forms]!
[Filter_Departments]![paramterB] then select that record
OR
if [department2] equals [Forms]![Filter_Departments]![parameterA] OR [Forms]!
[Filter_Departments]![paramterB] then select that record

If I can have detailed instructions/code for these steps that would be great.

Const cstrSQL As String = "SELECT <field list> " _
& "FROM <table name> "
Dim strWHERE As String

If Not IsNull(Me.parameterA) Then
strWHERE = strWHERE _
& "OR department1 = """ & Me.parameterA _
& """ OR department2 = """ & Me.parameterA & """"
End If
If Not IsNull(Me.parameterB) Then
strWHERE = strWHERE _
& "OR department1 = """ & Me.parameterB _
& """ OR department2 = """ & Me.parameterB & """"
End If

If Len(strWHERE) > 0 Then
strWHERE = "WHERE " & Mid(strWHERE, 4)
End If

With CurrentDb.QueryDefs("myquery")
.SQL = strSQL & strWHERE
End With

A different approach might be to just save the parameter
values in a table. Then when you want to use the query load
the parameter values from the table and put them into
(hidden?) text boxes on the form.

 

[jodamo5 via AccessMonster.com]

Hi John,

Thanks for your comments. "Why?" is a very good question! The answer is
because I am using the queries as a mail merge source. The programme that is
running the mail merge can't handle queries that need extra parameters
entered.

So using the form I choose some parameters and then this displays a list of
contacts. I might do this a few times to test different parameters and when I
find a set that I like and want to send a mail merge to then I will click
"save" and save that as a query.

You're right that I'll end up with a long list of queries, which is why I
need a "save as" dialogue box, or similar so I can choose the name. Then I
can go into my mail merge programme and send a mail out to all of those
contacts.

So with that background, does this sound like a good way to approach it? Or
is there a better way?

Thanks

Josh

 

[jodamo5 via AccessMonster.com]

Thanks for the help Marshall. Much appreciated.

I'll put that code into the system now and will see how it goes.