Search feature

Can it be done?
the answer is YES!!!

Anything is possible, given sufficient time and money.  :)

Yes, it's possible but a little tricky;  you have to build a criteria statement, and then use that to open the form.

Create a form to display your records, make its default view Datasheet, and set the allow other views to No.

On your form drop an unbound field named txtFind, and a button called btnFind.  In the button's OnClick event, drop this code:

Dim strCriteria As String, strCriteriaStatement As String

strCriteria = Me.txtFind
strCriteriaStatement = "TextField like  '*" & strCriteria & "*' or Number field = " & strCriteria & " or DateField = #" " & strCriteria & "#"
DoCmd.OpenForm "Datasheete_FullData", acFormDS, , strCriteriaStatement

Select allOpen in new window

Modify strCriteriaStatement to include the names of all your fields.  (Note that the syntax is different for matching text, number, and date fields;  if you have other types they will probably require other syntax.
Test.  :)

Let us know how it progresses;  post your DB here if you hit a snag.  :)

Its perfectly viable..i have developed a function that does exactly this but is a bit unpolished and i want to make an article on this...if you wait for a few days i will release it....

Why would you spend your time and your employer's money to replicate a feature that is currently built in and already tested and fully functional?  Use the binoculars.   That is what they are for.

Pat, because the binoculars leave too much chance that a user could replace.

Paul,

I'm sure the code in this like is messed up.  Could you possibly tell me what I might be doing wrong?

I'm getting a compile error, Expected end of statement after I enter the last quote mark at the end of the line.  

strCriteriaStatement = "[DocumentName] like  '*" & strCriteria & "*' or "[ProductLine] like  '*" & strCriteria & "*' or "[ProductName] like  '*" & strCriteria & "*' or "[ModelN] like  '*" & strCriteria & "*' or "[FormN] like  '*" & or "[LocationPathAndLink] like  '*" & strCriteria & "*' or "[ForeignLangFormN] like  '*" & strCriteria & "*' or "[ForeignLangFileLocationPathAndLink] like  '*" & strCriteria & "*' or "[Notes] like  '*" & strCriteria & "*' or [ProductGroup] = " & strCriteria & " or [DocumentType] = " & strCriteria & " or [OriginalLanguage] = " & strCriteria & " or [ForeignLang] = " & strCriteria & " or [RevisionDate] = #" " & strCriteria & "#"

Select allOpen in new window

It is very inefficient to search every column of every record for a string value.  Spend some extra time and create something more robust.  Here's a picture of one of mine.Here's the code behind the search button.

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 strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String

    strSelect = "Select * from qClientList "
    strSelectForExport = "Select * from qClientListExport "
    strOrderBy = " ORDER BY FullName"
    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.txtaddress & "" = "" Then
    Else
        strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & 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.txtPhone & "" <> "" Then
        strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.cboCounty & "" <> "" Then
        strCondition = "County = '" & Me.cboCounty & "'"
        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.cboTier & "" <> "" Then
        strCondition = "PCATier = " & Me.cboTier
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If

    If Me.cboProgramID & "" = "" Then
    Else
        strCondition = "ProgramID = " & Me.cboProgramID
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    'Funding
    If Me.chkT19 = True Then
        strCondition = "T19 = 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
    If Me.chkMFP = True Then
        strCondition = "MFP = True"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    
    If Me.chkNOTT19 = True Then
        strCondition = "Not(T19 = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTSelf = True Then
        strCondition = "Not (Self = True)"
        If strWHERE = "" Then
            strWHERE = strCondition
        Else
            strWHERE = strWHERE & " AND " & strCondition
        End If
    End If
    If Me.chkNOTMFP = True Then
        strCondition = "Not(MFP = 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 4
            strCondition = "StatusID = 1427"          'Pending
        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
    
    
'query for form/report
    If strWHERE = "" Then
        strSQL = strSelect
    Else
        strSQL = strSelect & " WHERE " & strWHERE
    End If

    strSQL = strSQL & strOrderBy
    Me.txtQuery = strSQL
    
'query for Excel
    If strWHERE = "" Then
        strSQL = strSelectForExport
    Else
        strSQL = strSelectForExport & " WHERE " & strWHERE
    End If
    
    strSQL = strSQL & strOrderBy
    Me.txtQueryExport = strSQL
End Sub

Select allOpen in new window

John, please post a link to your article when it's finished... it sounds fascinating!  
Steve, I played with this a bit more;  here's what I came up with:

Private Sub btnSearch_Click()
Dim strCriteria As String, strCriteriaStatement As String

'confirm variable is empty
strCriteriaStatement = ""


If IsNull(Me.txtFind) Or Len(Me.txtFind) = 0 Then
   Exit Sub
Else
   strCriteria = Me.txtFind
End If

'list all numeric fields
If IsNumeric(Me.txtFind) Then strCriteriaStatement = "TextField like  '*" & strCriteria & "*' or IntegerField = " & strCriteria & " or DecimalField = " & strCriteria & " Or CurrencyField = " & strCriteria
   If Len(strCriteriaStatement) > 0 Then strCriteriaStatement = strCriteriaStatement & " or "

'list all date fields
If IsDate(strCriteria) And InStr(strCriteria, ".") = 0 Then
   strCriteriaStatement = strCriteriaStatement & "DateField between #" & strCriteria & " 12:00:00 am# and #" & strCriteria & " 11:59:59 pm#"
   If IsDate(Me.txtFind) And Len(strCriteriaStatement) > 0 Then strCriteriaStatement = strCriteriaStatement & " or "
End If

'list all text fields
strCriteriaStatement = strCriteriaStatement & "TextField like  '*" & strCriteria & "*'"

If DCount("*", "AllFieldTypesTb", strCriteriaStatement) > 0 Then
   DoCmd.OpenForm "AllFieldTypesFrm", acFormDS, , strCriteriaStatement
Else
   MsgBox "Sorry, no records like " & strCriteria & " in AllFieldTypesTb."
End If


End Sub

Select allOpen in new window

Obviously, this would have to be modified for your own table, field, and form names, but it's working as I would expect.  :)

Steve, right away

& " or [RevisionDate] = #" " & strCriteria & "#"

Select allOpen in new window

jumped out at me;  there are doubled quotation marks.

I also noticed

or [ProductGroup] = " & strCriteria & " or 

Select allOpen in new window

if ProductGroup (or DocumentType or OriginalLanguage) is not a numeric field   --and I suspect it's not-- you need to concatenate single quotes around it (them).

Take another swing, and let us know how it works.  :)

I've created one that can search the entire db, but sorry I can't post it. It is for a paying client.

Paul.  I'm stuck.  Here's a stripped down database.  Can you provide assistance?
Search-Test.accdb

Here is a try:
Table b(bdesc, dt) - table a with just 2 fields, a text field bdesc and a date field dt.
If you enter a string in textbox txtFind and click cmdFind button.

Private Sub cmdFind_Click()
    Me.Filter = "bdesc like ""*" & txtFind & "*""" _
            & " OR " _
            & "Nz(dt)  Like ""*" & txtFind & "*"""
    Me.FilterOn = True
End Sub

Select allOpen in new window

You may expand for other fields, by converting each to a string.