Simple Search

[spencern]

Hello,
I'm working on a database and I would like one of the forms to have a text box that will search through all of the last names in one table. It seems like it should be simple. I'd like to stay away from the built-in Find dialog since it's not as easy as just entering a name on the form.

I've looked through some past posts and the only one I could find was just searching on part of a word. I might want to do that later, but for now I'd like to stick to basic one word searches.

Anyone have a reference about doing this, or any code they wouldn't mind sharing?

Thanks a lot,
Spencer

 

[Rolliee]

This one checks the word in the txtbox and adds it to the database if it is not there already. You could adapt it.

Private Sub txtCity_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset ' reference for DAO 3.6 Object set must be set, I like DAO. Cud use ADO (2000's default)
Dim foundit As Boolean

foundit = False
Set rs = Me.RecordsetClone ' furnsihes program with a clone of the records of the form

If rs.EOF And rs.BOF Then Exit Sub ' go home if no records - you should do something fancier here.

rs.MoveLast ' populate the recordset
rs.MoveFirst ' obvious
Do While Not rs.EOF
'MsgBox "old city " & rs![City] & " New City " & Me.txtCity
If Me.txtCity = rs![City] Then
MsgBox "This one is in the database now"
foundit = True
Exit Do
End If
rs.MoveNext
Loop
If Not foundit Then
DoCmd.GoToRecord , , acNewRec
Me.City = Me.txtCity
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
End Sub

Rollie@bwsys.net

 

[fneily]

On your form with the tables info, you could create a textbox. For my example, it's named Findit. On the AfterUpdate event, type:

Private Sub Findit_AfterUpdate
Dim R As Recordset
Set R = Me.RecordsetClone
R.FindFirst "[LastName] = " & Char(34) & Me![Findit] & Char(34)
Me.BookMark = R.BookMark
End Sub

Char(34) means " (I got fancy)

Neil

 

[BillPower]

Hi spencern,

This is almost identical to fneily's good example and take notice of Rolliee's tip on DAO and ADO in their good example.

Change txtCriteria to the name of your find text box.
Change LName to to you last name control. Add a Command Button called comFind, Caption "Find First"

Paste this into the Declarations Section of your form:

Dim rst As DAO.Recordset, strCriteria As String, strLName As String
Dim intCounter As Integer

Paste this into the Click event of the Command Button:

On Error GoTo Err_Criteria
strLName = Me!txtCriteria
intCounter = intCounter + 1
strCriteria = "[LName] = '" & strLName & "'" 'Use this if the last name is in a field of it's own
'strCriteria = "[LName] Like '*" & strLName & "'" 'Use this if the last name is at end of the field
'strCriteria = "[LName] Like '" & strLName & "*'" 'Use this if the last name is at beginning of the field
Set rst = Me.RecordsetClone
If intCounter = 1 Then
rst.FindFirst strCriteria
Else
rst.FindNext strCriteria
End If
If rst.NoMatch Then
If intCounter > 0 Then
MsgBox "Already at last record for: " & strLName & vbCrLf & _
(intCounter - 1) & " records found"
Else
MsgBox "No records found for: " & strLName
End If
intCounter = 0
Me!comFind.Caption = "Find First"
Else
Me.Bookmark = rst.Bookmark
Me!comFind.Caption = "Find Next"
End If
Exit Sub
Err_Criteria:
intCounter = 0

Paste this into the Key Up event of txtCriteria:

Me!comFind.Caption = "Find First"
intCounter = 0

Each time the user clicks the command button, the next instance of the last name will be found until the last instance is found then a message box appears saying how many times the last name was found or not found. Obviously edit this to your own needs.

Bill