Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Thanks so much for your help ssanfu - it is greatly appreciated.

    Will keep those things in mind for next time.

  2. #17
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    It is difficult to give a good answer when the object names and table relationships are unknown (for whatever reason).
    Feels like I'm one of the 3 blind mice, touching the elephant's foot and trying to describe what the elephant looks like.


    I'm not sure what you want as far as searching, so maybe this will be of help.
    With 2 controls, there are 4 options
    1) Both controls Null,
    2) cboCatagory control filled in, text box control Null
    3) text box control filled in, cboCatagory control Null
    4) Both controls filled in.

    The search code deals with all 4.


    When the form opens, you can see all of the terms I entered.

    Because of the wild card character, if you search for Male, Female is also returned.
    Hi ssanfu, its me again. Thank you again for creating a relationship between my category combo box and my search box.

    I have another question to ask you. Would it be the same concept if I were to add in a list box which there would be a relationship to the combo box and then to the textbox?
    So the priority would be tapered such that it would be List Box --> Combo Box --> Search Keyword --> then results pop up.

    Additionally I would also like to have the listbox be on its own and/or have the search keyword option be related to the listbox if possible.

  3. #18
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am not sure you understand what is happening in the code.

    You have a combo box (cbo) and a text box (tb). They have no relationship - they are completely independent of each other. The code looks at the two controls and determines if there are values entered into either/both if the controls.
    The cbo is only compared with one field in the table; the tb is compared with the other fields listed.


    If there is a value entered in the control, enter a 0, else enter a 1.
    With 2 controls, there are 4 options.
    cbo
    tb
    0 0
    0 1
    1 0
    1 1


    With 3 controls (lst = list box, cbo = combo box, tb = text box), there are 8 options to code for:
    lst
    cbo
    tb
    0 0 0
    0 0 1
    0 1 0
    0 1 1
    1 0 0
    1 0 1
    1 1 0
    1 1 1




    If the controls are independent of each other (a value in a control is NOT dependent on another control), the code has to account for each option.
    If the cbo is dependent on a lst selection (dependent or cascading) the you are back to two controls/ 4 options.




    Quote Originally Posted by vha7 View Post
    Would it be the same concept if I were to add in a list box
    After all of this, the answer to your question is "Yes, iit is/could be the same concept".
    But, again, the controls are independent of each other. And you have to determine what fields each of the controls are looking at.



    Quote Originally Posted by vha7 View Post
    So the priority would be tapered such that it would be List Box --> Combo Box --> Search Keyword
    In this case, you would require all 3 controls to be filled for each search. Then you have one condition (plus the error message if 1 or more controls are left blank).



    Quote Originally Posted by vha7 View Post
    Additionally I would also like to have the listbox be on its own and/or have the search keyword option be related to the listbox if possible.
    You lost me here.... explain what you want to happen in each case........ give examples..

  4. #19
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    vha7_Demo2 (2).zipQUOTE=ssanfu;406236]

    With 3 controls (lst = list box, cbo = combo box, tb = text box), there are 8 options to code for:
    lst
    cbo
    tb
    1
    1
    1 1
    1
    1 1
    1 1
    1 1 1

    If the controls are independent of each other (a value in a control is NOT dependent on another control), the code has to account for each option.
    If the cbo is dependent on a lst selection (dependent or cascading) the you are back to two controls/ 4 options.
    [/QUOTE]

    Hi ssanfu, I understood your code when you explained it now - and the above is exactly what I am searching for. Do you mind helping me with the code for this - an example with an additional listbox and/or checkbox option? I have attached your demo database with the additional ad ons.

    Notably, I want the listbox and checkbox to be "values" instead of a query and what not, similar to the concept of the combo drop box where it uses the LIKE operator to search the key term across all fields/columns.

    EDIT1: Nvm, I somehow managed to get my code and search form to work!!
    Last edited by vha7; 08-20-2018 at 01:40 PM.

  5. #20
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy you got it to work. Would be interested in seeing the code.


    BTW, you should get rid of ALL spaces in object names - fields, tables & forms.This will save you lots of grief.

  6. #21
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    Happy you got it to work. Would be interested in seeing the code.


    BTW, you should get rid of ALL spaces in object names - fields, tables & forms.This will save you lots of grief.
    Pretty much it worked out by accident. I asked for help for another code for a multi-select listbox to apply to the same table and fortunately, I was just trying to figure out how I could link everything like you said and then I noticed my count box for the number of results was tapered and smaller than usual and I realized it filtered my list box based on the category and search keyword I have chosen.

    But heres the code:

    Code:
    Private Sub List63_AfterUpdate()
    
    
        Dim strList As String
        Dim strSplit As Variant
        Dim strFilter As String
        Dim i As Integer
    
    
    
    
        strList = getLBX(Me.List63)    'iterate listbox and get string of values
    
    
        Me.fGetlbx = strList    'just to display on sample form
    
    
        strSplit = Split(strList, ",")    'split the list of values
    
    
    
    
        For i = 0 To UBound(strSplit)
    
    
            strFilter = strFilter & "[Reference Citation] like '*" & strSplit(i) & "*' Or "    'construct strFilter
    
    
        Next i
    
    
        If Len(strFilter) > 0 Then
            strFilter = Left(strFilter, Len(strFilter) - 3)    'trim off trailing "Or"
        End If
    
    
        Me.txtFilter = strFilter    'just to display on sample form
    
    
        'Apply the filter
        If Me.List63.ItemsSelected.Count > 0 Then
            Me.subPublications2.Form.Filter = strFilter
            Me.subPublications2.Form.FilterOn = True
        Else
            Me.subPublications2.Form.FilterOn = False
        End If
    
    
    End Sub
    Module code:
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function getLBX(Lbx As ListBox, Optional intColumn As Variant = 0, Optional Seperator As String = ",", _
                           Optional Delim As Variant = Null) As String
    
    
    'Iterates thru the multiselect listbox and constructs a string of the selected items
    'Arguments:
    'Lbx is Listbox Object ie.Me.MyListbox
    'intColumn is the column # to be returned
    'Seperator is the character seperating items in string returned
    'Delim is optional delimiter to be return in string ie. #1/1/2001#,#12/25/2015#
    
    
        Dim strList As String
        Dim varSelected As Variant
    
    
       On Error GoTo getLBX_Error
    
    
        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
    
    
        getLBX = strList
    
    
       On Error GoTo 0
       Exit Function
    
    
    getLBX_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getLBX of Module modLBX"
    
    
    End Function
    
    
    Public Sub SelectLBX(Lbx As ListBox, strIN As String)
    
    
    'Clears the multiselect listbox and then selects the values in junction table
    
    
       On Error GoTo SelectLBX_Error
    
    
        Call ClearList(Lbx)
    
    
        Dim varItm As Variant
        Dim i As Integer
        Dim y As Integer
    
    
        varItm = Split(strIN, ",")
    
    
        For y = 0 To UBound(varItm)
    
    
            For i = 0 To Lbx.ListCount - 1
    
    
                If Lbx.ItemData(i) = varItm(y) Then
                    Lbx.Selected(i) = True
                    Exit For
                End If
    
    
            Next i
    
    
        Next y
    
    
       On Error GoTo 0
       Exit Sub
    
    
    SelectLBX_Error:
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SelectLBX of Module modLBX"
    
    
    End Sub
    Function ClearList(lst As ListBox) As Boolean
    
    
        On Error GoTo Err_ClearList
    
    
        'Purpose:   Unselect all items in the listbox.
        
        Dim varItem As Variant
    
    
        If lst.MultiSelect = 0 Then
            lst = Null
        Else
            For Each varItem In lst.ItemsSelected
                lst.Selected(varItem) = False
            Next
        End If
    
    
        ClearList = True
    
    
    Exit_ClearList:
        Exit Function
    
    
    Err_ClearList:
    
    
        Resume Exit_ClearList
    
    
    End Function
    
    
    Public Function SelectAll(lst As ListBox) As Boolean
    
    
        On Error GoTo Err_Handler
    
    
        'Purpose:   Select all items in the multi-select list box.
        
        Dim lngRow As Long
    
    
        If lst.MultiSelect Then
            For lngRow = 0 To lst.ListCount - 1
                lst.Selected(lngRow) = True
            Next
            SelectAll = True
        End If
    
    
    Exit_Handler:
        Exit Function
    
    
    Err_Handler:
    
    
        Resume Exit_Handler
    
    
    End Function

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    For the routines "Function ClearList" and "Function SelectAll", I do it a little different.

    I have 2 buttons: "Clear Selection" and "Reverse Selection".
    The code for the buttons is
    Code:
    Private Sub cmdReverse_Click()
       Call DoList("R", "lstEmp")
    End Sub
    
    
    
    Private Sub cmdClear_Click()
       Call DoList("C", "lstEmp")
    End Sub
    The code for the sub "DoList" is
    Code:
    Sub DoList(psAction As String, psTLD As String)
       Dim theList As Control, n As Long  
    
       Set theList = Me(psTLD)
       Select Case psAction
          Case "C"
             'Unselect all items in the listbox.
             For n = 0 To theList.ListCount
                theList.Selected(n) = False
             Next
          Case "R"
             'reverse select of items in the listbox.
             For n = 0 To theList.ListCount
                theList.Selected(n) = Not theList.Selected(n)
             Next
       End Select
    End Sub
    If there are no selections in the list box and you click the Reverse button, All options are selected. If you then un-select one (or more) options and click the Reverse button, the select state gets reversed.
    Or you want all selected except one option: click the Clear button, select the one option you DO NOT want, then click the reverse button.



    In any case, good luck with your project......

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

Similar Threads

  1. I want to create a link combo box
    By Jeremy Sng in forum Access
    Replies: 4
    Last Post: 10-27-2016, 07:08 AM
  2. Replies: 1
    Last Post: 01-22-2016, 04:36 AM
  3. Replies: 3
    Last Post: 07-15-2015, 03:14 AM
  4. Replies: 5
    Last Post: 05-10-2014, 09:24 AM
  5. Replies: 1
    Last Post: 01-10-2013, 05:59 PM

Tags for this Thread

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