• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

Display multiple search results on one form

In a Access 2007 database I'm trying to do a generic search-for example a street name.  I would like for every instance of that particular street to appear on one single form.  Is that possible?  Thanks for any help.
1
EASCOA
Asked:
EASCOA
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you searching a single table and field? Or are you trying to do a "full text" search, like in SQL Server?

A single able/field is fairly easy, and multiple tables/fields aren't much more difficult (just more work).

Full text is another matter ...
0
 
EASCOAAuthor Commented:
Actually I'm trying to search a single table for now and a single field..example-location address field.  I'm looking for anything in that address field.  Example  (directional) "NW" , (street name) "Houston", or (house location) "2034".  Thanks again for any help.
0
 
PatHartmanCommented:
The following is a picture of a search form and a picture of its results form and the code behind the search.  The results form contains a hyper link to open the full record where it may be edited.

Search Form
Option Compare Database
Option Explicit

Private Sub cmdBrowse_Click()
    Me.txtPath = fChooseDirectory()
End Sub

Private Sub cmdClear_Click()
Dim ctl As Control

    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox
                ctl = Null
        End Select
    Next ctl
    Set ctl = Nothing
    
    Me.fraStatus = 1
End Sub

Private Sub cmdExit_Click()
    Call CommonExit(Me)
End Sub

Private Sub cmdExportList_Click()
    Dim sFileName
    Me.txtPath.Visible = True
    Me.cmdBrowse.Visible = True
    If Me.txtPath & "" = "" Then
        MsgBox "A path is required.", vbOKOnly
        Me.txtPath.SetFocus
        Exit Sub
    End If
    sFileName = Me.txtPath & "\ClientList_" & Format(Date, "yyyymmdd") & ".xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qClientList", sFileName
End Sub

Private Sub cmdViewResults_Click()
   On Error GoTo Err_Proc

    Call BuildSQL
    DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
    Me.Visible = False

Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:

    Select Case err.Number
        Case Else
            MsgBox "Error " & err.Number & " (" & err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
    End Select
End Sub

Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strWhere As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from tblClients "
    strOrderBy = " ORDER BY LastName, FirstName"
    strWhere = ""
    
    If Me.txtFirstName & "" = "" Then
    Else
        strWhere = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
    End If
    If Me.txtLastName & "" = "" Then
    Else
        strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.txtcity & "" = "" Then
    Else
        strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.chkBUP = True Then
        strCondition = "BUP = True"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.cboCareMgrID & "" = "" Then
    Else
        strCondition = "CareMgrID = " & Me.cboCareMgrID
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.cboGenderID & "" = "" Then
    Else
        strCondition = "GenderID = " & Me.cboGenderID
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.txtZip & "" = "" Then
    Else
        strCondition = "Zip = '" & Me.txtZip & "'"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.cboRaceID & "" = "" Then
    Else
        strCondition = "RaceID = " & Me.cboRaceID
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.cboDiagnosis & "" = "" Then
    Else
        strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.txtpid & "" <> "" Then
        strCondition = "PID = '" & Me.txtpid & "'"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.txtClientID & "" <> "" Then
        strCondition = "ClientID = " & Me.txtClientID
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.txtems & "" <> "" Then
        strCondition = "EMS = '" & Me.txtems & "'"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
        
    If Me.cboLevelID & "" <> "" Then
        strCondition = "LevelID = " & Me.cboLevelID
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.chkMFP = True Then
        strCondition = "MFP = True"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.chkT19 = True Then
        strCondition = "T19 = True"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.chkCHCPD = True Then
        strCondition = "Cat4 = True"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    If Me.chkSelf = True Then
        strCondition = "Self = True"
        If strWhere = "" Then
            strWhere = strCondition
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If
    
    Select Case Me.fraStatus
        Case 1
            strCondition = "StatusID = 1318"          'Open
        Case 2
            strCondition = "StatusID = 1319"          'Closed
        Case Else
            strCondition = ""
    End Select
    If strWhere = "" Then
        If strCondition = "" Then
        Else
            strWhere = strCondition
        End If
    Else
        If strCondition = "" Then
        Else
            strWhere = strWhere & " AND " & strCondition
        End If
    End If   
    
    
    If strWhere = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWhere
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL
End Sub

Private Sub Form_Close()
    Call CommonClose(Me)
End Sub

Open in new window

SearchResultsForm.JPG
0
 
EASCOAAuthor Commented:
Thanks for all the help unfortunately the client went another direction.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now