Results 1 to 7 of 7
  1. #1
    5rise is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    4

    Multiple combo boxes to search different fields

    Hi,



    In an older version of Access, I created a form with various combo boxes to search the underlying record (search whilst typing)

    For instance, First name, Surname & Address.

    The boxes are linked together with an ID field so that the same underlying record is shown irrespective of which box is used for the search.

    However, I've discovered that in the current version of Access (2007-2013), I can't reproduce the behaviour despite hours of head scratching.

    Originally, I used the combo box wizard and then included the ID field on the form.

    For the "On current" [Event Procedure] for the form I put:

    Code:
    Private Sub Form_Current()
    Combo1 = ID
    Combo2 = ID
    Combo3 = ID
    
    End Sub
    And for the "After Update" [Event Procedure] for the combo box the wizard would generate:

    Code:
    Private Sub Combo1_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    Set rs = Me.Recordset.Clone
        rs.FindFirst "[ID] = " & Str(Me![Combo1])
        Me.Bookmark = rs.Bookmark
    End Sub
    (I should say my skills are very limited and I don't understand the above code - it's what the wizard generated)

    As I mentioned, for some reason, this doesn't work in the newer version of Access and instead just shows the ID field in each box.

    I'd be extremely grateful for some help on this.

    Regards
    Jon

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of FindFirst, use FILTER to show all records that fit the s[election....

    Code:
    sub btnFind_click()
    dim sWhere as string
    
    if not IsNull(cboLast) then sWhere = "and [lastname]='" & cboLast & "'"
    if not IsNull(cboCity) then sWhere = "and [city]='" & cboCity & "'"
       
        ' remove first AND
    sWhere = mid(sWhere,4)
    
      'appy filter
    if sWhere="" then
      me.filterOn = false
    else
      me.Filter = sWhere
      me.filterOn = true
    endif
    end sub

  3. #3
    5rise is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    Many thanks for this but can you tell me where do I put the code and does this dispense with the need for the ID field and the On Current Event Procedure?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Does everything else work correctly, is the right ID being displayed in the combobox? You may have your column widths set incorrectly, to hide the first column and show the second column of the combobox.

  5. #5
    5rise is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    Thanks, but can't really answer as not sure how to incorporate the suggested code

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Column widths are in the properties of the combobox, not code.

  7. #7
    5rise is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    4
    Yes, but I need to put the original code in somewhere to see whether it works correctly or not. If not, then I could look at your suggestion.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Search Query Based on Multiple Combo / Text Boxes
    By ItsJustRey in forum Queries
    Replies: 5
    Last Post: 07-20-2016, 02:04 PM
  3. Replies: 6
    Last Post: 02-26-2014, 05:06 PM
  4. Replies: 5
    Last Post: 08-05-2013, 09:47 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