Open Report based on selection from a pull-down menu 1

[caykamanj1966]

All, I am building a database to help with my Business. I couldn't find any software that would work, based on the requirements I needed, so I decided to build my own in Access.

With that being said, here is something I want to do.

I have a Query (qry_Expenses) that will be tied to a report, called: rpt_ExpRpt

I have a field that is in this Query called: companyName. In this field there is a list companies.

I will have a button on a form, that when clicked I want it to give me the choice to pick a company (i.e Comcast) and based on that choice, it will open the report (rpt_ExpRpt), but before the report is opened, it will automatically change the "Criteria" property to "Comcast" and when the report is opened it will filter and show only the Comcast records.

Also, when that is done, I also need to change the label for the Type field: Caption property, to whatever report will be displayed. So for instance for this Comcast report, I want the Caption property (type field) to say: Comcast expenses for year, and if it would be a report that I want all records for Home Depot, then it would say: Home Depot expenses for year. I want that to also change on this report that is to be displayed.

Can someone help me with this?

It will surely be appreciated and thank you all in advance.

 

[dhookom]

I would not place any filter in the actual query. Use the WHERE clause in the DoCmd.OpenReport method to apply a filter.

Assuming a form with a combo box [cboCompany] to select a company and a button to open the form, the code might be something like:

Dim strWhere As String
strWhere = "[CompanyName] = """ & Me.cboCompany & """"
DoCmd.OpenReport "rpt_ExpRpt", acViewPreview, , strWhere

The company name should be in the report's record source so your "label" can be changed to a text box with a control source like:

=[CompanyName] & " Expenses for the year"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

Thank you for your response, but this is not working.

This is what I have:

I have the form (frm_expenses)

On that form is a combo box, and the Name is: Combo65. This has all the companies as values.

In the Row Source of the combo box is this: SELECT [qry_valueCompanyName].[tbl_valueCompanyName] FROM [qry_valueCompanyName] ORDER BY [tbl_valueCompanyName];

In the Row Source Type of the combo box is: Table/Query

In the After Update property of the combo box is the code you provided (slightly modified):
Dim strWhere As String
strWhere = "[companyName] = """ & Me.Combo65 & """"
DoCmd.OpenReport "rpt_ExpRep", acViewPreview, , strWhere

Now onto the report:

In the design view of the report (rpt_ExpRep), I have an unbound field, with this in the control source property: =[companyName] & " Expenses for the year"

When I open the form with the combo box and I choose, say for instance "Home Depot", it doesn't change the title of the unbound field to" Home Depot Expenses for the year, but it just has "Expenses for the year.

Also there are no records for Home Depot, so it should have a result of no records on the report, but it comes back with all the records. It is not filtering.

What am I doing wrong?

 

[dhookom]

If there are no records, the value "Home Depot" should not appear in the drop down unless running an empty report is a requirement. If you need to run the report, change the text box (must be in the report header or footer to:
=Forms!frm_expenses!Combo65

BTW: you should never be satisfied with a control name of Combo65 when it is significant to your process.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

The combo box was created from the wizard using this: SELECT [qry_valueCompanyName].[tbl_valueCompanyName] FROM [qry_valueCompanyName] ORDER BY [tbl_valueCompanyName];

So I do want all the values to be a choice, when I choose a company, no matter if there are records or no records. Those values are coming from the query and it is about 25 values of different companies.

So I am choosing a company from the combo box (i.e. Verizon), so when I select that from the values, I want the report to come up with only Verizon records and I also want the Label that is in the header to automatically change to: Verizon expenses for year ....I have another field that automatically updates with the previous year and you helped me figure that out also

Not sure where I was to place this, that you gave me: =Forms!frm_expenses!Combo65, but I put it on the Name property of the Label that is in the header, but nothing happened, so that is probably not on the correct property.

So if I choose a company from the combo box and there are no records, then I am ok to see a blank report.

If there are records for the company I choose, then I should only see the filtered records of my choice.

Does that make sense?

Oh and yes I will change the control name of the combo box, and thanks

 

[dhookom]

Sorry, I should have added the additional text in the control source of the text box which is in the report header section:

=Forms!frm_expenses!Combo65 & " Expenses for the year"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

That worked perfectly, but now I want to add the previous year to that string.

I tried this, but it is not working:

=Forms!frm_expenses!Combo65 & " Expenses for the year" & " Year(Now())-1"

What am I missing here?

 

[dhookom]

Try:
=Forms!frm_expenses!Combo65 & " Expenses for the year " & Year(Now())-1

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

That worked my friend and I really appreciate all of your help with this!

Have a great weekend!

Do you watch football? If so, who are you going for at the Super Bowl?

 

[dhookom]

I don’t have a favorite in the Super Bowl. I’m just hoping for a good game with some lead changes and both teams scoring at least 30. It was a weird season for my Vikings that ended with the usual disappointment. Can I assume you are a Broncos fan looking forward to a new coach?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

I am with you brother! I just want a good game and not a blow out!

I am originally for GA, but I have always loved he Raiders I know we sucked this year

Yes I live in Colorado and I only root for the Broncos when they are not playing my Raiders.

I also like the Falcons as well though.

My gf and I will be throwing a superbowl party at her house and I will be smoking some ribs and drummettes.

I will also make this tasty Rotini Pasta with Shrimp and Grill Salmon in it. I had it at place called The Viewhouse with my daughter and I loved it and I said to her, I will make this for our Super Bowl party

It will be fun brother

 

[dhookom]

next season

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[caykamanj1966]

Yep next season.

I did enjoy the Superbowl though!