Multiple parameters

[prothery]

I want to run a parameter query with four parameters - this is tedious for
users. Is there a way to enter them on a nice tidy form ?

 

[Douglas J. Steele]

You can replace the parameters in the query with references to controls on a
form using Forms![NameOfForm]![NameOfControl]

Note that the controls can be combo boxes rather than text boxes if you want
to limit what's selected.

The form must be open when the query runs, though: Access will not open the
form for you.

 

[Tom Wickerath]

Hi Prothery,

Yes, there is a way to create a form to assist with this need. This is
commonly referred to as QBF, which stands for Query By Form.

Here are some examples that you can download:

QBF Examples
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

http://www.accessmvp.com/TWickerath/downloads/elements.zip

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008


The first example, "customdialogbox", is the easiest example to implement.
It has minimal VBA code.

The second example, "elements", shows how one can use the .ItemsSelected
property of a list box with MultiSelect set to either simple or extended.
Note that this property does not exist for list boxes that are set for
MultiSelect = None. This is a bare-bones sample that is only intended to show
how to use the multiselect list box.

The third example, "Chap08QBF", takes things a step further. This is a
revised version of the QBF sample found in Chapter 8 of Access 2000 Power
Programming, written by F. Scott Barker. The revisions I added include the
ability to double-click a record in the subform to open just that one record
for editing. I also implemented Access MVP Allen Browne's calendar form.

The last example includes a Word document along with a sample database that
is a stripped down version of the venerable Northwind database (2003
version). The Word document makes an attempt to explain how this works. The
last three samples have in common an unbound QBF form (ie. a form with no
recordsource) which include a bound subform. The SELECT and ORDER BY portions
of a SQL (Structured Query Language) statement (ie. a query) are hard-coded
in the class module associated with the QBF form, although there's no reason
that the ORDER BY statement need be hard-coded. The WHERE clause is built
dynamically, as the user makes selections. These three clauses are then
joined together, and used to set the recordsource of the subform. That's how
it is suppost to work.



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[prothery]

Thanks Tom I am investigating your referrals and think they will help me in
several ways.

 

[prothery]

Thanks Douglas - that solved my problem straight away

You can replace the parameters in the query with references to controls on a
form using Forms![NameOfForm]![NameOfControl]

Note that the controls can be combo boxes rather than text boxes if you want
to limit what's selected.

The form must be open when the query runs, though: Access will not open the
form for you.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I want to run a parameter query with four parameters - this is tedious for
users. Is there a way to enter them on a nice tidy form ?

 

[lil2009]

Hi, I tried your examples in my own database and the null propagation isn't
working. So strWhere is And And [listbox selection]. If someone picks from
only the 3rd listbox, the first 2 are set to null.

Any idea what I am doing wrong?

 

[Tom Wickerath]

Hello -

So strWhere is And And [listbox selection].

It *appears* as if you have one of the AND statements outside of a set of
parentheses, or you are concatenating this keyword instead of using an
addition. However, I cannot see your code, so I have no way of knowing for
sure.

I only discovered your reply by chance, and it came through as a single
message without the rest of the messages in the thread. I think the reason
for this is that you waited so long to reply, that the other messages in this
thread "aged off" of the newsgroup server. I finally found the rest of the
thread by using the web interface, and searching for "lil2009".

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:
From: "lil2009" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Wednesday, August 26, 2009 6:21 PM
Subject: RE: Multiple parameters

Hi, I tried your examples in my own database and the null propagation isn't
working. So strWhere is And And [listbox selection]. If someone picks from
only the 3rd listbox, the first 2 are set to null.

Any idea what I am doing wrong? __________________________________________

"Tom Wickerath" wrote:

By: Tom Wickerath
In: microsoft.public.access
4/5/2009 1:00 PM PST
Subject: RE: Multiple parameters

 

[lil2009]

Hi, I hope I haven't waited too long for this response.

Here is my code.

strWHERE = " Where " & (varONFxn + " AND ") & (varDNFxn + " AND ") &
(varIHFxn + " AND ") & (varECFxn + " AND ") & (varCryFxn + " AND ") &
(varAreaFxn + " AND ") & (varEDTFxn)

Here are the debug.print statements.

