'------------------------------------------------------
'How To: Search a Database for a specific record
'Posted: 06-July-98
'------------------------------------------------------
Q: I am writing a VB5 program to interface with a MS Access 
database...it will add and delete records and scroll through 
all the records currently entered.  I need to be able to add 
a Find function to this application. I tried the FindFirst and 
FindNext methods but...it didn`t work. I need it to display the 
record that matches the given search string. 

A: You want to Search the Given data in a Table...Let me give you an 
example for this. Suppose You want to Search for CustomerName in 
Customer Table of CustomerDetails Database. The text you want to 
search is in "Text1.Text" box.

Dim VSearch as String

VSearch = "CustomerName="+Text1.Text
CustomerDetails.Recordset.FindFirst VSearch 

'This is all you have to do to check that 
'your search is succesful use NoMatch Property
 
CustomerDetails.Recordset.Nomatch = True ' Not Found
Else "Record is Found "

Yogesh

A: The following is from an early program of mine.
Today I use SQL and do not use the data control 
if possible. But this works: 

Dim sBookMk As String, sTmp As String

sBookMk = Data1.Recordset.Bookmark
sTmp = "Name >= " & Chr$(34) & _ txtSeek.Text & Chr$(34) 'Was one line.
Data1.Recordset.FindFirst sTmp
If Data1.Recordset.NoMatch Then
Data1.Recordset.Bookmark = sBookMk
End If

The number one problem with FindNext, etc. methods and SQL is in 
handling the quotes. Note the Chr$(34)`s. They are quotation marks. 
Beyond that, note that the routine cleans up after itself by checking 
for a match. If no match, the data control`s recordset is set back to 
the record that was current prior to the FindNext.

Bob Walsh

A: In an inventory database I have I need to be able to look up items 
by number. This must be done so entering a number of 35 will find 
item 35, but not item 535. In case it would help here is the code 
I use from my access module.

Function Find_Record_By_Item#()
Dim Notice As String
Dim Msg As String
Dim Buttons As Integer
Dim Title As String
Dim Number As String
Dim Number2 As Integer
start:
Msg = "Enter an item number to look up and press enter." _
& Chr(13) & Chr(13) & "To cancel this operation press escape."
Title = "Item Number Look up"
Number = InputBox(Msg, Title)
If Number <> "" Then
DoCmd.FindRecord Number, acEntire, False, acSearchAll, True, acAll, True
Screen.ActiveForm![txtItem_Number].SetFocus
Number2 = Screen.ActiveForm![txtItem_Number].Text
If Number <> Number2 Then
Msg = "The item number you entered was not found." & Chr(13) _
& "Please enter another one."
Title = "Invalid Item Number"
Buttons = 48
Notice = MsgBox(Msg, Buttons, Title)
GoTo start
End If
Else
Screen.ActiveForm![txtItem_Number].SetFocus
End If
If Screen.ActiveForm![txtItem_Number].Locked = True Then
Screen.ActiveForm![txtDate_of_Sale].SetFocus
End If
End Function

The last If allows for immediate data entry after lookup 
on the sales form where the item number, description, and 
asking price fields are looked (They can only be changed 
on the items form.). Chr(13) represents a carriage return.

Lila Godel