Show/Hide images on report based on combobox selection?

**82280zx** · 09-11-2022, 07:43 AM

So I have two forms, one is for printing off single certificates (I have this one working) and another form with a query subform that prints off multiple certificates... the problem I am having is getting the signatures to show/hide based on which signature is selected on each report. I've tried this without any success...

Code:

On Error GoTo errHandler

If Me.SelectSignatureToUse.Column(1) = "Dan" Then
    Me.imgDanSig.Visible = True
    Me.ImgGailSig.Visible = False
Else
    Me.ImgGailSig.Visible = True
    Me.imgDanSig.Visible = False
End If

Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description

As well as Me.SelectSignatureToUse.Value

I put these in On Report Open. Another one I tried earlier was

Code:

If DLookup("[SelectSignatureToUse]", "[QRY_FilterByCompany]", "[CertificateNumber]=" & [Forms]![FRM_FilterByCompany]![FRM_FilterByCompanySubForm]![txtCertificateNumber]) = "Dan" Then
    Me.imgDanSig.Visible = True
    Me.ImgGailSig.Visible = False
Else
    Me.ImgGailSig.Visible = True
    Me.imgDanSig.Visible = False
End If

This one works on my other report but is built different... I've eyeballed it and it should work? I don't see any misspelled stuff... or forms. Any help would be appreciated!

**Micron** · 09-11-2022, 09:17 AM

Probably cause is using the wrong column reference for the combo. Are there 2 or more columns, and is the second one from the left the one that contains "Dan"? Combo column reference is 0 based.

Please enclose more than a few lines of code in code tags (use # button on posting toolbar) to maintain code indentation and improve readability.

**82280zx** · 09-11-2022, 09:44 AM

It's a combobox that I just added two names to "Dan" and "Gail" there should only be 1 column it it that I'm aware of.

**Micron** · 09-11-2022, 09:48 AM

Then you have the answer in my first post. If interested, you can also accomplish this as (using my own test form)

Code:

Private Sub Combo2_AfterUpdate()

Me.lblDan.Visible = False
Me.lblMary.Visible = False
If Me.Combo2.Column(0) = "Dan" Then Me.lblDan.Visible = Not Me.lblMary.Visible
If Me.Combo2.Column(0) = "Mary" Then Me.lblMary.Visible = Not Me.lblDan.Visible

End Sub

lbl controls would be your control that contains the signature; combo2 is the combo with the names. However, not going to be practical if you have more than 2 to deal with.

**82280zx** · 09-11-2022, 09:56 AM

Originally Posted by Micron

Then you have the answer in my first post. If interested, you can also accomplish this as (using my own test form)

Code:

Private Sub Combo2_AfterUpdate()

Me.lblDan.Visible = False
Me.lblMary.Visible = False
If Me.Combo2.Column(0) = "Dan" Then Me.lblDan.Visible = Not Me.lblMary.Visible
If Me.Combo2.Column(0) = "Mary" Then Me.lblMary.Visible = Not Me.lblDan.Visible

End Sub

lbl controls would be your control that contains the signature; combo2 is the combo with the names. However, not going to be practical if you have more than 2 to deal with.

It seems to be different on a Report, I also can't use the on update with a combo box, I'm pulling up a query that has the data already put in. I did try your code though on my report no luck. Tried the following

Code:

If Me.SelectSignatureToUse.Column(0) = "Dan" Then
    Me.imgDanSig.Visible = True
    Me.ImgGailSig.Visible = False
Else
    Me.ImgGailSig.Visible = True
    Me.imgDanSig.Visible = False
End If

The code isn't reading the value that's in the SelectSignatureToUse box. The word Dan is in there... but it's triggering the else statement.

**Micron** · 09-11-2022, 10:07 AM

Your first post was form code, yes? That's what I based my code on. If you need to show/hide in a report but don't know how then you could either refer back to the form combo value from report load code, or you could pass the combo value as the OpenArg property in the line that opens the report.

By not including at least the beginning of your event and what it's contained in (form/report), we have no idea what you're using to run the code or even where it resides. I'm just not seeing how referencing a form combo helps you with the report. Or is this combo on the report? That's what I mean - not enough info to clue in to what's what. At least I guess we got the column number issue solved.

**82280zx** · 09-11-2022, 10:09 AM

Sorry my bad I'm putting it inside of Report on Open

Also if it helps the combobox I am using has a row source type that is a value list? Row Source is "Dan";"Gail" Control Source is SelectSignatureToUse that data should be pulled in on the query, I put a test box on my reports and the values are coming in right... just not able to trigger my signature if statement?

Code:

Private Sub Report_Open(Cancel As Integer)

On Error GoTo errHandler

If Me.SelectSignatureToUse.Column(0) = "Dan" Then
    Me.imgDanSig.Visible = True
    Me.ImgGailSig.Visible = False
Else
    Me.ImgGailSig.Visible = True
    Me.imgDanSig.Visible = False
End If

Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description

End Sub

**Micron** · 09-11-2022, 10:24 AM

Better, but incomplete.

Or is this combo on the report?

**82280zx** · 09-11-2022, 10:31 AM

Originally Posted by Micron

Better, but incomplete.

This combo is on the report and showing the correct names when I run print previews. It pulls the data from a query called QRY_FilterByCompany and the field name in the query is SelectSignatureToUse.

**Micron** · 09-11-2022, 10:43 AM

Sorry, I missed that from before and am guilty of skimming. I would advise doing this from the form you use to open the report. Interactive controls are really not for reports, but I admit to using them (but I think it was only for forum testing). The issue I have with reports is that some events don't run in particular views (print preview vs report view, for example). Also, some events are not available for certain controls as you've noted - AfterUpdate is one of them. I'm not even sure that once a report is opened and loaded that you can change the visible property of controls (conditional formatting doesn't count).