strWhere with fxn concatenation = Where [tblDS].[ON] In ('ONChoice1') AND
AND
strWHERE without trailing AND= Where [tblDS].[ON] In ('ONChoice1') AND

I am very new to this and I currently have the options in queries that are
called when a report is opened, but I can only have a few choices with that
one so I thought I would try it this way. Your examples are very useful.

Thanks for your help!

Hello -

So strWhere is And And [listbox selection].

It *appears* as if you have one of the AND statements outside of a set of
parentheses, or you are concatenating this keyword instead of using an
addition. However, I cannot see your code, so I have no way of knowing for
sure.

I only discovered your reply by chance, and it came through as a single
message without the rest of the messages in the thread. I think the reason
for this is that you waited so long to reply, that the other messages in this
thread "aged off" of the newsgroup server. I finally found the rest of the
thread by using the web interface, and searching for "lil2009".

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:
From: "lil2009" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Wednesday, August 26, 2009 6:21 PM
Subject: RE: Multiple parameters

Hi, I tried your examples in my own database and the null propagation isn't
working. So strWhere is And And [listbox selection]. If someone picks from
only the 3rd listbox, the first 2 are set to null.

Any idea what I am doing wrong? __________________________________________

"Tom Wickerath" wrote:

By: Tom Wickerath
In: microsoft.public.access
4/5/2009 1:00 PM PST
Subject: RE: Multiple parameters

 

[lil2009]

Also, the first error message is invalid procedure call or argument.

Hello -

So strWhere is And And [listbox selection].

It *appears* as if you have one of the AND statements outside of a set of
parentheses, or you are concatenating this keyword instead of using an
addition. However, I cannot see your code, so I have no way of knowing for
sure.

I only discovered your reply by chance, and it came through as a single
message without the rest of the messages in the thread. I think the reason
for this is that you waited so long to reply, that the other messages in this
thread "aged off" of the newsgroup server. I finally found the rest of the
thread by using the web interface, and searching for "lil2009".

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:
From: "lil2009" <[email protected]>
Newsgroups: microsoft.public.access
Sent: Wednesday, August 26, 2009 6:21 PM
Subject: RE: Multiple parameters

Hi, I tried your examples in my own database and the null propagation isn't
working. So strWhere is And And [listbox selection]. If someone picks from
only the 3rd listbox, the first 2 are set to null.

Any idea what I am doing wrong? __________________________________________

"Tom Wickerath" wrote:

By: Tom Wickerath
In: microsoft.public.access
4/5/2009 1:00 PM PST
Subject: RE: Multiple parameters

 

[Tom Wickerath]

At this point, I think I can provide more effective help if I have a copy of
your database to work on. If you can send me a compacted and preferably
zipped copy of your database, I will take a look at it. Remove any sensitive
data before sending.

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of this page:

http://www.access.qbuilt.com/html/expert_contributors.html#TomW

Scroll down past the two pictures, to the bottom of the page, where you
should see a clickable link. Please do not post your e-mail address (or mine)
to a newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[lil2009]

I was able to get it to work.

Thanks!

At this point, I think I can provide more effective help if I have a copy of
your database to work on. If you can send me a compacted and preferably
zipped copy of your database, I will take a look at it. Remove any sensitive
data before sending.

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of this page:

http://www.access.qbuilt.com/html/expert_contributors.html#TomW

Scroll down past the two pictures, to the bottom of the page, where you
should see a clickable link. Please do not post your e-mail address (or mine)
to a newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi, I hope I haven't waited too long for this response.

Here is my code.

strWHERE = " Where " & (varONFxn + " AND ") & (varDNFxn + " AND ") &
(varIHFxn + " AND ") & (varECFxn + " AND ") & (varCryFxn + " AND ") &
(varAreaFxn + " AND ") & (varEDTFxn)

Here are the debug.print statements.

strWhere with fxn concatenation = Where [tblDS].[ON] In ('ONChoice1') AND
AND
strWHERE without trailing AND= Where [tblDS].[ON] In ('ONChoice1') AND

I am very new to this and I currently have the options in queries that are
called when a report is opened, but I can only have a few choices with that
one so I thought I would try it this way. Your examples are very useful.

Thanks for your help!