Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Getting form critera from >1 multiple selection list boxes?

    I have code that grabs criteria from a multiple select list box in a form and then uses that criteria in a report. It is looping through the list box and grabbing all the selected FF's (Focus Factories). It works great, except now I need to add another multiple select list box (Shippable?) for the same form/report. I can't figure out how to pass 2 (or more) criteria to the report. Here is my code:

    Code:
    Private Function GetCriteria() As String
       Dim stDocCriteria As String
       Dim VarItm As Variant
       For Each VarItm In ListFilter.ItemsSelected
      stDocCriteria = stDocCriteria & "[FF] = '" & ListFilter.Column(0, VarItm) & "' OR "
       Next
       If stDocCriteria <> "" Then
      stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
       Else
      stDocCriteria = "True"
       End If
       GetCriteria = stDocCriteria
    
    End Function
    Code:
    Private Sub previewreport_Click()
    DoCmd.OpenReport "rptInventory Detail Current Quarter - Report", acViewPreview, , GetCriteria()
    End Sub
    Last edited by clawlan; 10-18-10 at 18:33.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, generally speaking you have to separate your different criteria with And:

    FieldName = 1 And OtherFieldName = 'ABC'

    In your case you can build 2 different strings and join them, but because you'd be mixing And & Or, you'd need to parenthesize:

    (FieldName = 1 Or FieldName = 2) And (OtherFieldName = 'ABC' Or OtherFieldName = 'DEF')

    For that reason and others, I'd build an IN clause instead of the Or's:

    Multi-Select Listbox

    Yours would be a little more complicated due to the multiple criteria, but it isn't really that tricky once you keep in mind what you need the end result to look like.
    Paul

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    Well, generally speaking you have to separate your different criteria with And:

    FieldName = 1 And OtherFieldName = 'ABC'

    In your case you can build 2 different strings and join them, but because you'd be mixing And & Or, you'd need to parenthesize:

    (FieldName = 1 Or FieldName = 2) And (OtherFieldName = 'ABC' Or OtherFieldName = 'DEF')

    For that reason and others, I'd build an IN clause instead of the Or's:

    Multi-Select Listbox

    Yours would be a little more complicated due to the multiple criteria, but it isn't really that tricky once you keep in mind what you need the end result to look like.
    Thanks for the response. I am fairly new to vba so your help is appreciated. How would I incorporate an IN clause? I have been working on this for a couple days and still struggling.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My link builds an IN clause.
    Paul

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    My link builds an IN clause.
    Forgive me, but I have a hard time seeing where my code and yours is different. Both use "In". I am not sure how i would use this to join the criteria from two different list boxes.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Where do you use IN in the SQL? You build

    FieldName = 1 Or FieldName = 2 OR FieldName = 3

    I build

    FieldName IN (1,2,3)
    Paul

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    Where do you use IN in the SQL? You build

    FieldName = 1 Or FieldName = 2 OR FieldName = 3

    I build

    FieldName IN (1,2,3)
    Ah, i see it now. So since I have multiple criteria, I would want the report line to be like this?

    Code:
    DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ") & Field2 IN(" & strWhere2 & ")"

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sure, except that one "&" right before Field2 would need to be "And". & is a concatenation operator, "And" is a logical operator. They each have their place.
    Paul

  9. #9
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Ok, I finally got it working with your help, thank you! I have one more question. If the user selects nothing from the list boxes, I want it to default to selecting all (I know I would have to get rid of the checks currently in the code). I've tried different things but cant figure it out. Ideas?

    Code:
    Private Sub previewreport_Click()
    
    Dim strWhere As String
    Dim strWhere1 As String
    Dim ctl As Control
    Dim varItem As Variant
    
    'make sure a selection has been made
    If Me.ListFF.ItemsSelected.Count = 0 Then
      MsgBox "You must select at least 1 Focus Factory"
      Exit Sub
    End If
    'add selected values to string
    Set ctl = Me.ListFF
    For Each varItem In ctl.ItemsSelected
      'strWhere = strWhere & ctl.ItemData(varItem) & ","
      'Use this line if your value is text
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)
    
    'make sure a selection has been made
    If Me.ListShippable.ItemsSelected.Count = 0 Then
      MsgBox "You must select at least 1 Shippable Status"
      Exit Sub
    End If
    'add selected values to string
    Set ctl = Me.ListShippable
    For Each varItem In ctl.ItemsSelected
      'strWhere = strWhere & ctl.ItemData(varItem) & ","
      'Use this line if your value is text
      strWhere1 = strWhere1 & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    'trim trailing comma
    strWhere1 = Left(strWhere1, Len(strWhere1) - 1)
    
    'open the report, restricted to the selected items
    DoCmd.OpenReport "rptInventory Detail Current Quarter - Report", acPreview, , "FF IN(" & strWhere & ") And Shippable IN(" & strWhere1 & ")"
    End Sub

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'd go a slightly different direction. If a user doesn't select from a listbox, I'd leave that field out completely (which would have the same effect as including it with all options). So you'd have one string variable, and conditionally add to it as you go down the code. That would mean you'd include the "FF In(" parts earlier in the code, when you know they've made a selection from that listbox.

    Bottom line, this

    Field1 In(1,7)

    will return the same results and be more efficient than

    Field1 In(1,7) AND Field2 In(1,2,3,4,5,6,7,8...)
    Paul

  11. #11
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by pbaldy View Post
    I'd go a slightly different direction. If a user doesn't select from a listbox, I'd leave that field out completely (which would have the same effect as including it with all options). So you'd have one string variable, and conditionally add to it as you go down the code. That would mean you'd include the "FF In(" parts earlier in the code, when you know they've made a selection from that listbox.

    Bottom line, this

    Field1 In(1,7)

    will return the same results and be more efficient than

    Field1 In(1,7) AND Field2 In(1,2,3,4,5,6,7,8...)
    Ok that makes a lot of sense. But I am having trouble using one string variable and conditionally adding to it. I am assuming i will be using IF statements, but not sure where to go from there. Can you help me out with the syntax?

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This type of thing:

    Code:
    Set ctl = Me.ListFF
    strWhere = ""FF IN("
    For Each varItem In ctl.ItemsSelected
      'strWhere = strWhere & ctl.ItemData(varItem) & ","
      'Use this line if your value is text
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
    When you do the second one, you test to see if strWhere has already been started (length greater than 0). If it has, add " AND " to it and then the second listbox info.
    Paul

  13. #13
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    This works, except if the first listbox has selections, but the 2nd doesnt, then there is the "AND" at the end. I guess I would need some if statements to see if it needs to be trimmed off or not?

    Here is my current code:
    Code:
    Dim strWhere As String
    Dim strWhere1 As String
    Dim strWhere2 As String
    Dim ctl As Control
    Dim varItem As Variant
    
    If Me.ListFF.ItemsSelected.Count <> 0 Then
    'add selected values to string
    Set ctl = Me.ListFF
    strWhere = "FF IN("
    For Each varItem In ctl.ItemsSelected
      'strWhere = strWhere & ctl.ItemData(varItem) & ","
      'Use this line if your value is text
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
    Else
    strWhere = ""
    End If
    
    If Len(strWhere) <> 0 Then
    strWhere = strWhere & " And "
    End If
    
    'add selected values to string
    If Me.ListShippable.ItemsSelected.Count <> 0 Then
    Set ctl = Me.ListShippable
    strWhere = strWhere & "Shippable IN("
    
    For Each varItem In ctl.ItemsSelected
      'strWhere = strWhere & ctl.ItemData(varItem) & ","
      'Use this line if your value is text
      strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
      
    Next varItem
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1) & ")"
    End If
    MsgBox strWhere
    Last edited by clawlan; 10-19-10 at 19:41.

  14. #14
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, I mentioned that:

    Quote Originally Posted by pbaldy View Post
    When you do the second one, you test to see if strWhere has already been started (length greater than 0). If it has, add " AND " to it and then the second listbox info.
    I wouldn't look to trim off the " AND "; I wouldn't add it in the first place if not appropriate.
    Paul

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Actually now that I look more carefully, you have the correct test, but I would have it inside the If/Then block that indicates the second listbox has selections, before this line:

    strWhere = strWhere & "Shippable IN("
    Paul

Posting Permissions

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