Search by name !!!

[Chris]

Hi Graham,
Hope this is what you looking for...

Let assume the Field name for the datathat you wish to serch in database is "EmployeeName".

So the coding will be...


Option Explicit
Dim xDb As DAO.Database
Dim xRs As DAO.Recordset
Dim xData As String
Dim xSQL As String
Private Sub Form_Load()
Set xDb = DBEngine.OpenDatabase(App.Path & "\db1.mdb", False, False)
xData = InputBox("Enter A Name To Search For", "NAME SEARCH", "")
If xData = "" Then Exit Sub
xSQL = "SELECT * FROM TblEmployee WHERE EmployeeName LIKE '*" & xData & "*';"
Set xRs = xDb.OpenRecordset(xSQL, dbOpenSnapshot)
With xRs
If .RecordCount <> 0 Then
While Not .EOF
Debug.Print .Fields(0)
.MoveNext
Wend
End If
End With
xRs.Close
xDb.Close
Set xRs = Nothing
Set xDb = Nothing
End Sub


If you don't want the duplicate record being selected than you need to change the SQL statement to below:


xSQL = "SELECT DISTINCT EmployeeName FROM TblEmployee WHERE EmployeeName LIKE '*" & xData & "*';"


Good Luck :)

[GRAHAM]

Thanks for responding Chris,

The actual code that I,m using is almost identical to yours,
but my problem actually lies within the If statement. My records are displayed in 10 text boxes connected to Data1 because this is what was wanted(I would have preferred a DBGrid system). I made life hard for myself by not using an array of textboxes, so I'll have to live with that mistake now.

What I need is something like

If......(result)....Then
Data1...display (result) in textboxes

rather than debug print, if you see what I mean

I need Data1 to move to that particular record, and I've tried countless lines without success.

Thanks again
GRAHAM :)

[GRAHAM]

Its ok Chris, I've sussed it

Data1.RecordSource = "SELECT etc etc
Data1.Refresh

Thanks again
GRAHAM :D

[GRAHAM]

As I can see quite a few people have looked at this post, I thought I,d post the code I eventually used (my original code worked, but was too long and ugly)

Simple Code:

Private Sub cmdSearchByName_Click()

Dim search As String
Dim prompt As String
Dim title As String

'Define an input box search
prompt = "Enter a name to search for"
title = "NAME SEARCH"
employee = InputBox(prompt, title)
search = "Name = '" + employee + "'"

'Find the record
Data1.Recordset.FindFirst (search)

'If the name cannot be found, tell user
If Data1.Recordset.NoMatch Then
MsgBox "No record was found for " + employee, 32, "NAME SEARCH"
End If

End Sub

Hoping someone finds this useful
GRAHAM ;)