Microsoft Access - Form List-box(s) selection as data requirements for reports

Question

Let me start with I am no seasoned with VB and have been wracking my brain to figure this out... I have seen solutions that I can not seem to wrap my thick skull around being as I am such a novice.

I have a database with a form. The form has 2 List Boxes "List1" and "Invoice Type". Each with unique values. I have a "Select All" and "Clear Selection" button for each Unbound Listbox, and they work.

I have a "StartDate" and "EndDate" that are working for all 3 report buttons.I have accomplished this by using them as the criteria in the queries themselves.

My reports are "CompleteTransRPT", "NoPaymentRPT" and "PaidRPT". Each report has a button and all three currently work with the exception of passing through the requirements of what is selected in the Listboxes.

My current codes consists of:

Private Sub Detail_Click()

End Sub

Private Sub BTNRunReport_Click()
On Error GoTo BTNRunReport_Click_Err

    DoCmd.OpenReport "CompleteTransRPT", acViewReport, "", "", acNormal


BTNRunReport_Click_Exit:
    Exit Sub

BTNRunReport_Click_Err:
    MsgBox Error$
    Resume BTNRunReport_Click_Exit

End Sub

Private Sub BTNPaidInvoices_Click()
On Error GoTo BTNPaidInvoices_Click_Err 

    DoCmd.OpenReport "PaidRPT", acViewReport, "", "", acNormal


BTNRunReport_Click_Exit:
    Exit Sub

BTNRunReport_Click_Err:
    MsgBox Error$
    Resume BTNPaidInvoices_Click_Exit

End Sub

Private Sub DeSelectAllTeams_Click()

    Dim varItm As Variant

    With TeamName

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With
End Sub

Private Sub SelectAllTeams_Click()

Dim i As Integer

For i = 0 To Forms!InvoiceReporting!TeamName.ListCount - 1
Forms!InvoiceReporting!TeamName.Selected(i) = True
Next i

End Sub 

Private Sub SelectAllInvoices_Click()

Dim i As Integer

For i = 0 To Forms!InvoiceReporting!InvoiceType.ListCount - 1
Forms!InvoiceReporting!InvoiceType.Selected(i) = True
Next i

End Sub

Private Sub DeSelectAllInvoices_Click()

    Dim varItm As Variant

    With InvoiceType

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With
End Sub

How do I use the selected fields in the two listboxes as requirements for what is shown in the reports? Edit: As requested here is a report Query

