Access VB- Using combo boxes as input parameters to a report


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Access VB- Using combo boxes as input parameters to a report

  1. #1
    Grant Guest

    Access VB- Using combo boxes as input parameters to a report


    Hello VB Experts,

    For Access 2000: My objective is to create a code that when the user hit
    the command button to preview a report, the user can have the option of choosing
    the parameter on the form as the input to the report selected. Basically,
    the form has two combo boxes, a partner's name and a publication name. The
    user has three combination choices-either choose by a partner and a publication(All
    those contacts who are on this partner's list and who are receiving this
    publication) or just by partner(All those contacts who are on this partner's
    list) or just by publication(All those contacts who are receiving this publication,
    doesn't matter who partners's list are they on). After the user selects
    the choices from the combo boxes, he/she can choose the type of reports that
    they want from the option group box on the form.

    The problem is that I can't get the filter to understand that when user leaves
    either the partner or publication combo box blank, it means no filter applies-e.g.
    select all records based on this partner or select all records based on this
    publication. Right now the form works only when both the partner and publication
    combo box have a value in it. It doesn't work with null. Instead of creating
    separate forms for the partner combo box or the publication combo box and
    separate reports for each report type, is there a way/code to get the program
    to understand the null values in a those two combo boxes? Is there another
    method that I can use?

    Any help that you can give me, will be deeply appreciated?


    Private Sub Print_Click()
    On Error GoTo Err_Print_Click

    'Ignore filter when the combo box has a null value
    If Combo0 = Null Then
    DoCmd.ShowAllRecords
    End If

    If Combo2 = Null Then
    DoCmd.ShowAllRecords
    End If


    ' Open a mailing labels report with a filter

    Dim strFilter As String, strReportName As String


    ' Determine which mailing labels user wants.
    Select Case MailingType
    Case 1
    strReportName = "rptFullPartnerContact"
    Case 2
    strReportName = "rptFullPublicationContact"
    Case 3
    strReportName = "rptGTFBMailingLabels"
    Case 4
    strReportName = "rptPubSmallMailingLabels"
    Case 5
    strReportName = "rptSmallMailingLabels"
    Case 6
    strReportName = "rptPartnerContact"
    Case 7
    strReportName = "rptShortPartnerContact"
    Case 8
    strReportName = "rptPublicationContact"
    End Select

    DoCmd.OpenReport strReportName, acViewPreview, , strFilter




    Exit_Print_Click:
    Exit Sub

    Err_Print_Click:
    MsgBox Err.Description
    Resume Exit_Print_Click

    End Sub


  2. #2
    Paul Clement Guest

    Re: Access VB- Using combo boxes as input parameters to a report

    On 1 May 2000 09:09:37 -0700, "Grant" <vlee@gt.com> wrote:


    Hello VB Experts,

    For Access 2000: My objective is to create a code that when the user hit
    the command button to preview a report, the user can have the option of choosing
    the parameter on the form as the input to the report selected. Basically,
    the form has two combo boxes, a partner's name and a publication name. The
    user has three combination choices-either choose by a partner and a publication(All
    those contacts who are on this partner's list and who are receiving this
    publication) or just by partner(All those contacts who are on this partner's
    list) or just by publication(All those contacts who are receiving this publication,
    doesn't matter who partners's list are they on). After the user selects
    the choices from the combo boxes, he/she can choose the type of reports that
    they want from the option group box on the form.

    The problem is that I can't get the filter to understand that when user leaves
    either the partner or publication combo box blank, it means no filter applies-e.g.
    select all records based on this partner or select all records based on this
    publication. Right now the form works only when both the partner and publication
    combo box have a value in it. It doesn't work with null. Instead of creating
    separate forms for the partner combo box or the publication combo box and
    separate reports for each report type, is there a way/code to get the program
    to understand the null values in a those two combo boxes? Is there another
    method that I can use?

    Any help that you can give me, will be deeply appreciated?

    I don't see where you build the report filter (strFilter) in your code?

    It seems to me you can build your SQL filter string based upon whether user enters data in the Combo
    Boxes. If the user enters something in both of the Combo Boxes, include the criteria for both fields
    in your Report filter. If the user enters data in only one of the Combo Boxes, use only that field
    in the criteria of you Report filter. If the user doesn't enter anything in either of the Combo
    Boxes, omit the filter argument altogether.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center