Search Forms

A

Anechka

Hi everybody,

I'm trying to build a search form in VBA. What it should basiciall
do is retreive a string from an ImputBox, and return all the record
where it apperas as a word, or as a part of a word.
For example, if the input is do,and one feild of the 2nd record ha
the word undo,and a different feild in the 10th record has the wor
done, I'd have both those records .
So how do I do it in VBA, or maybe SQL or a qry would be better? S
far I tried all three methods and nothing worked.

Thanks a lot
 
M

MartinMCU

If I understand you correctly, this should be a fairly simple SQL query, that
you would implement in the VBA for the form. For the purposes of discussion,
let us assume that your search form has three controls: an input textbox,
tboSearchFor, a command button, cmdDoSearch, and a list box to display the
results, lboResults. Clicking the button will cause the results to display in
the listbox, if the following code is in the Click event for the button:

Private Sub cmdDoSearch_Click()
On Error GoTo cmdDoSearch_Click_Error

Const conQuote As String = """"

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim strWhere As String

' Basic query, will return all records
strSQL = "SELECT * FROM tblSource"
strWhere = ""

' Set up DAO objects
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblSource")

' Loop through all the fields in the table and add to the criteria
' string if appropriate
For Each fld In tdf.Fields
If (fld.Type = dbText) Then
strWhere = strWhere & fld.Name & " Like " & conQuote & "*" _
& tboSearchFor & "*" & conQuote & " OR "
End If
Next

' Append the criteria string to the query, trim off final excess " OR "
If Len(strWhere) > 0 Then
strSQL = strSQL & " WHERE " & Left(strWhere, Len(strWhere) - 4)
End If

' Execute the search
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount > 0 Then
With Me.lboResults
.RowSourceType = "Table/Query"
.RowSource = strSQL
.Enabled = True
.ColumnCount = rst.Fields.Count
.ColumnHeads = True
End With
Else
With Me.lboResults
.RowSourceType = "Value List"
.RowSource = "No Records Found"
.Enabled = False
.ColumnCount = 1
.ColumnHeads = False
End With
End If

cmdDoSearch_Click_Exit:
Exit Sub

cmdDoSearch_Click_Error:
Select Case Err
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume cmdDoSearch_Click_Exit
End Select

End Sub

Hope this helps, and let me know if there are any bugs/questions!

Matt
 
Top