Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing Multiple Choices (a2k)

    This is the short version of what I'm attempting to do, print a report based on the following selection criteria.

    I want to be able to select multiple CN's and have them available for a report. I realize that I should somehow be using the strWhereCN as part of my print routine, but none of my sordid attempts have worked.

    pickCN is an unbound text box using the following as RowSource:

    SELECT DISTINCT [qry 01 CN].SRT_CN FROM [qry 01 CN];

    The user can select one or more CN's

    '================================================= ============================================
    Private Sub butSelect2_Click()
    Dim varItem As Variant
    Dim strWhereCN As String
    For Each varItem In Me.pickCN.ItemsSelected
    strWhereCN = strWhereCN & ", " & Me.pickCN.ItemData(varItem)
    Next varItem
    If Not strWhereCN = "" Then
    strWhereCN = Mid(strWhereCN, 3)
    strWhereCN = "AND SRT_CN In (" & strWhereCN & ")"
    End If

    '================================================= ============================================
    'Note: the original code goes here which is why there is extra code showing in the combined version below
    '================================================= ============================================
    ' This is where all the Strings are combined
    '================================================= ============================================
    strWhere = strWhereFunction & strWhereCN & strWhereCustomer & strWhereProduct & strWhereService & strWherePONo & strWhereInvNo & strWhereInvDate
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    If DCount("*", "qry 01 CN", strWhere) = 0 Then
    MsgBox ("Your combination of choices couldn't find any records." & vbNewLine & " " & vbNewLine & " Please click OK then Red Button"), vbInformation, " No Records Found !!"
    Else
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End If
    If strWhere = "" Then
    Me.sbfFiltered.Form.RecordSource = "qry 01 CN"
    Else
    Me.sbfFiltered.Form.RecordSource = "SELECT * FROM [qry 01 CN] WHERE " & strWhere
    End If
    Me.sbfFiltered.Visible = True
    End Sub


    It's at this point that I want the report to pick up the selected data from above.

    Private Sub prv_Rpt_38_Click()
    DoCmd.OpenReport "rpt 30 Customers", acViewPreview
    DoCmd.RunCommand acCmdZoom75
    End Sub

    I hope this is reasonably clear?
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing Multiple Choices (a2k)

    Is it safe to assume that strWhere has been declared at the module level (i.e. at the top of the module)?

    If so, you can change the line

    DoCmd.OpenReport "rpt 30 Customers", acViewPreview

    to

    DoCmd.OpenReport "rpt 30 Customers", acViewPreview, , strWhere

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Multiple Choices (a2k)

    Hans,

    strWhere was one of my many, and what seemed to me to be an obvious, variations.

    The following is the totally stripped code in the totally stripped form.

    The ability to select a single or any number of CN's works perfectly.

    That's the good news

    When I go to "Print", it displays all of the records available as opposed to the specific ones I've selected.



    Option Compare Database
    Option Explicit
    '==============================================
    Dim strWhere As String
    '==============================================
    Private Sub prv_SrchRep_01_Click()
    Dim varItem As Variant
    Dim strWhereCN As String
    DoCmd.OpenReport "rpt 30 Customers", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom75
    End Sub
    '================================================= ============================================
    Private Sub butSelect2_Click()
    Dim varItem As Variant
    Dim strWhereCN As String
    For Each varItem In Me.pickCN.ItemsSelected
    strWhereCN = strWhereCN & ", " & Me.pickCN.ItemData(varItem)
    Next varItem
    If Not strWhereCN = "" Then
    strWhereCN = Mid(strWhereCN, 3)
    strWhereCN = "AND SRT_CN In (" & strWhereCN & ")"
    End If
    '================================================= ============================================
    strWhere = strWhereCN
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    If DCount("*", "qry 01 CN", strWhere) = 0 Then
    MsgBox ("Your combination of choices couldn't find any records." & vbNewLine & " " & vbNewLine & " Please click OK then Red Button"), vbInformation, " No Records Found !!"
    Else
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End If
    If strWhere = "" Then
    Me.sbfFiltered.Form.RecordSource = "qry 01 CN"
    Else
    Me.sbfFiltered.Form.RecordSource = "SELECT * FROM [qry 01 CN] WHERE " & strWhere
    End If
    Me.sbfFiltered.Visible = True
    End Sub


    It's always suspensful to see what obvious thing I've missed.
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing Multiple Choices (a2k)

    If you use this code, you should click butSelect2 before you click prv_SrchRep_01 because you set the value of strWhere in butSelect2_Click.

    If you don't want that, you'd have to copy the code that sets strWhere from butSelect2_Click to prv_SrchRep_01_Click, above the line that opens the report.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Multiple Choices (a2k)

    Hans,

    In actual fact that is what I was doing.

    I clicked on butSelect2, which would select the records I wanted, and then would click on prv_SrchRep_01, and end up with all records.

    At your suggestion, I copied the whole shooting match from butSelect2 to just in front of opening the report, and

    that Works!

    It's not very pretty, it doesn't make sense, but who cares.

    Thanks very much, Hans
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing Multiple Choices (a2k)

    If you want to reduce the duplication, you could move the common code to a separate Sub in the same module, and call that Sub from both On Click event procedures.
    The advantage of that approach is that if you want to modify or expand the code, you'd have to do it in one place only.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Multiple Choices (a2k)

    Thanks for the suggestion, Hans.

    Because I'm pre-selecting records, there is no need for me to check if there are none, so I've managed to cut the code down to:

    Private Sub prv_SrchRep_01_Click()
    Dim varItem As Variant
    Dim strWhereCN As String
    For Each varItem In Me.pickCN.ItemsSelected
    strWhereCN = strWhereCN & ", " & Me.pickCN.ItemData(varItem)
    Next varItem
    If Not strWhereCN = "" Then
    strWhereCN = Mid(strWhereCN, 3)
    strWhereCN = "AND SRT_CN In (" & strWhereCN & ")"
    End If
    strWhere = strWhereCN
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    If DCount("*", "qry 01 CN", strWhere) = 0 Then
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End If
    Me.sbfFiltered.Visible = True
    DoCmd.OpenReport "rpt 00111 - Brains II - Generic", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom75
    End Sub

    As they say " A Bird in the Hand feels Furry," so I'm going to quit while I'm ahead. ( for the moment )
    Cheers,
    Andy

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Multiple Choices (a2k)

    Hans,

    Just to prove that I really do pay attention to your suggestions:

    '==============================================
    Private Sub GetCNs()
    Dim varItem As Variant
    Dim strWhereCN As String
    For Each varItem In Me.pickCN.ItemsSelected
    strWhereCN = strWhereCN & ", " & Me.pickCN.ItemData(varItem)
    Next varItem
    If Not strWhereCN = "" Then
    strWhereCN = Mid(strWhereCN, 3)
    strWhereCN = "AND SRT_CN In (" & strWhereCN & ")"
    End If
    strWhere = strWhereCN
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    If DCount("*", "qry 01 CN", strWhere) = 0 Then
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End If
    Me.sbfFiltered.Visible = True
    End Sub
    '==============================================
    Private Sub prv_SrchRep_01_Click()
    Call GetCNs
    DoCmd.OpenReport "rpt 30 Customers", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom75
    End Sub
    '==============================================
    Cheers,
    Andy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Printing Multiple Choices (a2k)

    Yep, that looks good.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Multiple Choices (a2k)

    Hans,

    Thanks for checking
    Cheers,
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •