MS Access Help

Nate · 4 May 2006 at 11:11

I have a report based on a dynamic query and it works but i have to type in the value. Is there a way that i could use a drop down.

For example lets say its cars and i want all the results for one type of car at the moment i click the report and it asks me which one and lets say i have to type in 'volvo'

Now or the type are stored in a table could i not use a dropdown that lists them all so i could just select one and click ok?

Could this be done on a form?

The_KiD · 4 May 2006 at 13:33

It can all you need is a table with all the different types of cars in, then make a combo box that is linked to that table.

Sorry I cant be more specific, but I am no access genius, I just know it can be done.

Pyrosoft · 4 May 2006 at 13:36

Exactly as The Kid says it is, create a table and use the wizard to create a combo box with all the items in that table.
Update the table and the combo box items update.

I use this all the time when I make databases, very handy indeed.

Nate · 4 May 2006 at 14:12

yes i know that i think the point has been slighty lost i need a form that i put information into that then runs a report that is based on a dynamic query which is where the information i put into the form is needed.

marc_howarth · 4 May 2006 at 15:40

create a new query using the wizard and add all the fields from the table that you wish to use. In the query, for example, in CAR NAME field go down to the criteria row and type in [Enter The Car Name].

Now when the query is run a pop up box will open asking to type in the name of car. To put this into a form just change the source of data to the query instead of the table

i think this is what you're after

hope it helps

marc

KevinCB · 4 May 2006 at 15:54

OK hopefully you will understand this.

Right easy things first create your form and add your combo box using the wizard. And add a button or whatever to link to the report you created.

In your query for the report select the table that you will be using for the search. In the first column select all of the records in the table e.g. TableName.*, then in the following columns add in the field row you will be using to search with [NameOfYourField] & "". You can add as many fields as you want with this method to narrow down the search.

In each of those columns with the field [NameOfYourField] & "", in the criteria row add the following:

Code:

Like IIf([Forms]![NameOfSearchForm]![NameOfComboBox] Is Null,"*",[Forms]![NameOfSearchForm]![NameOfComboBox] & "*")

The above works by searching the table for a result that is LIKE what is in the combobox, and if you don't select anything from the combobox it just uses a wildcard and brings back everything. So you can add more fields to search using the same code to narrow it down even more.

And thats pretty much it.