User Prompt for Access 2007 Report

[BettyN]

The Quote report created in Access 2007 using a SQL 2008 query has an image on the cover page. I need to create a prompt for the user when they run the report to select which image they want on the cover page. I have placed two images in the report. One is OLEUnbound341 and the other is OLEUnbound342. I've never created a prompt in Access or in SQL. What is the best way to accomplish this?
Thanks. Betty

[HansV]

I'd use Image controls instead of unbound OLE controls (the latter have all kinds of additional features that you don't need), but that's not essential.

You can create an event procedure for the On Open event of the report:

Code: Select all

Private Sub Report_Open(Cancel As Integer)
    Dim f As Boolean
    f = MsgBox("Click Yes for Image1, No for Image2", vbQuestion + vbYesNo) = vbYes
    Me.OLEUnbound341.Visible = f
    Me.OLEUnbound342.Visible = Not f
End Sub

Change the text displayed by the message box to suit your purposes.

[BettyN]

It works beautifully!

Thank you so much.
You, HansV, have once again come to my rescue!
Betty

[BettyN]

The user has changed their minds. Now I have three images. so I need to setup something like "Select Option 1, Option 2, or Option 3." Each option will display a different image on the cover page. What is the best way to do this?

Thanks.
Betty

[HansV]

You could use a message box with Yes, No and Cancel buttons:

Code: Select all

Private Sub Report_Open(Cancel As Integer)
    Dim lngResult As Long
    lngResult = MsgBox("Click Yes for Image1, No for Image2, Cancel for Image3", vbQuestion + vbYesNoCancel)
    Me.OLEUnbound341.Visible = (lngResult = vbYes)
    Me.OLEUnbound342.Visible = (lngResult = vbNo)
    Me.OLEUnbound343.Visible = (lngResult = vbCancel)
End Sub

where OLEUnbound343 is the name of the third image control on the report.

Although this is the easiest way to expand the code you already had, it's not very elegant. Another option is to create a form, say frmChoice, with an option group grpChoice with three option buttons (one for each image, with an appropriate caption), and a command button to open the report. The Option Value property of the option buttons would be 1, 2 and 3, respectively.

The On Open event of the report would change to the following:

Code: Select all

Private Sub Report_Open(Cancel As Integer)
    Dim lngChoice As Long
    lngChoice = Forms!frmChoice!grpChoice
    Me.OLEUnbound341.Visible = (lngChoice = 1)
    Me.OLEUnbound342.Visible = (lngChoice = 2)
    Me.OLEUnbound343.Visible = (lngChoice = 3)
End Sub

[BettyN]

Thanks, Hans. I thought about the Yes, No, Cancel option but was checking for other options. I'll give it a try.
Betty