Query design - How to design one query which can use different inputs

[sg2808]

Hello,

Scenario:
I have list of policies for different countries. What I have done so far is that I have designed a query for each country which will pull out the policies for that country. The report is bases on this query. However, this way I am creating lot of queries.

What I want to do is design a query (and therefore the report) so that I can pull out the policies for a country by simply selecting a name from a drop down.

Tables and Fields are as below:

Table[Country]
CountryID
CountryName

Table[Policy]
PolicyID
CountryID
PolicyDetails

Could you please advise if this is possible and if yes, how to achieve this?

Thanks,
SG

[TheSmileyCoder]

Yes, that is quite easy.

You should design your report as you normally would, except you do not apply a filter on the country ID. Then you pass(and apply) the filter when you open the report.

Lets imagine you have a combobox in which you have selected your country, lets call it cmb_SelectCountry. You can now choose to either use the AfterUpdate event or make a button with code like so:

Private Sub btn_PrintReport()
  If IsNull(Me.cmb_SelectCountry) then
    Msgbox "No country selected"
    Exit Sub
  End If
 
  docmd.OpenReport "rep_MyReportName",acViewPreview,,"ID_Country=" & me.cmb_SelectCountry,acDialog
End Sub

[sg2808]

Thanks SmileyCoder.

Sorry,I am almost zero in VB coding :-(.

The way I am doing things are:

Step 1 - I am building the query first using Query Design. In the Criteria field, I am using the Country name for which I want the policies.
Step 2 - After saving the query, I am using Report Wizard based on the saved query to generate the report.

So, I am not using and Combobox at the moment (just using the Criteria in the Query design).

Given my situation, could you please walk me through how do I achive my objective?

Many thanks,
SG

[Mihail]

In your query, in field CountryName, in the Criteria row write this: Country ?
Run the query, answer to question, and let me know if this help you.

Cheers

PS: Open the report too

[sg2808]

Thanks Mihail for your response. I tried what you suggested.

On hitting the run button, there is no question, it just shows a blank datasheet.

The field in the Criteria changes to [Like "Country ?"] after running the query.

Any further suggestion please.

[sg2808]

Hi Mihail,

Just to add, I simply put a "?" in the criteria field and then I see this question box popping up. Now wondering, how do I put some additional text so that it shows in the message box that's popping up?

Many thanks,
SG

[sg2808]

OK, got this too.. I inserted this as [Country ?], and now I can see it working :-)

[sg2808]

Many thanks for your help Mihail.

[sg2808]

Sorry to pepper with more questions.....

I just want to know that the solution that you have suggested above, how does it work when the table has more than 1 field?

For eg, in the table[Country], if there are more fields eg

[CountryID]
[CountryName]
[Region]
[etc..]

If I want to use the filter the information based on [Region] or any other field, will it still work?

Many thanks,

[Mihail]

Try it !

[sg2808]

:-) It works for multi fields and multi tables as well, thanks.

The problem that I face is that the entry has to be same as that in the table and if the list is long, then it is not great to have everything in the pop- up.

Is there a way that I can show the available options using a drop down so that it is easier for any user to select and generate the report they want?

Thanks,

[Mihail]

Yes it is.
See Smiley's solution.

[sg2808]

Many thanks. I can do this but I need little bit of hand holding here to implement Smiley's solution.

Can anyone walk me through the steps that I need to take?

Thanks.

[sg2808]

I tried the following as suggested by one of the gurus:

1. Create a blank form and add the Combobox/Dropdown control on it.
2. Follow the Wizard to display the names of the countries for the selection.
3. Create a new query (or modify one of the existing ones) to display all the policy data.
4. For the country criteria, enter a reference to the dropdown control on the form. It should look something like this:

Forms!FormName.ControlName

5. Create a new report (or use the same one based on the query you modified) based on the new query.
6. Add a button on the form and follow the Wizard to "open the report."

To test it, open the form and select a country from the dropdown, then click on the button.

This worked for me !