Results 1 to 12 of 12
  1. #1
    coes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    Unhappy Combo box search on all fields

    Hi everyone



    I have been searching for a method/code which I used in an Access 2007 database that consisted of two combo boxes. The first combo box pulled through all the fields on the form and the second was used to search, for instance the fields may be surname, first_name, Age, post_code. You would select one of these from the first, i.e. first_name and then select 'Bill' from the second and the database would select everyone called Bill... That simple. It had a little bit of coding in the form but very minimal but worked like a charm.... I've changed jobs and didn't keep the code and now cannot find it .... If anyone knows of it I would love to be pointed in the right direction. There was a sample database of this on the web and I can't find it.

    Any ideas would be appreciated.

    Best wishes
    Sara

  2. #2
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Cascading combobox is what you're looking for.

    Hopefully this link will help:

    https://www.nhaustralia.com.au/blog/...-Access-forms/

  3. #3
    coes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Thank you for the quick response and for the link, I know this is the usual way but the sample in question used the background code to pull all field names into the lookup, when I used it before there was about 20 fields in the form, all of which you could do a search on by adding the criteria in the second combobox. Only example I found of this and meant you didn't need a search form or numerous boxes.

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Thats what this will do, the second combo box uses the first one in the criteria. There is a requery event in the example that happens on change of combo box 1.

    So all you have is 2 combo boxes to get the result you require. What you do with that result is something else. If im missing something let me know.

  5. #5
    SahandSabahi is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2018
    Posts
    7
    the cascading solution you have suggested limits the available records in the second combo box to corresponding records in another field meeting the filtration criteria determined in the first combo box.

    what coes is asking for is to chose the field itself (first_name for example) in the first combo box, and then determine the record (Bill for example) according to which this field (first_name) should be filtered. in the next filtration attempt, one can chose a different field (Las_name for example) to filter the form.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I think you want something like this example on Allen Browne's site http://allenbrowne.com/ser-62.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I see, sorry for the confusion.

    http://www.databasedev.co.uk/filter_combo_boxes.html

    Is this better?

  8. #8
    coes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Thank you everyone, SahandSabah has explained it much clearer than myself, the first combo just gives a dropdown of all the fields in the form. I did think it was an allenbrowne db but now not so sure as his still isn't as intuitive as the one I found. I'm sure I will be adopting the more orthodox approach.

  9. #9
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    I think the problem was my lack of sleep rather than your explination.

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi, everybody!

    coes, give the code a try in a bound form that have some text boxes etc and a combobox with the name "Combo0" which has two columns and set 0 cm as width of the first.

    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Form_Load()
        FillCombo
    End Sub
    
    Sub FillCombo()
        Dim ctrl As Control
        Dim strList As String
    
        For Each ctrl In Me.Controls
            If (TypeName(ctrl) = "TextBox") Or (TypeName(ctrl) = "ComboBox") Then
                If ctrl.Name <> Me.Combo0.Name Then
                    strList = strList & ctrl.ControlSource & ";" & ctrl.Name & ";"
                End If
            End If
        Next ctrl
    
        With Me.Combo0
            .RowSource = strList
            .RowSourceType = "Value List"
        End With
    End Sub
    
    Private Sub Combo0_AfterUpdate()
        With Me.Combo0
            'It would be a filter for your form if all criteria was text
            MsgBox "[" & .Value & "]='" & Me.Controls(.Column(1)) & "';" 
        End With
    End Sub
    Let us know if suit your needs.
    Good luck!

  11. #11
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Just fiddling around, Is this similiar to what your looking for?
    I used listboxes and a filter but you can also use combo boxes.

    note that the list of fields has a hidden column which is the datatype of the field and is used for the delimiter in the filter string.
    Attached Files Attached Files

  12. #12
    coes is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Thank you everyone for your responses, I have managed to find the example, it was an Allen Browne item http://allenbrowne.com/AppFindAsUType.html very quick to use.

    Thank you again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-24-2017, 08:06 AM
  2. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  3. Replies: 11
    Last Post: 08-08-2013, 03:31 PM
  4. Replies: 1
    Last Post: 05-03-2012, 04:59 AM
  5. combo box to search multiple fields
    By jo15765 in forum Forms
    Replies: 21
    Last Post: 12-23-2010, 03:28 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