Parameter query

[Darhl Thomason]

I'm creating a parameter query in my database, but I'd like the users to be
able to choose the parameter (like from a dropdown list) instead of having
to know the exact spelling of the parameter. Is this possible? The
selections for the parameter are in a separate table.

Thanks!

Darhl

 

[Nikos Yannacopoulos]

Darhl,

Suppose you make a form called frmQueryParameters, and put a combo box
on it to read the values from the table so the user can choose from,
named Combo1. The criterion in your query should be:

[Forms]![frmQueryParameters]![Combo1]

HTH,
Nikos

 

[Darhl Thomason]

Hi Nikos,

Thanks for the idea, it seems like it should work, but it's not. I did as
you suggested, but the dialog box that comes up asking for the parameter
merely says "[Forms]![frmQueryParameters]![Combo1]" and still just gives me
a text box for me to type in my selection, then the query works. I'm not
getting the dropdown box in there like I want.

Any other ideas?

Darhl

Darhl,

Suppose you make a form called frmQueryParameters, and put a combo box on
it to read the values from the table so the user can choose from, named
Combo1. The criterion in your query should be:

[Forms]![frmQueryParameters]![Combo1]

HTH,
Nikos

I'm creating a parameter query in my database, but I'd like the users to
be
able to choose the parameter (like from a dropdown list) instead of
having
to know the exact spelling of the parameter. Is this possible? The
selections for the parameter are in a separate table.

Thanks!

Darhl

 

[Van T. Dinh]

You have to open the Form *first*, select a value in the ComboBox and then
run the Query.

The Query won't open the Form for you.

 

[Darhl Thomason]

OK, I understand, however it still doesn't work. I select the value in the
combo box, then run the other form that pulls the query...but doesn't return
any values...

d

You have to open the Form *first*, select a value in the ComboBox and then
run the Query.

The Query won't open the Form for you.

--
HTH
Van T. Dinh
MVP (Access)

Hi Nikos,

Thanks for the idea, it seems like it should work, but it's not. I did
as
you suggested, but the dialog box that comes up asking for the parameter
merely says "[Forms]![frmQueryParameters]![Combo1]" and still just gives me
a text box for me to type in my selection, then the query works. I'm not
getting the dropdown box in there like I want.

Any other ideas?

Darhl

 

[Van T. Dinh]

Does the BoundColumn of the ComboBox corresponds to the value you are
looking for?

Note that what displays in the ComboBox may not be the value of the ComboBox
(that is passed to the Query). The Value of the ComboBox (i.e. the value of
the BoundColumn of the selected row) is passeded to the Query, not what's
displayed.

 

[Nikos Yannacopoulos]

Also, you have substituted my hypothetical form and combo names with the
actual ones, right?

 

[Darhl Thomason]

Yep, sure have...not sure why it's not working.

d

 

[Nikos Yannacopoulos]

If you want, you are welcome to compact (and zip if big) your database
and mail it to me to have a look.

 

[Darhl Thomason]

Great question, I'll check that out this morning.

Thanks!

Darhl

 

[Darhl Thomason]

Thanks Nikos, it's on the way.

d

If you want, you are welcome to compact (and zip if big) your database and
mail it to me to have a look.

 

[Van T. Dinh]

I leave this to Nikos aince I am sure he works it out in no time when he
gets a copy of your database.

Van T. Dinh
MVP (Access)

 

[Darhl Thomason]

Thanks for your help Van! It's appreciated.

d

I leave this to Nikos aince I am sure he works it out in no time when he
gets a copy of your database.

Van T. Dinh
MVP (Access)

 

[Darhl Thomason]

You know what? I just reread your post, and it clued me in to what I was
doing wrong. I had the bound column the 1st column which was the owner ID,
not the owner name. When I changed the bound column to 2 (the owner name)
then the query/report started working!

Thanks Van! Your suggestion was perfect.

Nikos, no need to tear through my database...unless you want to anyway and
can see some way to improve it.

Thanks guys! I've got more questions as I learn Access.

Darhl

 

[Nikos Yannacopoulos]

Darhl,

Glad you worked it out, it's much better this way, for your skills as
well as your confidence!

By the way, since you have - correctly - joined the two tables on the
key field, you could just add it to the query and filter on that one,
leaving your combo as it was originally (bound column = 1). See change
in returned database.

Another approach you could take is to put the combo on the same form
(header section) instead of using a separate one, make the form's
recordsource unfiltered (so it returns all records when opened), and use
the combo's On Change event to apply a filter whenever changed,
dynamically, without having to move back and forth between forms. See
the new form (and unfiltered query) I added in the returned database.

HTH,
Nikos

 

[Darhl Thomason]

Wow Nikos,

That's cool! I really like putting the drop down into the header of the
form. I think I can use something similar to get rid of all my other forms,
since they're all the same anyway, just based on different queries.

What I'd like to be able to do is remove the filter. I've been playing with
a command button, but can't get that figured out either.

Thanks again for all your help!!!

Darhl

 

[Nikos Yannacopoulos]

Darhl,

Piece of cake. Put a command button in the form header next to the combo
(caption "Clear" or "Reset"?) with two lines of code in its Click event:

Me.cboOwner = Null
Me.FilterOn = False

The first line resets the filter combo (cboOwner in my example) and the
second removes the filter.

HTH,
Nikos

 

[Nikos Yannacopoulos]

Dahrl,

Just for the fun of it:

Add this just before the End Sub in the cboOwner_Change sub:

Me.Command55.Enabled = (Me.cboOwner <> "")

and this again at the end of the Command55_Click sub:

Me.Command55.Enabled = False

where Command55 is the new command button in my example, change to the
actual button name. Then, in the form's design, change the command
button's Enabled property (tab Data) to No. See what happens.

Nikos

 

[Darhl Thomason]

Very cool! It disables the button when there aren't any filters
applied...which I'm relatively sure you knew ;-)

Thanks tons!

d

 

[Nikos Yannacopoulos]

Welcome!

Very cool! It disables the button when there aren't any filters
applied...which I'm relatively sure you knew ;-)

Thanks tons!

d