1. I suppose that the form is bound to the table (here named 'Shipments') in which the search is to be performed. If it's not the case you'll have to adapt the code.
2. On the form, create a combo box, name it 'cbo_Columns' and assign 'Field List' to its RowSourceType property and the name of the table ('Shipments') to its RowSource property.
3. On the same form, also create a text box (name: 'txt_Search') and a command button (name: 'cmd_Search').
4. You can use this code to perform the search:
Code:
Private Sub cmd_Search_Click()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim strOperator As String
Dim strArgument As String
If (Len(Nz(Me.cbo_Columns.Value, "")) > 0) And (Len(Nz(Me.txt_Search.Value, "")) > 0) Then
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("Shipments")
Select Case tdf.Fields(Me.cbo_Columns.Value).Type
Case dbText, dbMemo
strArgument = "'" & Me.txt_Search.Value & "'"
strOperator = " Like "
Case dbDate
strArgument = "#" & Format(Me.txt_Search.Value, "mm/dd/yyyy") & "#"
strOperator = " = "
Case Else
strArgument = Me.txt_Search.Value
strOperator = " = "
End Select
Set tdf = Nothing
strCriteria = Me.cbo_Columns.Value & strOperator & strArgument
Set rst = Me.RecordsetClone
rst.FindFirst strCriteria
If rst.NoMatch = False Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "No match: " & strCriteria, vbInformation, "Not Found"
End If
rst.Close
Set rst = Nothing
End If
End Sub
dBforums
>
Search & edit function
05-04-11, 10:13
Search & edit function
Linear Mode
