Parameter Query - What If You Don't Know How to Respond to the Prompt?

wilderfan · Feb 14, 2009

I have created a simple select query to list employees and their various jobs / positions.

I thought it might be useful to have the query prompt users to specify which employee was of interest (rather than show the entire list of employees and their various jobs). So I went into design view to add the criteria [Enter Employee's Name].

This parameter query works fine - IF YOU ALREADY KNOW the employee's name.

Users will inevitably spell employee names wrong or will not know the names of the employees at all in some cases.

Is it possible to have the prompt SHOW a combo box from which users can choose whatever name they wish?

pbaldy · Feb 15, 2009

Create a form with a combo box, and have the query look there instead:

Forms!FormName.ComboName

HiTechCoach · Feb 15, 2009

wilderfan said:
I have created a simple select query to list employees and their various jobs / positions.

I thought it might be useful to have the query prompt users to specify which employee was of interest (rather than show the entire list of employees and their various jobs). So I went into design view to add the criteria [Enter Employee's Name].

This parameter query works fine - IF YOU ALREADY KNOW the employee's name.

Users will inevitably spell employee names wrong or will not know the names of the employees at all in some cases.

Is it possible to have the prompt SHOW a combo box from which users can choose whatever name they wish?

This is why I never use parameter prompts in a query. To do what you want with a combo box, you will need to use a form.

You could try using this criteria:

Code:

Where [EmplNameField] Like  [Enter Employee's Name] & "*"

This will allow you to leave the name blank to get all, or the the starting few letters of the name

wilderfan · Feb 15, 2009

Hi, Paul.

I've created a form with a combo box (frmSelectEmployeeName within the attached zip file).

I'm not sure where I'm supposed to place the reference to the combo box within the query.

I assume I'm supposed to have the query open in design view, but where to place the combo box reference, I'm not sure.

-- Robert

boblarson · Feb 15, 2009

See attached

wdrspens · Feb 15, 2009

Hi,
This is similar to my problem at http://www.access-programmers.co.uk/forums/showthread.php?t=165892

I solved it by entering in the criteria line under the relevant field:

Like "*" & [Enter Criteria] & "*"

It then matched the criteria I had entered with any string part of the relevant field.

So, if the name is Browne or Janeson and you enter "ne" without the quotes both will be revealed.

If you simply click on <Enter> all the records in that field are revealed.

Does that help?

David

wilderfan · Feb 15, 2009

Hi, Bob.

I tried the revised file which you posted.

I see that the form reference now appears as a criteria under the EmployeeID column in the query.

When I click on the query, it still prompts me for a response with no guidance as to what the possible entries might be.

I must be missing something.

wilderfan · Feb 15, 2009

David:

Thanks for the suggestion.

It helps.

But I'm still wondering if there's a way to see the possible entries in something akin to a combo box without leaving the query module.

I know I could go into forms, look at the combo box there and then go back to the query. But that's incovenient and not user-friendly.

Guess I'll wait to see if Bob or Paul have more to say.

Thanks again.

-- Robert

pbaldy · Feb 15, 2009

To my knowledge, you can't have a combo box without a form. Based on the description of your problem in post 7, you didn't have the form open at the time the query ran. Generally, you would open the form to allow the user to input their criteria, then have a button on that form that opened the form or report based on that query.

wilderfan · Feb 15, 2009

Thanks, Paul. Thanks, Bob.

Now I understand.

I didn't realize I had to be within the form module first.

Bob used an AfterUpdate event to avoid the need for a command button to run the query. I may toy around with deleting the AfterUpdate event and replacing it with an explicit Command Button. I haven't quite decided yet.

Thanks again to both of you.

pbaldy · Feb 15, 2009

It can work either way, so it's up to you. I use the after update event on password forms and such. I tend to use buttons on criteria forms to give the user a chance to change their minds. I also tend to use the same criteria form for various different reports, many of which have different criteria needs (1 date, 2 dates, drivers, accounts, whatever). Since the last required criteria changes, it's simpler to use a button.

boblarson · Feb 15, 2009

There are probably hundreds of various combinations of methods you can use to do something in Access. So, we have the ability to post various methods that we each use and you can use whatever works for your situation. I know that I use various methods, depending on what I need to do. I don't count it as a "use this all of the time" things because there are reasons to do it other ways as well.

So, good luck with your stuff and, however you do it, you have learned a few new ways that you can use.

wilderfan · Feb 16, 2009

Bob & Paul -

Many thanks. Over the last year, I have definitely learned a lot from you two.

No doubt, I will have further questions in the coming months.

Much appreciated,

Robert

Parameter Query - What If You Don't Know How to Respond to the Prompt?

wilderfan

Registered User.

pbaldy

Wino Moderator

HiTechCoach

Well-known member

wilderfan

Registered User.

Attachments

boblarson

Smeghead

Attachments

wdrspens

Registered User.

wilderfan

Registered User.

wilderfan

Registered User.

pbaldy

Wino Moderator

wilderfan

Registered User.

pbaldy

Wino Moderator

boblarson

Smeghead

wilderfan

Registered User.

Similar threads

Users who are viewing this thread