Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Convoluted parameters (2002 SP-2)

    In creating an unbound form with multiple unbound fields for a parameter query I discovered the need for cascading combo box(s) as well as cascading list box(s). The attached code (with thanks and a tip of the hat to HansV) one of the parameters is set by a Where statement in the print command based on contents of a list box (lstProducts). Further consideration reveals that there will be a need for reference to multiple list box(s) (ie: lstCSR, lstCity, etc) and I'm not certain how to go about including multiple list box references in the code - or even if it's possible or wise. Thus far I believe all the references would be to text fields, so the code will be amended as suggested by HansV. The original <post#=383663>post 383663</post#> had to do with cascading combo box(s) and moved on to this current issue.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Convoluted parameters (2002 SP-2)

    It certainly is possible to refer to multiple list boxes in the WhereCondition. Are they all going to be multi select list boxes (i.e. the user can select more than one item)?

    The idea is to construct individual WhereConditions for each of the list boxes, then concatenate them with " AND " in between', for example for two list boxes

    strWhereCSR = ...
    strWhereCity = ...
    strWhereTotal = strWhereCSR & " AND " strWhereCity
    DoCmd.OpenReport ReportName:="rptSomething", acViewPreview, , strWhereTotal

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Hans:

    Marvelous! I'll go to work on this today/tonight <img src=/S/munch.gif border=0 alt=munch width=19 height=17>

    In answer to your question (multi-select); the reason I'm using the list box method is because comboCustomer can ask for all orders for their account, or only orders for the lstCSR (CustomerServiceRep). My "plan" is to utilize your method of select all for the lstCSR to return all orders for the account in ComboCustomer. Optionally, I would be able to return orders for any individual CSR (or combination thereof) for that account. It becomes even more "twisted" when they ask for it based on the above parameters and by a certain destination (or combination of destinations). Thank the powers that be that I don't need to do this more than a few times a month, because I was having to create special parameter queries for each request.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Hans:

    My first pass (using only strWhereProducts =) worked just fine, but after adding strWhereCSR and strWhereTotal I get "Type Mismatch" error when clicking the cmdOpenReport (no error msg in VB). I checked the source for lstCSR to make certain there were no references to a numeric field, there are none (all text ie: CSR name, company name, etc). What else might I be looking for as concerns "Type Mismatch" ?

    Private Sub cmdOpenReport_Click()
    Dim i As Long
    Dim strWhere As String

    On Error GoTo ErrHandler

    If lstProducts.ItemsSelected.Count = 0 Then
    MsgBox "No products selected!", vbExclamation
    Exit Sub
    End If

    If lstCSR.ItemsSelected.Count = 0 Then
    MsgBox "No CSR selected!", vbExclamation
    Exit Sub
    End If

    For i = 0 To lstProducts.ListCount - 1
    If lstProducts.Selected(i) = True Then
    strWhere = strWhere & ", " & Chr(34) & lstProducts.ItemData(i) & Chr(34)
    End If
    Next i

    For i = 0 To lstCSR.ListCount - 1
    If lstCSR.Selected(i) = True Then
    strWhere = strWhere & ", " & Chr(34) & lstCSR.ItemData(i) & Chr(34)
    End If
    Next i

    ' Create string of the form IN (12, 23, 35)
    strWhereProducts = "[txtCommodity] IN (" & Mid(strWhere, 3) & ")"
    strWhereCSR = "[txtOrderedByCSR] IN (" & Mid(strWhere, 3) & ")"
    strWhereTotal = "strWhereProducts & " And " strWhereCSR"

    ' Open report with where-condition
    DoCmd.OpenReport "rptCustomerCustom", acViewPreview, , strWhereTotal
    Exit Sub

    ErrHandler:
    If Err <> 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Convoluted parameters (2002 SP-2)

    You forgot to set the value of strWhereProducts and strWhereCSR - you used strWhere for both but that won't work. (And you didn't declare strWhereProducts and strWhereCSR - naughty naughty!)

    I forgot one & in the code I posted earlier, causing the final concatenation to fail. Here is the corrected code:

    <code>Private Sub cmdOpenReport_Click()</code>
    <code> Dim i As Long</code>
    <code> Dim strWhereTotal As String</code>
    <code> Dim strWhereProducts As String</code>
    <code> Dim strWhereCSR As String</code>

    <code> On Error GoTo ErrHandler</code>

    <code> If lstProducts.ItemsSelected.Count = 0 Then</code>
    <code> MsgBox "No products selected!", vbExclamation</code>
    <code> Exit Sub</code>
    <code> End If</code>

    <code> If lstCSR.ItemsSelected.Count = 0 Then</code>
    <code> MsgBox "No CSR selected!", vbExclamation</code>
    <code> Exit Sub</code>
    <code> End If</code>

    <code> For i = 0 To lstProducts.ListCount - 1</code>
    <code> If lstProducts.Selected(i) = True Then</code>
    <code> strWhereProducts = strWhereProducts & ", " & Chr(34) & lstProducts.ItemData(i) & Chr(34)</code>
    <code> End If</code>
    <code> Next i</code>

    <code> For i = 0 To lstCSR.ListCount - 1</code>
    <code> If lstCSR.Selected(i) = True Then</code>
    <code> strWhereCSR = strWhereCSR & ", " & Chr(34) & lstCSR.ItemData(i) & Chr(34)</code>
    <code> End If</code>
    <code> Next i</code>

    <code> ' Create string of the form IN (12, 23, 35)</code>
    <code> strWhereProducts = "<!t>[txtCommodity]<!/t> IN (" & Mid(strWhereProducts, 3) & ")"</code>
    <code> strWhereCSR = "<!t>[txtOrderedByCSR]<!/t> IN (" & Mid(strWhereCSR, 3) & ")"</code>
    <code> strWhereTotal = strWhereProducts & " And " & strWhereCSR</code>

    <code> ' Open report with where-condition</code>
    <code> DoCmd.OpenReport "rptCustomerCustom", acViewPreview, , strWhereTotal</code>
    <code> Exit Sub</code>

    <code>ErrHandler:</code>
    <code> If Err <> 2501 Then</code>
    <code> MsgBox Err.Description, vbExclamation</code>
    <code> End If</code>
    <code>End Sub</code>

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>
    . . .it's too late now, but I actually thought about that and made the changes (you'd be so proud. . .I'm not quite as naughty as one would think. . .not this time anyway), but still got the same error. It seems you're actually rubbing off on me (or just finally beating some sense into my thick head). The final code works wonderfully! Thanks so very much for all your help! Now, to see what else I can do to muck it up with further parameters.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Well it took nearly an hour to do it, but I did manage to finally back myself into a corner. I have not yet amended the code to handle the additional lstbx(s) shown in the attachment (white back color), but I can see where I'm going to run into trouble. A request can be made (and often are) for a report based on the shipper/consignee - name/city, but then there are also requests for info regardless of the shipper/consignee. I need some way to include the parameters when requested, but to ignore if not needed (checkbox?), and I will have to deal with this in the code once I define the strWhere statements (presumed). Everything else works great though! (Can hardly believe I won't have to make any more "temporary" custom queries/reports). Going to get some much needed rest.
    <img src=/S/doze.gif border=0 alt=doze width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Convoluted parameters (2002 SP-2)

    If you would handle the combo boxes for shipper and consignee the same way as the other ones, you'd get a warning if you leave them empty, and the code would exit instead of opening the report. You can change this to omit the condition for shipper or consignee if the corresponding combo box is empty.

    -----------------------

    If IsNull(Me.cboShipper) Then
    strWhereShipper = ""
    Else
    ' code to assemble strWhereShipper here
    ...
    End If

    ' other parts assembled here
    ...

    strWhereTotal = strWhereProducts & " And " & strWhereCSR
    If strWhereShipper <> "" Then
    strWhereTotal = strWhereTotal & " And " & strWhereShipper
    End If

    -----------------------

    Similar for other where-conditions you only want to add if required.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Hans:

    Geesh, morning already? (you're probably just finishing lunch) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Would you mind taking a look at what I have so far? I'm not sure I understand where the If IsNull belongs, there are two of them now, and seems like there should be another End If (but what do I know?). Also, with two If IsNull conditions, I get totally lost in the If StrWhere statement (I haven't entered anything for that because of my confusion). Would there now be an Else statement in there for the second If IsNull condition?
    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Convoluted parameters (2002 SP-2)

    Can you indicate which of the list boxes may be left empty (i/e/ no items selected) and which ones still require at least one item to be selected?

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Hans:

    While waiting for your kind reply, I went back and looked at some of the printouts over the last six months or so and believe that the lstShipperCity (by comboShipperName) and lstConsigneeCity (by comboConsigneeName) would be the only ones where I would want to return all records regardless of Shipper or Consignee Name/City.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Just to show I'm not just sitting on my hands over here (though that would be about as productive), I have attached my most recent attempt. Getting a Compile Error "Block If without End If"
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Bryan,

    You need 2 more end if statements.

    If IsNull(Me.comboShipperName) Then
    strShipper = ""
    Else
    For i = 0 To lstShipperCity.ListCount - 1
    If lstShipperCity.Selected(i) = True Then
    strWhereShipper = strWhereShipper & ", " & Chr(34) & lstCSR.ItemData(i) & Chr(34)
    End If
    Next i
    End If

    If IsNull(Me.ComboConsigneeName) Then
    strConsignee = ""
    Else
    For i = 0 To lstConsigneeCity.ListCount - 1
    If lstConsigneeCity.Selected(i) = True Then
    strWhereConsignee = strWhereConsignee & ", " & Chr(34) & lstCSR.ItemData(i) & Chr(34)
    End If
    Next i

    End If
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Convoluted parameters (2002 SP-2)

    Apart from Don Lansing's valid remarks, there are a few other things that need to be changed. See attached version.
    Attached Files Attached Files

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Convoluted parameters (2002 SP-2)

    Hans:

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Beautiful, stupendous, wonderful, amazing, and just really cool! This is going to save me a great deal of grief in the future! What a wonderful solution to a real pain in the "donkey"! Thanks Hans for all your work and persaverance.

    Take the rest of the day off <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 2 12 LastLast

Posting Permissions

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