Page 2 of 2 FirstFirst 12
Results 16 to 30 of 30
  1. #16
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Just glad to be of assistance.

    Though a couple observations for future reference, when you're building your WHERE clause in a query instead of "WHERE fld=a OR fld=b OR fld=c OR etc" you can shorten that to "WHERE fld In (a, b, c, etc)" see here for further reading. You already built the sql string, instead of then building the value list from the strings results you could just set the rowsource of the list box to the sql string and requery the list box. Finally, it's a good idea to close the recordset variable and set it to nothing, see tip #9 here http://allenbrowne.com/ser-29.html

  2. #17
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Now there is another requirement that I was not aware of. Based on your updated code if there is no selection in listbox 1 then the code comes out of the listbox 2 but still the listbox 3 and listbox 4 records are visible. Is there a way where in if the selection in listbox 1 changes all the records from all three listboxes must also change?

  3. #18
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Should they change or just be emptied? You can just repeat your code Form_frmFILTER.listbox_name_here.RowSource = "" for each listbox to make them blank until the user makes their relevant selections.

  4. #19
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643

    Post

    I use a generic function to retrieve the values of a multiselect listbox (below)

    you can then easily construct a where clause such as - ...Where [MAchine ID] in (" & getLBX2(me.YourListboxName) & ")"

    Code:
    Public Function getLBX2(lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",",  _
    Optional Delim As Variant = Null) As String  
    
    
        Dim strList As String
        Dim varSelected As Variant
    
    
        If lbx.ItemsSelected.Count = 0 Then
            'MsgBox "Nothing selected"
        Else
    
    
            For Each varSelected In lbx.ItemsSelected
    
    
                strList = strList & Delim & lbx.Column(intColumn, (varSelected)) & Delim & Seperator
    
    
            Next varSelected
    
    
            strList = Left$(strList, Len(strList) - 1)  'remove trailing comma
    
    
        End If
    
    
        getLBX2 = strList
    
    
    End Function

  5. #20
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Yes that is working. Thank you again. As I go deeper i find out new things. Is it possible to search by directly clicking on the records in listbox rather than first selecting the records and then clicking the search button?

  6. #21
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Yes, you can use the AfterUpdate event of the list box to run your code after everytime the listbox is changed.

    I'd move all the code in your button's OnClick event to a new sub then just call that sub from the button's onclick event and the listboxes AfterUpdate event.

    So this:
    Code:
    Private Sub cmdMyButton_Click()
        'my code here
        ' many lines
    End Sub
    might become something like this:
    Code:
    Private Sub mySub()
        'my code here
        ' many lines
    End Sub
    
    Private Sub cmdMyButton_Click()
        mySub
    End Sub
    
    Private Sub lstMyList_AfterUpdate()
        mySub
    End Sub

    Go to the forms Design View -> Right click your listbox and select Properties -> Go to the events tab -> Click on the button next to 'After Update' with a ... -> call your sub here.

  7. #22
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    Private Sub listMachine_BeforeUpdate(Cancel As Integer)
    Call search
    End Sub



    Private Sub btnMachine_Click()
    Call search
    End Sub
    Sub search()
    Dim intMachineID As Integer
    Dim strWhrMachineID As String
    Dim strSQLstr As String
    Dim rsData As Recordset

    This is what I had done. But the same procedure does not hold good for listbox 2,3 and 4. Is it probably because the listbox 1 rowsource is table based and the subsequent listbox row source is value list?

  8. #23
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Deepak.Doddagoudar View Post
    This is what I had done. But the same procedure does not hold good for listbox 2,3 and 4. Is it probably because the listbox 1 rowsource is table based and the subsequent listbox row source is value list?
    No that shouldn't be an issue. Without seeing your full code I can only guess that your simply not calling your procedures where they need to be called. Do your buttons still work?

  9. #24
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I will send you the code.Click image for larger version. 

Name:	Capture.PNG 
Views:	18 
Size:	45.1 KB 
ID:	33094

  10. #25
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    I made a small mistake instead of after update I coded it for before update. But that does not matter right?

  11. #26
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by Deepak.Doddagoudar View Post
    I made a small mistake instead of after update I coded it for before update. But that does not matter right?
    Someone more knowledgeable can answer this question but I'd think it most appropriate to put it in the AfterUpdate or the OnClick event of the list. The before update event is typically used for validation procedures. That said it should still work though.


    Side note here: I'd move the three lines that empty the row sources of listboxes 2 through 4 up to the beginning of the sub with the line that empties listox 1. Where they are the wont be executed in the event your where clause is blank in which case you wanted everything blank.

    Back to the question a hand: what's the problem or what exactly are expecting to happen? If you want listboxes 2 - 4 to be updated when you click on them you will have to code their respective events.

  12. #27
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I made a small mistake instead of after update I coded it for before update. But that does not matter right?
    Yes, that may very well matter. You should be using the AfterUpDate event.

    on another note, when you show code in your thread it would be much better to copy and paste the code and surround it with the code tags (code tags are "[" Code"]" and "["/Code"]" without the ""'s) you get them with the # button.
    You posted a screenshot which makes it harder to sugget code for you as no one wants to re-type it.

    Is Form_frmFILTER your main form? if so is its real name just frmFILTER? if so you probably should not be using Form_frmFILTER as thats the module refernce of the form and could have unintended results. if the listboxes are on frmFilter you should be using the Me Keyword to reference them - me.MyListBoxName - or the forms collection - Forms("MyFormName") or Forms!MyFormName.

  13. #28
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    heres a very simple example which uses 2 generic functions.
    it gets the selections from the first listbox, constructs an sql select string using an "In" clause and passes it to the other function which populates the listbox.

    listboxes.zip

  14. #29
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    That was a very good example. Thank you the problem is solved.

  15. #30
    Deepak.Doddagoudar is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Sweden
    Posts
    43
    The problem is solved. Thank you for your help I appreciate it. But now I have a bigger problem. I have posted a new thread, please look at it and see if you can help me.

    Regards
    Deepak

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple Listboxes on form with subform
    By cbrsix in forum Programming
    Replies: 12
    Last Post: 08-01-2013, 03:35 PM
  2. Multiple Listboxes in Search Form
    By cbrsix in forum Programming
    Replies: 6
    Last Post: 05-03-2013, 12:11 PM
  3. Replies: 5
    Last Post: 02-05-2013, 01:18 PM
  4. Combining data from multiple listboxes
    By dshillington in forum Access
    Replies: 1
    Last Post: 10-04-2012, 11:53 AM
  5. Multiple Listboxes
    By Butterflies88 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums