SteveL13
asked on
Search feature
I have a form that I want to use to be able to search for records in a table. The table has various field types... short text, data, etc. On the search form I want just one field where the user can enter a string of characters and then via a command button, have a datasheet form open to display the records that had the string of characters ANYWHERE in the table fields. I hope this makes sense. Can it be done?
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:
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. :)
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
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.
ASKER
Pat, because the binoculars leave too much chance that a user could replace.
ASKER
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.
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 & "#"
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
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:
Obviously, this would have to be modified for your own table, field, and form names, but it's working as I would expect. :)
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
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
I also noticed
Take another swing, and let us know how it works. :)
& " or [RevisionDate] = #" " & strCriteria & "#"
jumped out at me; there are doubled quotation marks.I also noticed
or [ProductGroup] = " & strCriteria & " or
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.
ASKER
Paul. I'm stuck. Here's a stripped down database. Can you provide assistance?
Search-Test.accdb
Search-Test.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
You may expand for other fields, by converting each to a string.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the answer is YES!!!