Open report based on combo box selection (1 Viewer)

mpaulbattle

Registered User.
Local time
Today, 17:30
Joined
Nov 30, 2017
Messages
56
Is there a way to open the report the user selected in the combo box?

I created a form with a combo box (cboCannedReports) and listed three reports:
1. Completed Closures
2. Pending Closures
3. All Closures

I came up with something like this

Code:
DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,Me.cboCannedReports)

but that is not working. I would like to think there is probably going to be some If statements. Is this close to what I need? Looked at some Google examples but couldn't find anything.
 

bob fitz

AWF VIP
Local time
Today, 22:30
Joined
May 23, 2011
Messages
4,717
Perhaps:
Code:
DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,Me.cboCannedReports.Column(1))
 

Cronk

Registered User.
Local time
Tomorrow, 09:30
Joined
Jul 4, 2013
Messages
2,770
With 3 separate reports and if their names are in the one column of the combo, then
DoCmd.OpenReport Me.cboCannedReports,acViewNormal

If the 3 reports have exactly the same format, and the only difference is the status, then experience developers would have only one report and filter on the status.
 

mpaulbattle

Registered User.
Local time
Today, 17:30
Joined
Nov 30, 2017
Messages
56
If the 3 reports have exactly the same format, and the only difference is the status, then experience developers would have only one report and filter on the status.

Would this be added to the OnLoad event?
Is there a link that shows or instructs one how to filter on the status?
Would this be the right click > Text Filters?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:30
Joined
Sep 21, 2011
Messages
14,048
I approached that sort of problem like this

Code:
Private Sub cmdOpen_Click()
' Open report for all or a certain submitter
If Me.cboSubmitter = 0 Then ' Combo shows ALL
    DoCmd.OpenReport strReport, acViewPreview
Else
    DoCmd.OpenReport strReport, acViewPreview, , "SubmitterID = " & Me.cboSubmitter
End If
End Sub
This allows me to get All or an individual entry from the combo.

HTH

Is there a way to open the report the user selected in the combo box?

I created a form with a combo box (cboCannedReports) and listed three reports:
1. Completed Closures
2. Pending Closures
3. All Closures

I came up with something like this

Code:
DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,Me.cboCannedReports)
but that is not working. I would like to think there is probably going to be some If statements. Is this close to what I need? Looked at some Google examples but couldn't find anything.
 

Mark_

Longboard on the internet
Local time
Today, 15:30
Joined
Sep 12, 2017
Messages
2,111
Were it me, the source for the combo would either be a hard coded list OR driven by a table.

If a list, I'd use a select case to run the reports. If off of a table, the table would hold both a display name and the name of the actual report. Of the two I would advocate the later.

From a "Users" perspective, trying to guess what the programmer intended their report name to mean can be a guessing game. If you let the users enter user friendly names it becomes much easier for them.
 

mpaulbattle

Registered User.
Local time
Today, 17:30
Joined
Nov 30, 2017
Messages
56
I seen a youtube video where he added the reports to the table and had a field name for the report. Unfortunately he was speaking in a different language so i couldn't understand why he was doing it that way. I can try and mimmick and see if that helps.

At the moment I just created a Form for the reports with Buttons.
 

AnthonyGerrard

Registered User.
Local time
Today, 22:30
Joined
Jun 11, 2004
Messages
1,069
Is there a way to open the report the user selected in the combo box?

I created a form with a combo box (cboCannedReports) and listed three reports:
1. Completed Closures
2. Pending Closures
3. All Closures

I came up with something like this

Code:
DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,Me.cboCannedReports)

but that is not working. I would like to think there is probably going to be some If statements. Is this close to what I need? Looked at some Google examples but couldn't find anything.

In a very simple direct answer to your question. (ie I may do it another way, ie in teh report itself rathe rthan have 3)

Isnt it just

DoCmd.OpenReport Me.cboCannedReports,acViewNormal

ie the combo - goes in the reportname argument - just seen Cronk already says it - unless we are misunderstanding.

If you are doing it in the reports - passing the combo box as the Openargs arguement.

Then on open do a select case and change the recordsource - always found that more flexible than the where arguement. So you need an extra comma to get it into the openargs arguement.

DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,Me.cboCannedReports
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:30
Joined
Sep 21, 2011
Messages
14,048
I do that with a system I have just created.
I have an object table that holds value of "query", "form", or "report", object name with descriptive name for a combo and ability to add a where clause, but that was due to the fact I knew I would be creating a lot of reports, forms and adhoc queries. It also has a form option if more criteria is required.

I'd start off small as you have done and have individual buttons for now.?

I seen a youtube video where he added the reports to the table and had a field name for the report. Unfortunately he was speaking in a different language so i couldn't understand why he was doing it that way. I can try and mimmick and see if that helps.

At the moment I just created a Form for the reports with Buttons.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:30
Joined
Feb 19, 2002
Messages
42,976
I also use a table to control my reports and exports. That way I can assign categories and use combos or listboses to show just a selection based by category rather than all the reports including subreports. The table also includes a query name if an Excel export makes sense. Sometimes the report's query isn't what you want to use when exporting the data so I build a separate Excel version that expands the lookup values to their text strings and shows those rather than the foreign key values.
 

Cronk

Registered User.
Local time
Tomorrow, 09:30
Joined
Jul 4, 2013
Messages
2,770
mpaulbattle,

You've had lots of suggestions for generic situations.

For your specific case (and to keep it simple), I'd set the Row Source Type of your combo to a value list, the combo's values to "Completed";"Pending";"All" and then open your report

DoCmd.OpenReport "rptPracCloseCompleted",acViewNormal,,,"Status=" & Me.cboCannedReports

assuming the field in the report is called Status (Also I'd now rename your report something like just rptPrac
 

mpaulbattle

Registered User.
Local time
Today, 17:30
Joined
Nov 30, 2017
Messages
56
Sorry Cronk, I am just trying to understand all the suggestions being presented. I am not an experienced developer and a beginner at using VBA. For the most part I just use a lot of MACROS.

I try to do a lot of research on this before I start asking questions but I can't view some sites here at work.
 

Users who are viewing this thread

Top Bottom