print or preview reports based on selected value in list or combobox

[dianna]

Hi
I am still trying to get a hang of development in access 2010.

I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.

How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.

Your help will be greatly appreciated.

dianna

 

[sxschech]

You could do this in the after update event of the combo box, then you wouldn't need buttons for printing or preview as the user would be prompted.

Change cboReports to the name of your combo box containing the list of reports.

Change the WHERECONDITION to the criteria from your form. Example:
"ReportDate = Me.txtDateCriteria

Replace ReportDate with the name of the field in your query and txtDateCriteria with the name of the textbox on your form where the user chooses the date criteria. You may need to play around with quotations " ' for the WHERECONDITION depending on the format of the data.

Private Sub cboReports_AfterUpdate()
    Dim PrintChoice As Integer
    
    
    PrintChoice = MsgBox("Click Yes to Preview on Screen. " & vbCrLf & "Click No to Print.", vbYesNoCancel, "Preview or Print")
    If PrintChoice = vbCancel Then Exit Sub
        If PrintChoice = vbYes Then
            DoCmd.OpenReport Me.cboReports, acViewPreview, , WHERECONDITION
        Else
            DoCmd.OpenReport Me.cboReports, acViewNormal, , WHERECONDITION
    End If

End Sub

 

[Pat Hartman]

The following is two pictures of my Reports form. The first shows how it looks when it opens. The second shows how it looks with a report selected and the output set to PDF. I also included the entire code module so you can see the code behind the buttons.
The process is supported by a table that lists all the reports and which criteria columns are Required/Optional/Not Supported. These flags control which boxes show and whether they must be populated.

Option Compare Database
Option Explicit

Private Sub cboJob_AfterUpdate()
    Me.cboTransmittal.Requery
    Me.cboSequence.Requery
    Me.cboDrawing.Requery
End Sub

Private Sub cmdBrowse_Click()
    Me.txtPath = fChooseDirectory()
End Sub

Private Sub cmdClear_Click()
    Me.cboJob = Null
    Me.cboSequence = Null
    Me.cboDrawing = Null
    Me.cboTransmittal = Null
    Me.txtFromDT = Null
    Me.txtThruDT = Null
End Sub

Private Sub cmdExit_Click()
    Call CommonExit(Me)
End Sub

Private Sub cmdRun_Click()
    
    Dim strReport As Variant
    Dim strWhere As Variant
    Dim frm As Form
    On Error GoTo cmdRun_Click_Error
   
    'set up variables form - used so all queries have a common place for criteria
        DoCmd.OpenForm "frmVariables", acNormal
        Set frm = Forms!frmVariables
        Call ClearVariables(frm)
        frm.cboJob = Me.cboJob
        frm.cboSequence = Me.cboSequence
        frm.cboDrawing = Me.cboDrawing
        frm.cboTransmittal = Me.cboTransmittal
        frm.txtPath = Me.txtPath
        frm.txtFromDT = Me.txtFromDT
        frm.txtThruDT = Me.txtThruDT
        frm.Visible = False
        
    strReport = Me.lstReports
 
    If Me.cboJobInd = "Required" Then
        If Me.cboJob & "" = "" Then
            MsgBox "Job is required.", vbOKOnly
            Me.cboJob.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboSequenceInd = "Required" Then
        If Me.cboSequence & "" = "" Then
            MsgBox "Sequence is required.", vbOKOnly
            Me.cboSequence.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboDrawingInd = "Required" Then
        If Me.cboDrawing & "" = "" Then
            MsgBox "Drawing is required.", vbOKOnly
            Me.cboDrawing.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboTransmittalInd = "Required" Then
        If Me.cboTransmittal & "" = "" Then
            MsgBox "Transmittal is required.", vbOKOnly
            Me.cboTransmittal.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboFromDTInd = "Required" Then
        If Not IsDate(Me.txtFromDT) Then
            MsgBox "From Date is required.", vbOKOnly
            Me.txtFromDT.SetFocus
            Exit Sub
        End If
    End If
    If Me.cboThruDTInd = "Required" Then
        If Not IsDate(Me.txtThruDT) Then
            MsgBox "Thru Date is required.", vbOKOnly
            Me.txtThruDT.SetFocus
            Exit Sub
        End If
    End If

    If IsNull(strReport) Then
        MsgBox "Please select a report.", vbOKOnly
        Me.lstReports.SetFocus
        Exit Sub
    Else
        Select Case Me.fraOutputTo
            Case 1
                DoCmd.OpenReport strReport, acViewPreview
            Case 2
                DoCmd.OpenReport strReport, acViewNormal
            Case 3
                If Me.txtPath & "" = "" Then
                    MsgBox "Please select a path.", vbOKOnly
                    Me.cmdBrowse.SetFocus
                    Exit Sub
                End If
                DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, Me.txtPath & "\" & Me.cboJob.Column(1) & "_" & strReport & "_" & Format(Date, "yyyymmdd") & ".pdf"
        End Select
    End If

cmdRun_Click_Exit:
    Exit Sub

cmdRun_Click_Error:
    Select Case Err.Number
        Case 2501
            Resume cmdRun_Click_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdRun_Click of VBA Document Form_frmReports"
    End Select
End Sub

Private Sub Form_Close()
    Call CommonClose(Me)
End Sub

Private Sub fraOutputTo_AfterUpdate()
    Select Case Me.fraOutputTo
        Case 3
            Me.txtPath.Visible = True
            Me.cmdBrowse.Visible = True
        Case Else
            Me.txtPath.Visible = False
            Me.cmdBrowse.Visible = False
    End Select
End Sub


Private Sub lstReports_AfterUpdate()
    Me.Requery
    
    If Me.cboJobInd = "NotSupported" Then
        Me.cboJob.Visible = False
    Else
        Me.cboJob.Visible = True
    End If
    If Me.cboSequenceInd = "NotSupported" Then
        Me.cboSequence.Visible = False
    Else
        Me.cboSequence.Visible = True
    End If
    If Me.cboDrawingInd = "NotSupported" Then
        Me.cboDrawing.Visible = False
    Else
        Me.cboDrawing.Visible = True
    End If
    If Me.cboTransmittalInd = "NotSupported" Then
        Me.cboTransmittal.Visible = False
    Else
        Me.cboTransmittal.Visible = True
    End If
    If Me.cboFromDTInd = "NotSupported" Then
        Me.txtFromDT.Visible = False
    Else
        Me.txtFromDT.Visible = True
    End If
    If Me.cboThruDTInd = "NotSupported" Then
        Me.txtThruDT.Visible = False
    Else
        Me.txtThruDT.Visible = True
    End If
    
    
End Sub

 

[Pat Hartman]

Here's a picture of the form in design view. The form is bound to the report's table. The yellow controls are hidden. I color the background yellow so they jump out at me when I open the form in design view.

 

[dianna]

Good day
Thanks for your quick replies. I will give both a try to see which works best for me.

dianna