SELECT Almost.[Invoice #], Almost.[Invoice Date], Almost.[Invoice Amount], 
    Almost.Payment, Almost.Expression AS Due, Almost.[Invoice Type], 
    Almost.[Invoice Comments], Almost.[Team Name]
FROM Almost
WHERE (((Almost.[Invoice Date]) Between [Forms]![INVOICEREPORTING]![txtBeginDate] 
                                    And [Forms]![INVOICEREPORTING]![txtEndDate]));

Ended up with:

Option Compare Database

Private Sub Detail_Click()

End Sub



Private Sub BTNRunReport_Click()
On Error GoTo BTNRunReport_Click_Err
    'DoCmd.OpenReport "CompleteTransRPT", acViewReport, , strFilter
    Dim strFilter As String
    Dim lSelCnt As Long
    strFilter = ""

    strWhere = GetValues(Me.TeamName, "Team Name", lSelCnt)
    If lSelCnt >= 1 Then strFilter = strFilter & "(" & strWhere & ")"

    lSelCnt = 0
    strWhere = GetValues(Me.InvoiceType, "Invoice Type", lSelCnt)
    If lSelCnt >= 1 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND (" & strWhere & ")"
        Else
            strFilter = strFilter & " (" & strWhere & ")"
        End If
    End If


    Debug.Print strFilter

    DoCmd.OpenReport "CompleteTransRPT", acViewReport, , strFilter

BTNRunReport_Click_Exit:
    Exit Sub

BTNRunReport_Click_Err:
    MsgBox Error$
    Resume BTNRunReport_Click_Exit

End Sub

Private Sub BTNPaidInvoices_Click()
On Error GoTo BTNPaidInvoices_Click_Err
 'DoCmd.OpenReport "PaidRPT", acViewReport, , strFilter
    Dim strFilter As String
    Dim lSelCnt As Long
    strFilter = ""

    strWhere = GetValues(Me.TeamName, "Team Name", lSelCnt)
    If lSelCnt >= 1 Then strFilter = strFilter & "(" & strWhere & ")"

    lSelCnt = 0
    strWhere = GetValues(Me.InvoiceType, "Invoice Type", lSelCnt)
    If lSelCnt >= 1 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND (" & strWhere & ")"
        Else
            strFilter = strFilter & " (" & strWhere & ")"
        End If
    End If


    Debug.Print strFilter

    DoCmd.OpenReport "PaidRPT", acViewReport, , strFilter

BTNPaidInvoices_Click_Exit:
    Exit Sub

BTNPaidInvoices_Click_Err:
    MsgBox Error$
    Resume BTNPaidInvoices_Click_Exit


End Sub

Private Sub BTNUnPaidInvoices_Click()
On Error GoTo BTNUnPaidInvoices_Click_Err

    'DoCmd.OpenReport "CompleteTransRPT", acViewReport, , strFilter
    Dim strFilter As String
    Dim lSelCnt As Long
    strFilter = ""

    strWhere = GetValues(Me.TeamName, "Team Name", lSelCnt)
    If lSelCnt >= 1 Then strFilter = strFilter & "(" & strWhere & ")"

    lSelCnt = 0
    strWhere = GetValues(Me.InvoiceType, "Invoice Type", lSelCnt)
    If lSelCnt >= 1 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND (" & strWhere & ")"
        Else
            strFilter = strFilter & " (" & strWhere & ")"
        End If
    End If


    Debug.Print strFilter

    DoCmd.OpenReport "NoPaymentRPT", acViewReport, , strFilter

    'DoCmd.OpenReport "NoPaymentRPT", acViewReport, "", "", acNormal


BTNUnPaidInvoices_Click_Exit:
    Exit Sub

BTNUnPaidInvoices_Click_Err:
    MsgBox Error$
    Resume BTNUnPaidInvoices_Click_Exit

End Sub

Private Sub DeSelectAllTeams_Click()

    Dim varItm As Variant

    With TeamName

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With
End Sub

Private Sub SelectAllTeams_Click()

Dim i As Integer

For i = 0 To Forms!INVOICEREPORTING!TeamName.ListCount - 1
Forms!INVOICEREPORTING!TeamName.Selected(i) = True
Next i

End Sub

Private Sub SelectAllInvoices_Click()

Dim i As Integer

For i = 0 To Forms!INVOICEREPORTING!InvoiceType.ListCount - 1
Forms!INVOICEREPORTING!InvoiceType.Selected(i) = True
Next i

End Sub

Private Sub DeSelectAllInvoices_Click()

    Dim varItm As Variant

    With InvoiceType

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With
End Sub


Function GetValues(lstbox As ListBox, lstField As String, lSelectedCnt As Long) As String

    Dim varItem As Variant      'Selected items
    Dim strWhere As String      'String to use as WhereCondition
    Dim strDescrip As String    'Description of WhereCondition
    Dim lngLen As Long          'Length of string
    Dim strDelim As String      'Delimiter for this field type.

    'strDelim = """"

    'Loop through the ItemsSelected in the list box.
    With lstbox
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter from the bound column (hidden).
                strWhere = strWhere & "'" & strDelim & .ItemData(varItem) & strDelim & "',"
                lSelectedCnt = lSelectedCnt + 1
            End If
        Next
    End With

    'Remove trailing comma. Add field name, IN operator, and brackets.
    lngLen = Len(strWhere) - 1
    If lngLen > 0 Then
        GetValues = "[" & lstField & "] IN (" & Left$(strWhere, lngLen) & ")"
    End If

End Function

It works, but if anyone sees any issues please let me know. I am going to leave the before and after up in hopes that it can help others in the future... I basically used others peoples code and worked it till it worked by putting various posts together from different sites.

Microsoft Access - Form List-box(s) selection as data requirements for reports

Answers (1)

Related Questions