Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining Selections (A2K)

    The following works perfectly when initiated individually, so there is no problem with the code in itself.

    I have two bound boxes

    lstSelectedFunctions and lstSelectedCompanies

    Each box individually allows me to make multiple selections within the box.

    So, I can pick numerous Functions and retrieve their matching records

    OR

    I can pick numerous Companies and retrieve their matching records

    What I need to be able to do is to pick numerous Functions

    and

    Any number of records for ONE company matching the selected Functions.

    The following is the current code

    '==============================================
    ' This code selects any number of Functions
    '==============================================
    Private Sub butSelectFunction_Click()
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstSelectedFunctions.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelectedFunctions.ItemData(varItem)
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    Me.butClear.SetFocus
    End Sub
    '==============================================

    The query that feeds the above contains two fields: F00_Functionx and FunctionName

    '==============================================
    ' This code selects any number of Companies
    '==============================================
    Private Sub butSelectCustomer_Click()
    Dim varItemCustomer As Variant
    Dim strWhereCustomer As String
    For Each varItemCustomer In Me.lstSelectedCompanies.ItemsSelected
    strWhereCustomer = strWhereCustomer & ", " & Me.lstSelectedCompanies.ItemData(varItemCustomer)
    Next
    If Not strWhereCustomer = "" Then
    strWhereCustomer = Mid(strWhereCustomer, 3)
    strWhereCustomer = "F01_RA_No In (" & strWhereCustomer & ")"
    Me.Filter = strWhereCustomer
    Me.FilterOn = True
    End If
    Me.butClear.SetFocus
    End Sub
    '==============================================

    The query that feeds the above contains two fields: F01_RA_No and F01_RA_Customer

    '==============================================
    ' This code clears the selections when completed
    '==============================================
    Private Sub butClear_Click()
    Me.RecordSource = "SELECT * FROM [qry 01 CN] WHERE F00_Function < " & Me.butClear
    Dim lngIndex As Long
    For lngIndex = 0 To Me.lstSelectedFunctions.ListCount - 1
    Me.lstSelectedFunctions.Selected(lngIndex) = False
    Next lngIndex
    For lngIndex = 0 To Me.lstSelectedCompanies.ListCount - 1
    Me.lstSelectedCompanies.Selected(lngIndex) = False
    Next lngIndex
    End Sub
    '==============================================

    I'm sure that once I get the clue needed to join these two into one selection box, that I should be able to add more should the situation arise.


    As always, thanks in advance for any help with this problem.
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    You could use one button butSelect with the following code:

    Private Sub butSelect_Click()
    Dim varItem As Variant
    Dim strWhere As String
    Dim strWhereCustomer As String
    For Each varItem In Me.lstSelectedFunctions.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelectedFunctions.ItemData(varItem)
    Next varItem
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    End If
    For Each varItem In Me.lstSelectedCompanies.ItemsSelected
    strWhereCustomer = strWhere & ", " & Me.lstSelectedCompanies.ItemData(varItem)
    Next varItem
    If Not strWhereCustomer = "" Then
    strWhereCustomer = Mid(strWhereCustomer, 3)
    strWhereCustomer = "F01_RA_No In (" & strWhereCustomer & ")"
    If strWhere = "" Then
    strWhere = strWhereCustomer
    Else
    strWhere = strWhere & " AND " & strWhereCustomer
    End If
    End If
    If Not strWhere = "" Then
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub

    What is the meaning of the line

    Me.RecordSource = "SELECT * FROM [qry 01 CN] WHERE F00_Function < " & Me.butClear

    If butClear is a command button, Me.butClear doesn't have a value.

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

    Re: Combining Selections (A2K)

    Hi Hans,

    I copied your code:

    Private Sub butSelect_Click()
    Dim varItem As Variant
    Dim strWhere As String
    Dim strWhereCustomer As String
    For Each varItem In Me.lstSelectedFunctions.ItemsSelected
    strWhere = strWhere & ", " & Me.lstSelectedFunctions.ItemData(varItem)
    Next varItem
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "F00_Function In (" & strWhere & ")"
    End If
    For Each varItem In Me.lstSelectedCompanies.ItemsSelected
    strWhereCustomer = strWhere & ", " & Me.lstSelectedCompanies.ItemData(varItem)
    Next varItem
    If Not strWhereCustomer = "" Then
    strWhereCustomer = Mid(strWhereCustomer, 3)
    strWhereCustomer = "F01_RA_No In (" & strWhereCustomer & ")"
    If strWhere = "" Then
    strWhere = strWhereCustomer
    Else
    strWhere = strWhere & " AND " & strWhereCustomer
    End If
    End If
    If Not strWhere = "" Then
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub

    into the Event Procedure of a new button called butSelect.

    I selected two Function Codes in lstSelectedFunctions which uses F00_Functionx and FunctionName from qryFunctions
    and one company in lstSelectedCompanies and which uses F01_RA_No and F01_RA_Customer from qryCN

    And I got

    Run-time error 2448
    You can't assign a vaule to this object

    which highlighted: Me.Filter = strWhere

    Did I mess something up in the translation?
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    Is butSelect on the same form as the original buttons?

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

    Re: Combining Selections (A2K)

    Yes. There are now only three buttons:

    butSelect
    butSelectCustomer
    butClear
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    Could you do the following, please?

    Immediately above the line Me.Filter = strWhere, insert a new line

    Debug.Print strWhere

    When you click the button and get the error message, you should see the strWhere string in the Immediate window.
    Please select it, press Ctrl+C to copy it, then paste it into a reply.

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

    Re: Combining Selections (A2K)

    Hope this helps,

    F00_Function In (3, 4) AND F01_RA_No In (0_Function In (3, 4), 1400)
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> My bad! The line

    strWhereCustomer = strWhere & ", " & Me.lstSelectedCompanies.ItemData(varItem)

    should have been

    strWhereCustomer = strWhereCustomer & ", " & Me.lstSelectedCompanies.ItemData(varItem)

    Sorry about the goofup!

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

    Re: Combining Selections (A2K)

    Hans,

    Absolutely no problem! It appears to be working as wished. If, however, I don't back away from this screen and go down for dinner, I will be pummeled, which at my age, can be considered to be Elder Abuse! Will get back to you later and thanks again,
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    Hans,

    Here's me back with the ongoing saga of "Pick the Whatever".

    Your original code works perfectly with all possible combinations of selections for the two items, Functions and Customers. I did change some of the names for the sake of documentational clarity.

    Dim strWhereFunction As String
    Dim strWhereCustomer As String
    For Each varItem In Me.pickFunctions.ItemsSelected
    For Each varItem In Me.pickCustomers.ItemsSelected

    Never one to leave well enough alone and also attempting to learn more on how to use your methodology I attempted to add one more selection choice with the additon of the following parameters:

    strWhereProduct
    pickProduct

    Private Sub butSelect2_Click()
    Dim varItem As Variant
    Dim strWhereFunction As String
    Dim strWhereCustomer As String
    Dim strWhereProduct As String
    For Each varItem In Me.pickFunctions.ItemsSelected
    strWhereFunction = strWhereFunction & ", " & Me.pickFunctions.ItemData(varItem)
    Next varItem
    If Not strWhereFunction = "" Then
    strWhereFunction = Mid(strWhereFunction, 3)
    strWhereFunction = "F00_Function In (" & strWhereFunction & ")"
    End If
    For Each varItem In Me.pickCustomers.ItemsSelected
    strWhereCustomer = strWhereCustomer & ", " & Me.pickCustomers.ItemData(varItem)
    Next varItem
    If Not strWhereCustomer = "" Then
    strWhereCustomer = Mid(strWhereCustomer, 3)
    strWhereCustomer = "F01_RA_No In (" & strWhereCustomer & ")"
    If strWhereFunction = "" Then
    strWhereFunction = strWhereCustomer
    Else
    strWhereFunction = strWhereFunction & " AND " & strWhereCustomer
    End If
    End If
    For Each varItem In Me.pickProduct.ItemsSelected
    strWhereProduct = strWhereProduct & ", " & Me.pickProduct.ItemData(varItem)
    Next varItem
    If Not strWhereProduct = "" Then
    strWhereProduct = Mid(strWhereProduct, 3)
    strWhereProduct = "F01_Product_ProdType In (" & strWhereProduct & ")"
    If strWhereFunction = "" Then
    strWhereFunction = strWhereCustomer
    If strWhereCustomer = "" Then
    strWhereFunction = strWhereProduct
    Else
    strWhereFunction = strWhereFunction & " AND " & strWhereCustomer & " AND " & strWhereProduct
    End If
    End If
    If Not strWhereFunction = "" Then
    Debug.Print strWhereFunction
    Me.Filter = strWhereFunction
    Me.FilterOn = True
    End If
    End If
    End Sub


    And this is what happened:

    If I selected a Function, no selections were made.

    If I selected a Customer, no selections were made.

    If I selected both a Function and a Customer, no selections were made.

    All of the above worked in the original scenario.

    If I then selected a Product, a "Please Enter Parameter Value" box popped up.

    I've obviously, albeit close, lost something in the translation when attempting to add a third set of search parameters.
    I'll probably what to setup the same sort of selections in other circumstances with even more selections, so it would be really neat if I could figure out what the correct sequence is to add further selections.

    For the moment, I'd be happy to just get my third one in.

    I would appreciate any time you spend on analyzing my attempt,
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    What is the data type of the F01_Product_ProdType field? Text or numeric?

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

    Re: Combining Selections (A2K)

    TEXT, and I think I know where this is going.
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    I have restructured the code slightly to make it easier to add more parts to it if desired.
    <code>
    Private Sub butSelect2_Click()
    Dim varItem As Variant
    Dim strWhereFunction As String
    Dim strWhereCustomer As String
    Dim strWhereProduct As String
    Dim strWhere As String

    ' Functions
    For Each varItem In Me.pickFunctions.ItemsSelected
    strWhereFunction = strWhereFunction & ", " & Me.pickFunctions.ItemData(varItem)
    Next varItem
    If Not strWhereFunction = "" Then
    strWhereFunction = Mid(strWhereFunction, 3)
    strWhereFunction = "AND F00_Function In (" & strWhereFunction & ")"
    End If

    ' Customers
    For Each varItem In Me.pickCustomers.ItemsSelected
    strWhereCustomer = strWhereCustomer & ", " & Me.pickCustomers.ItemData(varItem)
    Next varItem
    If Not strWhereCustomer = "" Then
    strWhereCustomer = Mid(strWhereCustomer, 3)
    strWhereCustomer = "AND F01_RA_No In (" & strWhereCustomer & ")"
    End If

    ' Products
    For Each varItem In Me.pickProduct.ItemsSelected
    strWhereProduct = strWhereProduct & ", " & Chr(34) & _
    Me.pickProduct.ItemData(varItem) & Chr(34)
    Next varItem
    If Not strWhereProduct = "" Then
    strWhereProduct = Mid(strWhereProduct, 3)
    strWhereProduct = "AND F01_Product_ProdType In (" & strWhereProduct & ")"
    End If

    ' Combine all strings
    strWhere = strWhereFunction & strWhereCustomer & strWhereProduct
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    Debug.Print strWhere
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub
    </code>
    To add another combo box, add a section similar to the Functions section, and add the corresponding string to the line

    strWhere = strWhereFunction & strWhereCustomer & strWhereProduct

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

    Re: Combining Selections (A2K)

    Hans,

    The next time I have the pleasure of travelling to Europe, I'll just bypass Lourdes and go directly to wherever it is you work your Access Miracles!

    Thanks as always,
    Cheers,
    Andy

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

    Re: Combining Selections (A2K)

    Hans,

    The whole thing runs perfectly when a selection is made and at least one record is found based on any combination of selection criteria.

    I added one line of code to the existing:

    strWhere = strWhereFunction & strWhereCustomer & strWhereProduct
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 5)
    Debug.Print strWhere
    Me.Filter = strWhere
    Me.FilterOn = True
    End If

    Me.txtGoToFunction.SetFocus new line

    End Sub

    However, and there's always a however, I ran into a "small" anomaly.

    If there are no records found, it goes into "Add New Record" mode, which is not a good thing.

    Turning off "Allow Additions", without going into details, makes it worse.

    So, the question is, is their a way of trapping a "No Matches Found" so that it doesn't go into "Add New Record" mode
    Cheers,
    Andy

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
  •