'------------------------------------------------------
'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
ustomerDetails.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
Tmp = "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
Downloads
In IE right-click and select 'Save Target As...' or in Netscape
right-click and select 'Save Link As...'
View Plain Text
Not Available
Not Available
