Results 1 to 4 of 4
  1. #1
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41

    Displaying search results in list box on same form as search text field


    I have a database of about 6,500 obituary records. To access a record, users open a form containing a list box (1stSearch) that's already populated with a subset of fields from the main AllDeathRecords table (LastName, FirstName, MiddleName, DeathDate). All 6,500 records are listed. The list box's Row Source is the following SQL query:
    Code:
    SELECT AllDeathRecords.PersonID, AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.MiddleName, AllDeathRecords.DeathDate
    FROM AllDeathRecords
    ORDER BY AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.DeathDate;
    They can scroll the list to find the name they want, and then double-click a record to view all its fields.

    There are a lot of names to scroll through, so I'd like to have an additional method of finding a name, by adding a Search button to the form, with a text box to specify a last name. If the user types a last name (say, Smith) and clicks Search, I'd like the list box to refresh, showing just records for people with that last name (so, all the Smiths, showing their LastName, FirstName, MiddleName, DeathDate).

    The problem I'm having is that if a user specifies a last name and clicks Search I would need to change the list box's source to a different SQL query; one that's based on only the last name search. Right now I've just given the Search button an On Click event of "Me!lstSearch.Requery"--pretty lame, considering it doesn't reference the Search criterion or change the list box Row Source. Access is snickering at me.

    Is there a way to change the Row Source of a list box control by clicking another control on the same form? Or is this not the best way to do what I want? Or is what I want impossible?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    A ComboBox will do this naturally.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The combobox AutoExpand property will show matching items in list as user types value.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would use cascading combo boxes.

    Create a new form - name it frmSearch
    Set the default view to continuous forms
    Set the form record source to
    Code:
    SELECT AllDeathRecords.PersonID, AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.MiddleName, AllDeathRecords.DeathDate
    FROM AllDeathRecords
    ORDER BY AllDeathRecords.LastName, AllDeathRecords.FirstName, AllDeathRecords.DeathDate;
    In the detail section, add text box controls for the last name, first name, DeathDate and PersonID. Set the visible property for the PersonID to FALSE.



    In the form header add two unbound combo boxes:
    The first combo box - name it cboLastName
    The row source would be
    Code:
    SELECT DISTINCT AllDeathRecords.LastName FROM AllDeathRecords ORDER BY AllDeathRecords.LastName;
    In the after update event of cboLastName, add this code:
    Code:
    Private Sub cboLastName_AfterUpdate()
       Me.cboFirstName.Requery
    End Sub

    The second combo box - name it cboFirstName
    The row source would be
    Code:
    SELECT DISTINCT AllDeathRecords.FirstName FROM AllDeathRecords WHERE AllDeathRecords.LastName = Forms!frmSearch.cboLastName ORDER BY AllDeathRecords.FirstName;

    Add a button to the form header. Name it cmdSearch. Set the caption to "Search".
    The code for the click event:
    Code:
    Private Sub cmdSearch_Click()
       On Error GoTo Err_cmdSearch_Click
    
       Dim sFilter As String
    
    
       If Len(Me.cboLastName & "") > 0 Then
    
          sFilter = "LastName = '" & Me.cboLastName & "' AND "
       End If
    
       If Len(Me.cboFirstName & "") > 0 Then
          sFilter = sFilter & "FirstName = '" & Me.cboFirstName & "' AND "
       End If
    
       If Len(sFilter) > 0 Then
          sFilter = Left(sFilter, Len(sFilter) - 5)
       End If
    
       'Debug.Print sFilter
    
       Me.Filter = sFilter
       Me.FilterOn = True
    
    
    Exit_cmdSearch_Click:
       Exit Sub
    
    Err_cmdSearch_Click:
       MsgBox Err.Description
       Resume Exit_cmdSearch_Click
    
    End Sub
    Add another button to the form header. Name it cmdClearFilter. Set the caption to "Clear Filter"
    The code in the click event:
    Code:
    Private Sub cmdClearFilter_Click()
       On Error GoTo Err_cmdClearFilter_Click
    
       Me.cboLastName = ""
       Me.cboFirstName = ""
    
       Me.Filter = ""
       Me.FilterOn = False
    
    Exit_cmdClearFilter_Click:
       Exit Sub
    
    Err_cmdClearFilter_Click:
       MsgBox Err.Description
       Resume Exit_cmdClearFilter_Click
    
    End Sub


    This is how I set up basic search forms..

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

Similar Threads

  1. Replies: 12
    Last Post: 08-17-2013, 11:49 PM
  2. Problem with displaying search results in subform
    By ShawnCartwright in forum Programming
    Replies: 7
    Last Post: 08-14-2013, 07:25 AM
  3. Replies: 3
    Last Post: 08-22-2012, 03:28 AM
  4. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  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