So you are opening this report from a form thus you can affect the visibility of the report signature based on having the combo on the form?

EDIT - interesting; in my version a combo in a report header has no dropdown list either, so there's that to consider. If your version allows it, any user who has updated may not be able to use it.

**82280zx** · 09-11-2022, 10:50 AM

Originally Posted by Micron

Sorry, I missed that from before and am guilty of skimming. I would advise doing this from the form you use to open the report. Interactive controls are really not for reports, but I admit to using them (but I think it was only for forum testing). The issue I have with reports is that some events don't run in particular views (print preview vs report view, for example). Also, some events are not available for certain controls as you've noted - AfterUpdate is one of them. I'm not even sure that once a report is opened and loaded that you can change the visible property of controls (conditional formatting doesn't count).

So you are opening this report from a form thus you can affect the visibility of the report signature based on having the combo on the form?

Yes this report is opened from another form (but is also a query) so it generates multiple certificates based on the query data. I was hoping to check each record for which signature was used and display that signature on each report generated, but this is where my knowledge limit hits the wall and I've been struggling.

Code:

Private Sub cmdEmail_Click()

'Set a Reference to the Microsoft Outlook ?X.X Object Library
Dim strReportName As String
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.NameSpace
Dim strTO As String
Dim strMessageBody As String
Dim strSubject As String
Dim olMail2 As Object
Dim strSQL As String
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset2

Set db = CurrentDb()
     
strReportName = "Certificates"
DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, CurrentProject.Path & _
               "\" & strReportName & ".pdf", False
   
Set oLook = CreateObject("Outlook.Application")
Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
     
'*********************** USER DEFINED SECTION ************************
strTO = ""
strMessageBody = ""
strSubject = ""
'*********************************************************************
     
With oMail
 .To = strTO
 .Body = strMessageBody
 .Subject = strSubject
 .Attachments.Add CurrentProject.Path & "\" & strReportName & ".pdf"
 
 'add attachments
  'strSQL = "SELECT YellowCopy FROM QRY_FilterByCompany WHERE CertificateNumber = " & Nz(([FRM_FilterByCompany].[Form].[FRM_FilterByCompanySubForm].[Form]![txtCertificateNumber]), 0)
  'FRM_FilterByCompany.FRM_FilterByCompanSubForm.txtDateEmailed = Now()
  'Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
  'Set rs2 = rs!YellowCopy.Value
  'With rs2
      'Do While Not .EOF
          'strFilename = CurrentProject.Path & "\" & .Fields("FileName")
          'If Dir(strFilename) <> "" Then Kill strFilename
          '.Fields("FileData").SaveToFile CurrentProject.Path
          'oMail.Attachments.Add strFilename
          'If Dir(strFilename) <> "" Then Kill strFilename
          '.MoveNext
      'Loop
   'End With
   'rs2.Close
   'rs.Close
   .Display
 
     
End With
     
Set oMail = Nothing
Set oLook = Nothing



End Sub

**Welshgasman** · 09-11-2022, 11:00 AM

I do not think control values are set before the Form or Report Open event.?
Try the load event.

**Micron** · 09-11-2022, 11:10 AM

Assuming by that you mean one certificate per report (a report with only one record). If not, it might be best if you just uploaded a copy of your db. See how to attach files button at the top of this page. I can make a control invisible or not with this if you want to try it first:
form button code

Code:

Private Sub Command6_Click()
DoCmd.OpenReport "EmployeeInfo", acViewPreview, , , , Me.Combo2

End Sub

report load

Code:

Private Sub Report_Load()
If Not Me.OpenArgs = "" Then
     Me.[First Name].Visible = False
     Me.[Last Name].Visible = False
     If Me.OpenArgs = "Dan" Then Me.[First Name].Visible = Not Me.[Last Name].Visible
     If Me.OpenArgs = "Mary" Then Me.[Last Name].Visible = Not Me.[First Name].Visible
End If

End Sub

That is in a db I play with that I didn't create, so don't follow those bad habits (combobox name stinks; fields have spaces in them, etc.)

Please consider not using reply with quote button for new posts so that everything from the prior post isn't repeated.

**82280zx** · 09-11-2022, 11:45 AM

Load Event didn't work ether, I'll upload a copy of what I have, It's just a stripped down version with test data in it but its what I've managed to build so far. Thank you!

https://synology.aviushub.com/sharing/1lK2ItgrE

**Welshgasman** · 09-11-2022, 11:54 AM

127MB zipped?

Are you taking the mickey?

Have you tried walking through the code line by line with F8 and breakpoints?

Show/Hide images on report based on combobox selection?

Thread Tools

Show/Hide images on report based on combobox selection?

Similar Threads

Union selection in combobox, autofill based on selection

emailing a report based on a combobox selection

Combobox-dependent show/hide controls

Hide/Show Columns based on DropDown Selection in Form

Filter subForm comboBox based on main form combobox selection - With a TWIST

Posting Permissions