Use Drop Down to Open Different Reports

[adrienne_r30]

I have a question. I have a lot of reports I have created. A lot of them are the same report but with different criteria. Right now I just have a report form, which displays all of the reports with buttons and the user chooses the desired report, clicks, and bam. In an attempt to make this look nicer and cleaner, is there a way to use a couple of drop down menus to open the reports. For example, can I group them by the main report category, have a drop down, click on a desired report and it opens from there??

Thanks in advance

 

[plog]

Yes. I have a table (sys_Reports) with 4 fields:

sys_Reports
report_ID - autonumber ID
report_Title - name to display in drop down
report_Name - actual name in Access of report
report_Criteria - criteria string to use when opening report from button

Then I create an unbound form with a dropdown that shows the report_Title, but holds the report_ID value. Beneath that is a button, user clicks the button VBA looks up the report information using the report_ID and runs a Docmd.OpenReport using the report_Name field and report_Criteria.

My table looks something like this:

sys_Reports
report_ID, report_Title, report_Name, report_Criteria
1, Employee Directory, rpt_Directory, (1=1)
2, Current Sales, rpt_Sales, Month([SalesDate])=Month(Date()) AND Year([SalesDate])=Year(Date())
3. YTD Sales, rpt_Sales, Year([SalesDate])=Year([SalesDate])

That way it lets me use less reports and carve them up in many different ways.

 

[adrienne_r30]

This is really great info. Can you show my some of your VBA though, so I can have a starting point. I am not that great creating VBA but when I have an example I know how to customize it for my own database.

 

[plog]

I don't have a copy here but its pretty simple:

Dlookup to get report date from table
Do.Cmd to open the report data obtained from the dlookup

 

[adrienne_r30]

ok, so maybe I don't have it. I know you don't have a copy infront of you but can you look at what I have and tell me if I am close.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim LReport As String
LReport = DLookup("Report_ID", "tbl_Sys_Reports", "Report_Criteria")

DoCmd.OpenReport Me.cmbReports, acViewPreview

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

It is giving me the error that '1' is not a report name, which is that the combo box is giving as the report ID. Not sure how to get the report name from the ID

 

[plog]

What's your Dlookup doing for you? LReport is serving no purpose, you fill it with information, but never do anything with it.

Also, its wrong to begin with. Look at how Dlookup works:

http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[adrienne_r30]

Got it. But now I have a somewhat relevant question, hoping you can answer it. So I used the code below,

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim LReport As String
Dim LCriteria As String
Dim LFilter As String

LReport = DLookup("Report_Name", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)
LCriteria = DLookup("Report_Criteria", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)
LFilter = DLookup("Report_Filter", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)

DoCmd.OpenReport LReport, acViewPreview, , LCriteria
LFilter

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

I added the LFilter. This isn't so much a filter as so much as what I decided to call it. In my VBA that I used to have open my reports, I had the line,

Reports!ReportName!NameOfTextBox.Visible=True

I used this to display different titles for the same report, depending on the Where condition I used. So I made a new field in my table I just created for the report drop down and inserted that above code. In my VBA I put the above code exactly where I put LFilter but I am getting the error message,

Expected Sub, Function or Property?

I am assuming I have to do more to this, just not sure what. Do you have any suggestions?

 

[plog]

I don't really understand that code or where you are putting it. Is that the exact code you are using? Or did you genericize it?

You need to reference the report you just opened--whatever is in LReport. Also you just have LFilter on a line by itself?

 

[adrienne_r30]

Gotcha. So I changed the line where I had LFilter by itself and made it

Reports!LReport!LFilter

In my VBA I had

Reports!MyReportName!Title.Visible=True

So LReport will display the report I am on and LFilter will show, Title.Visible=True. But I am still getting a syntax error.

Thanks again for all the help

 

[plog]

I really don't follow what you are saying, you need to be more explicit in what code you are using and what you are trying to do. With that said, I use this code to show custom Titles on reports:

Screen.ActiveReport.subtitle.Visible = True
Screen.ActiveReport.subtitle.Caption = str_SubTitle

All my reports have a control named "subtitle". I make it visible then I load text into it. I don't need the report name because I use whichever one is currently being shown. I hope that helps. If not, be more explicit in what you are trying to do and the code you are using.

 

[adrienne_r30]

Sorry for not being clear, let me try again.
Before I implemented the drop down box to open all reports I had separate buttons for every report. Some report buttons use the same report, with different where clauses. For every report that opens, I have different titles that become visible to display exactly what is being shown. Some of the titles are field combo boxes and some are just text boxes. Some reports also make visible 2 or 3 titles. So on my VBA code to open these reports up I have,

Reports!ReportName!Title.Visible=True
Reports!ReportName!Title2.Visible=True
etc.

So, now, I am using the drop down to open all these reports, which works so awesome and makes my form so much simpler. I made the tbl_Sys_Reports as you suggested with all the same fields that you suggested, as shown in the above code in this thread. But I added one more field named, LFilter. This field shows the titles that need to be visible, ex.,

Title_3Expired

So in my new code I have,

Screen.ActiveReport.LFilter.Visible = True

But I am still getting an error. I hope this is clear enough. My code for LFilter is,

LFilter = DLookup("Report_Filter", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)

and in the table, in the Report_Filter field I have,

Title_3Expired

Which is the name of the title I want to be visible.

 

[plog]

Screen.ActiveReport.LFilter.Visible = True

Thats how you reference something with a control named LFilter. But your control is named with whatever value LFilter holds, so you need to reference it a different way.

I'm not certain how you do that and I'm on my way out for the weekend. You need to google something like "Referencing a control in VBA using a variable".

 

[adrienne_r30]

Thanks for the help. I Googled what you suggested and found my answer. Below is the full working code:

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim LReport As String
Dim LCriteria As String
Dim LFilter As String

LReport = DLookup("Report_Name", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)
LCriteria = DLookup("Report_Criteria", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)
LFilter = DLookup("Report_Filter", "tbl_Reports_Home", "Report_ID = " & Forms!frm_Main_Open_Page!cmbReports)
DoCmd.OpenReport LReport, acViewPreview, , LCriteria
Screen.ActiveReport(LFilter).Visible = True
Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub