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

I know I can use the paremeter in the Criteria field but if the list is big, this is not very conducive. I would like a drop down which will show all the available choices. After selecting one of the choices, I want the report to be created based on my selection.

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

PS -I am not very familiar with VBA. If you could explain how it could be done using Query design, that will be great.

Many thanks.

Hi,

Basically, you could try the following:

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.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi: I was building a short demo for you when theDBguy replied. Essentially the same as his suggestion, just in a demo. I didn't build the report, just displayed the query - from which you can build the report.

HTH
Joe

Thanks a ton RJD and theDBguy for your kind suggestions.

@DBguy -I will be trying it soon.
@RJD -Thanks for the demo, very helpful. I created a report based on the query "qryPolicyList". On running the report, I do get the pop-up but I do not see a drop down of the country list. Is it possible to show a drop-down in the pop-up when I try to open the report?
I want the user to see what is is avaialble to select on the drop down.

Again, many thanks.

SG

You could just use the form in the demo to open the report instead of the query. Replace the button calling the query with a button calling the report.

Or if you have a report menu, use the button calling the report to simply open the form (like from the demo), not the report. From the form select the country and then use a command button to call the report.

You might also enhance the procedure to check the combobox to make sure it has a selection before calling the report.

Joe

I tried your suggestions, but I cannot see a dropdown of the country list when I am opening the report. I just get a pop-up which is looking for the country name.
I tried RJD's example, even by replacing the '!' by '.' in the criteria field.

Just to add, the Form prepared by RJD, I can see the dropdown there. I would like something similar while opening the report.

Please advise.

Thanks Joe. Will try and will let you know.

See the revised demo attached for a way to do what I was talking about. Selecting the report from the report menu opens the form, from which a selection is made and the report opened.

HTH
Joe

Ah.. got it now.... thanks Joe and theDBguy. Awesome. Thank you soooooooo much for your help.

One more question :-) as this is on a similar lines. Can I use the similar logic if I have more than one criteria?

>>Can I use the similar logic if I have more than one criteria?

Absolutely. Just put all the criteria selections on the form and include in the query criteria. One thing you might face is dealing with "All" or "No selection" in a criteria. I'll let you think about that one and get back with us if you need further assistance.

And I am sure theDBguy joins me in saying ... (IMG:style_emoticons/default/yw.gif)

Regards,
Joe

You guys made my day. Thanks a ton. Take a bow. (IMG:style_emoticons/default/hat_tip.gif)

Hi,

Joe is correct!

(IMG:style_emoticons/default/yw.gif)

Good luck with your project.

Hi Joe,

I can see this working. Just one small issue I am facing with my form (with different data and names) and if you could please advise.

Step A - I have created the report which on trying to open, asks for input data and it then displays the relevant information on the report. So this is working similar to your example.

So after this, I am adding a "Open report" button on the form and have pointed this button to the report I have created above. However, my "open report" button doesnt seem to work for any reports I have. Not sure why.

I then added a "preview" button on the form. On clicking this button, I am getting blank report. I used the same input field which I used to open the report directly (in Step A above) but it is not showing any data.

Could you please advise?

Many thanks,

SG

Hi SG: Could be more than one thing - and it would be helpful (perhaps necessary) to see your db. If you could post the db, cut down without any sensitive data - just the parts that affect the situation you describe, I will be happy to look at it. This would be much easier/faster than just guessing.

Joe

Hi Joe,

Please see the relationship diagram, I was using the following tables - Jurisdiction, Junction_Jur_Rules, RulesMap, RuleDetail, L1Owners, Junction_RuleDetail_L1Owner.

I have the query and the form in the attached DB.

Really appreciate your help. if you need any info let me know.

Thanks,
SG

Hi Joe,

I am having the same problem with a trial DB that I created. I guess I am making some mistake somewhere.
This is similar to the problem with my actual big DB. You may refer to this DB and kindly advise.

many thanks,

SG

Right... after some research....I found something:

Taking your example - "Howtocreatereport" db,

I did not create a seaparate query (qrycountryname) for the Country. As I can see, the combo box is pointing to this query. What I did was, instead of pointing to a query, I used the Row Source in the property and build a query inside it.

After creating a separate query and linking with the combo box, this is now working fine.

However, is this a standard process? I mean, creating a query inside the Row Source (and not a separate query), will this not work?

SG

Hi,

I can now create report based on the option that is selected. Could you please share how can I have the option to select "All" options?

Thanks,
SG

Hi again SG: See the revised demo attached for a way "All" can be handled. Note the change in qryPolicyList criteria for CountryName, and how qryCountryName is now a UNION query, adding "All Countries" to the list.

I typically use an external query rather than row source SQL in a combobox since I find it easier to deal with (others may have another opinion) and it is available for use elsewhere in the application. So I didn't try to diagnose the problem you were reporting with that issue.

HTH
Joe

Hi All

I use this code in the drop-down menus to limit the buttons

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

Dim stDocName As String
Dim c As Integer
Dim f As String
c = Me.Frame15.Value
Select Case c
Case 1
f = "Query1"
Case 2
f = "Query1 Query"
Case 3
f = "printQuery"
Case 4
f = "repet"
Case 5
f = "qaniti"
Case 6
f = "dgree"
Case 7
f = "code1"
Case 8
f = "amore"
Case 9
f = "noamore"

End Select

DoCmd.OpenReport f, acPreview

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox "You must choose the report ", , "AZHAR ALJABREE"
Resume Exit_Command25_Click

End Sub