Results 1 to 6 of 6
  1. #1
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46

    Post Combo Box as search function in Form Header

    I am trying to include a combo box as a search function in the header of a form. The combo box shows a list of club members, and when one is selected, I want the form to show the details for that member. I note earlier versions of Access had this as an option in the combo box settings, but I believe this now has to be done with VB as an "after update" code. I have tried the following VB code but various errors are encountered....

    Private Sub SearchCombo_AfterUpdate()

    On Error GoTo Err_Process

    Dim strCriteria As String




    If (Not IsNull(Me.SearchCombo)) Then
    strCriteria = "<Last Name>=" & Me.SearchCombo
    End If

    Me.Filter = strCriteria
    Me.FilterOn = (strCrtieria <> "")

    Exit_Process:
    Exit Sub

    Err_Process:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Process

    End Sub

    I might be miles away from the correct solution here, but any help would be appreciated.

    Cheers

    Stuart

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If last name is a string then you must use quotes,
    if you are using the KEY, then no quotes....

    Code:
    If IsNull(cboBox) then 
      Me.filterOn = false
    else
      Me.filter = "[name]='" & cboBox & "'"
      Me.filterOn = true
    end if

  3. #3
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Thanks Ranman, but different problem now after I've tried your code ...

    Now when I search with the combo box, it clears the form for a new record, rather than finding the name selected from the combo box

  4. #4
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Another thought that might be causing a problem ..

    The field I am searching is a calculated field made up of different parts of the members name.

    The combo box is showing the correct list of members from the table, and the form has a field which also shows the correct list.

    All I am wanting to do is search with the combo box to return the correct record on the form.

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    There's the problem. You want the combo to have 2 fields, NAME,ID
    the cbo is bound to the ID (hidden) so the user only sees name.
    then filter on ID.
    (No quotes in the code)

    me.filter = "ID=" & me.cboBox

  6. #6
    StuartR is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Location
    Scotland
    Posts
    46
    Success ~ Thanks for your help

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

Similar Threads

  1. Creating a Search box on a header on a form
    By IAFerrari in forum Programming
    Replies: 2
    Last Post: 11-08-2015, 01:20 PM
  2. Combo box search function
    By Carouser in forum Forms
    Replies: 10
    Last Post: 08-13-2012, 10:21 PM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Replies: 1
    Last Post: 04-27-2012, 10:30 AM
  5. Replies: 1
    Last Post: 04-20-2012, 03:16 AM

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