Using a form to enter criteria for a query

[mariaw]

Hi,

I want the user to be able to pick from a list the criteria that they can put in when prompted by a query.

At the moment, when you run the query, I have it set up so it prompts for the criteria: [Enter Section:] - so the user has to type in the section.

I want them to be able to pick the section from a drop down list.

I remember vaguely having to set up a form to do this....???


Thanks!

Maria

 

[lightray]

combo box on a form

Hi there,
I would use a combo box on a form
set the 'row source type' to table/query
and create the query in the line below

you will need to include the relationship link and the section field
you need to set format parameters as well to get just the section name showing.

Then pass the value thru to a DoCmd statement, on an OnClick event
probably a button on the form

Hope that helps

 

[mariaw]

HI

Sorry, you have lost me completely.... can you try and explain it in a different way?

Thanks

 

[lightray]

The attached image is probably more complicated than it looks, but I have done many of these. I use them to run reports, but you could easily run a query. For your purpose look at just one of the combo boxes. I have selected the top combo box in the image.

NB this image is a photoshop composite.

Do you know how to use the DoCmd statement?

Any more helpful?

 

[mariaw]

Hi Lightray

Sorry, still can't get this to work....what i need to do is when the dialog box comes up on the screen to prompt for the query criteria, there is a drop down list to choose from?

Maria

 

[Brianwarnock]

If you use a form, as per the thread heading, then you do not get a parameter prompt. The combo box is on the form , the user selects the entry then the command button to run the query or report.

Brian

 

[lightray]

Hi mariaw, my methodology is to setup the parameters that the user wants, then run the report. Which works fine for reports, but the DoCmd.OpenQuery doesn't work the same way.
As Brian has indicated, I have perhaps lead you in a wrong direction

What is the end result of the query? Are you populating a form, report?

 

[mariaw]

Hi lightray

I am populating a table with information entered on the form.

So at the moment, when I click on the "Employee" text box, it brings up a dialog box prompting to enter the criteria for the "Section". If you type in "legal" as the section, then all of the employees for the Legal section appear in the drop down list for "Employee".

So what I want the dialog box to do is have a drop down list of sections?

Thanks

Maria

 

[Michael J Ross]

Try the following as your Row Source for the combo, it should give you a list of sections that exist in your table

SELECT DISTINCT [YourTable Name].[Section]From[YourTableName]ORDER BY [YourTable Name].[Section]

Or you can create a list of sections and put them in the row source e.g. "Legal";"Admin" etc

 

[mariaw]

Using a form for criteria in a query - fao Michael J Ross

Hi Michael

Thanks - I now have a form that will show the "list" of sections.

So how do I make the query pick that form up?

I have tried =[Forms]![Section]![Section], with and without the "=" - but it doesn't work..

Maria

 

[Michael J Ross]

Hi Maria,

It should work using [Forms]![Section]![Section]; assuming Section is form name and Section is combo name.

I've attached a simple demo for you.

Hope this helps

Michael

 

[mariaw]

Hi Michael

I can't open the zipped file for some weird reason.. ARGHHH! It says unrecognised file format when I unzip it.

and the [Forms]![Section]![Section] still doesn't work.....Section is the form name and Section is the combo name.... I put the [Forms]![Section]![Section] in the criteria box, right?

what is weird as well is that when I click on the Section form, it has 57 records in it - there are 57 sections....???? But the combo box in each record shows all sections??

Maria

Maria

 

[Michael J Ross]

Hi Maria,

My file is in Access 2000, are you using an earlier version? Although reading your posts again it didnt do exactly as you required anyway, but I know what you want now.

I think you have your Section form bound to your table when you don't need to, thats why you're getting 57 records.

Is it possible for you to attach a cut down version of your project?

 

[mariaw]

Database

Hi Michael

I'm afraid even if I chop down the database, it is too big to attach....have you got an email address??

Maria

 

[Michael J Ross]

Sent you a PM

 

[AccessNewbie72]

Hello all,
I'm so glad I found this forum....whaddayaknow? I have the same question like Maria's.....I actually tried Mike Ross' suggestion, it didn't work either. I pasted the following in the Query criteria (in the Vendor Name field which I wanted):

Forms!VendorForms!Vendor Name Combo List

When I click my report (which is link to my Query)...I got the prompt

Enter Parameter Value
Forms!VendorForms!Vendor Name Combo List

but it still doesn't show the pull down menu from it.

Maria/Mike Ross perhaps you guys can enlighten me?

Thanks...and sorry to jump right in without much Introduction.....

 

[EMP]

Originally Posted by AccessNewbie72
When I click my report (which is link to my Query)...I got the prompt

Enter Parameter Value
Forms!VendorForms!Vendor Name Combo List

but it still doesn't show the pull down menu from it.

Do not use embedded spaces in the name of objects on the form. Remove the spaces in the combo box name and use Forms!VendorForms!VendorNameComboList in the criteria of the query on which the report is based.

Before running the report, make sure the VendorForms form is already open and a vendor name in the combo box has been selected.

It's better to run the report from a command button on the form. In the On Click event of the command button, you can use the OpenReport method like this:-

DoCmd.OpenReport "your report name", acViewPreview

^

 

[AccessNewbie72]

Do not use embedded spaces in the name of objects on the form. Remove the spaces in the combo box name and use Forms!VendorForms!VendorNameComboList in the criteria of the query on which the report is based.

Before running the report, make sure the VendorForms form is already open and a vendor name in the combo box has been selected.

It's better to run the report from a command button on the form. In the On Click event of the command button, you can use the OpenReport method like this:-

DoCmd.OpenReport "your report name", acViewPreview

^

Hi EMP,
I tried your suggestion and it didn't work...

I'm pretty basic with Access compare with you experts here (only been actively working with it for about a year and I've completed both basic and intermediate courses), but I know that what I'm asking for the query here is pretty simple....

Anybody?? Mike Ross? Maria?

 

[gemma-the-husky]

Maria, are you currently in a form linked to the query you are talking about.

If so, what is your combo box bound to - this will show on the control source in the data tab of the combo box

 

[Brianwarnock]

If you did exactly what EMP told you to do it would work.
Check all syntax.

Brian