Combo Box Selection to Preview Report

[John1967]

I have a combo box that contains 3 items; I want the user to select one item
and for that selection to initiate a macro to open a report in preview mode.
I have created 3 reports and 3 macros. Sounds simple in concept but I just
can't seem to get the right code to do what I need. Would I use If, Then,
Else logic here?

Combo Box:
Main Issue
Contributor
Status

Macros:
OpenReport Main Issue
OpenReport Contributor
OpenReport Status

Thanks for any advice to this Access newbie.
John----

 

[Philip Herlihy]

I have a combo box that contains 3 items; I want the user to select one item
and for that selection to initiate a macro to open a report in preview mode.
I have created 3 reports and 3 macros. Sounds simple in concept but I just
can't seem to get the right code to do what I need. Would I use If, Then,
Else logic here?

Combo Box:
Main Issue
Contributor
Status

Macros:
OpenReport Main Issue
OpenReport Contributor
OpenReport Status

Thanks for any advice to this Access newbie.
John----

What I'd do:

Create a table to contain the list of report names, but with a numeric
(integer) ID field as the first field. Make the ID the primary key -
you'll need to enter unique values.

Create my combo box with the wizard turned ON, so that it asks you how
it should be set up. Choose the table of report names as the control
source, and store the numeric identifier but choose to hide it ("key
column") in the combo box display when asked. This'll make sense when
you run it if it doesn't now - Access wizards are brilliant.

In the combo-box properties page, click the selector at the end of the
After Update event - choose "Code Builder". I always use code modules -
allows you to include error code. See:
http://allenbrowne.com/ser-23a.html
(just the 9 numbered lines will do).

In the body of your event handler, use a SELECT ... CASE statement based
on the numeric value of the (updated) combo box to launch the report you
want (or a msgbox alert in the CASE ELSE branch).

Launch the report using:
DoCmd.OpenReport <reportname>, (see Help for other arguments).

Yup, that's essentially a macro, but this way it can be wrapped in error
handling code.

I still shudder at the recollection of the day I decided not to test for
errors in a particularly important function I was writing because
"that'll only generate an error if I've made a programming mistake". O,
the arrogance of youth... Later I'd wrapped this code in another
function, passing in a critical argument and introducing a (common)
programming mistake. Took us weeks to find it. Always use error
handling code.

HTH

Phil, London

 

[Beetle]

I agree that this would be better done in code rather than macros.
Here is another option if you only have the three report names to
deal with and you dont think there will be many more in the future.

One question would be do you want the report to open as soon as they
make a selection in the combo box? Or do you want them to make a
selection and then click a command button on the form?

If the former, then the code example below would go in the After Update
event of the combo box. If the latter then it would go in the Click event
of the command button.

The following example code assumes a combo box name of
cboSelectReport with a simple value list of three report names.



Dim strReportName As String

strReportName = Me!cboSelectReport

DoCmd.OpenReport strReportname, acViewPreview

 

[John1967]

Thanks Phil and Sean. It's working now.

 

[davidjearly]

John1967 wrote:
> I have a combo box that contains 3 items; I want the user to select one item
> and for that selection to initiate a macro to open a report in preview mode.
> I have created 3 reports and 3 macros. Sounds simple in concept but I just
> can't seem to get the right code to do what I need. Would I use If, Then,
> Else logic here?
>
> Combo Box:
> Main Issue
> Contributor
> Status
>
> Macros:
> OpenReport Main Issue
> OpenReport Contributor
> OpenReport Status
>
> Thanks for any advice to this Access newbie.
> John----

What I'd do:

Create a table to contain the list of report names, but with a numeric
(integer) ID field as the first field. Make the ID the primary key -
you'll need to enter unique values.

Create my combo box with the wizard turned ON, so that it asks you how
it should be set up. Choose the table of report names as the control
source, and store the numeric identifier but choose to hide it ("key
column") in the combo box display when asked. This'll make sense when
you run it if it doesn't now - Access wizards are brilliant.

In the combo-box properties page, click the selector at the end of the
After Update event - choose "Code Builder". I always use code modules -
allows you to include error code. See:
http://allenbrowne.com/ser-23a.html
(just the 9 numbered lines will do).

In the body of your event handler, use a SELECT ... CASE statement based
on the numeric value of the (updated) combo box to launch the report you
want (or a msgbox alert in the CASE ELSE branch).

Launch the report using:
DoCmd.OpenReport , (see Help for other arguments).

Yup, that's essentially a macro, but this way it can be wrapped in error
handling code.

I still shudder at the recollection of the day I decided not to test for
errors in a particularly important function I was writing because
"that'll only generate an error if I've made a programming mistake". O,
the arrogance of youth... Later I'd wrapped this code in another
function, passing in a critical argument and introducing a (common)
programming mistake. Took us weeks to find it. Always use error
handling code.

HTH

Phil, London

Help! Tried to follow this to do the same thing, but it's not working.

This is the code I have used, but when I select North report from the drop down menu in the form, nothing happens.

Private Sub Combo138_AfterUpdate()
 
	Select Case ID
	Case 1
		DoCmd.OpenReport ("NORTH Report")
 
	End Select
 
End Sub

To be clear, I have created a Combo Box on a form, which contains a list of 9 reports that are stored in my database. I have created a table with an ID column (autonumbered) and a report name column, with all 9 reports listed.

When I click on the Combo Box in my form I see the 9 reports listed, but clicking on them does nothing.

I would appreciate any help!