Opening Report From Form

[injanib]

Hi,

I have a button on my form that is supposed to open three different reports based on their criteria. All three forms are are based on the same query.



the reports are called : rptCompletionCertificate, rptAchievementCertificate, and rptDiploma

The VBA on the Click Event property of of the button (actually label) is this.

Private Sub lblPrintCertificate_Click()
Dim strWhere As String
Dim ctrl As Control
Dim strReport As String

strWhere = "[ID]=" & Me.txtRegistrationID
Set ctrl = Me.txtCertificateType

If Not IsNull(ctrl) Then
Select Case ctrl
Case "C. Certificate"
strReport = "rptCompletionCertificate"
Case "A. Certificate"
strReport = "rptAchievementCertificate"
Case "Diploma"
strReport = "rptDiploma"

End Select

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End If
End Sub

What happens is when the case is true for "A. Certificate", the right report (rptAchievementCertificate) opens filtered in report view mode.

But in the cases of "C. Certificate" and "Diploma", the right reports (rptCompletionCertificate and rptDiploma respectively) open filtered in print preview mode, but the rptAchievementCertificate also opens unfiltered in report view mode.

I can't figure out why this is happening.

Thanks in advance.

[ssanfu]

I can't see anything wrong with the code, except I question the use of "Set ctrl ..." just to get a value from a text box. And Since you created a pointer, you should destroy it by the statement "Set ctrl = Nothing".

This is how I would have written the code

Option Compare Database
Option Explicit

Private Sub lblPrintCertificate_Click()
   Dim strWhere As String
   Dim strReport As String
   Dim CertName As String

   'Dim ctrl As Control

   strWhere = "[ID]=" & Me.txtRegistrationID
   CertName = Me.txtCertificateType
   'Set ctrl = Me.txtCertificateType

   'If Not IsNull(ctrl) Then
   If Not IsNull(CertName) Then
      Select Case CertName
      Case "C. Certificate"
         strReport = "rptCompletionCertificate"
      Case "A. Certificate"
         strReport = "rptAchievementCertificate"
      Case "Diploma"
         strReport = "rptDiploma"

      End Select

      DoCmd.OpenReport strReport, acViewPreview, , strWhere
   End If
End Sub

[injanib]

Thank you for the feedback. I tried the code the way you have written it. Unfortunately, it didn't make any difference.

Any further suggestions?

[ssanfu]

Could be corruption. Delete the form, do a Compile and Repair, then close Access.

Open the mdb and recreate the form.

[injanib]

I figured it out. The label was made a hyperlink to open rptAchievementCertificate. That is why it kept opening it regardless of the case.

Thank you for you input.