Problem with my search code.

Hi,
I have form with all vehicle info. I want user to search any of vehicle info by key in vehicle_no. This is the code,


Private Sub cmdSearch_Click()
Dim rs As Recordset
Dim db As Connection

Set db = New Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Vehicle.mdb;Persist Security Info=False"

Set rs = New Recordset
rs.Open "select * from tblvehicle", db, adOpenStatic, adLockOptimistic

Let strvehicleno = InputBox("Please enter vehicle no", "Search")
rs.Find "vehicle_no = '" & strvehicleno & "'"

If Not rs.EOF Then
MsgBox "Record Not Found", , "ERROR"
End If
End Sub


The problem is, it will give msg box "Record Not Found" eventhough the vehicle_no is exist in the database.
any idea? thanx in advance..;)

Hi,

try filter and like, should give the same result...

rs.Filter = "[vehicle_no] Like '" & strvehicleno & "*'"

or

rs.Find "[vehicle_no] Like '" & strvehicleno & "*'"

where the * allows multiple endings, and softens the search

Also, check your type and declarations carefully.

good luck

Hi,
I have form with all vehicle info. I want user to search any of vehicle info by key in vehicle_no. This is the code,


Private Sub cmdSearch_Click()
Dim rs As Recordset
Dim db As Connection

Set db = New Connection
db.CursorLocation = adUseClient
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Vehicle.mdb;Persist Security Info=False"

Set rs = New Recordset
rs.Open "select * from tblvehicle", db, adOpenStatic, adLockOptimistic

Let strvehicleno = InputBox("Please enter vehicle no", "Search")
rs.Find "vehicle_no = '" & strvehicleno & "'"

If Not rs.EOF Then
MsgBox "Record Not Found", , "ERROR"
End If
End Sub


The problem is, it will give msg box "Record Not Found" eventhough the vehicle_no is exist in the database.
any idea? thanx in advance..;)

a few comments and questions about your code

1) you should explicitly declared the base class type of your connection and recordset i.e. "ADODB.Recordset"

2) you can remove the Let statement in your code

strvehicleno = InputBox("Please enter vehicle no", "Search")


3) I didn't see where do you define the variable strvehicleno, and do you have an Option Explicit in your code?

4) instead of opening the whole table and search for a specific record afterwards, it's kind of redundant, why don't you ask the user first then open the table according to the user's input i.e.


strvehicleno = InputBox("Please enter vehicle no", "Search")

Set rs = New Recordset
rs.Open "SELECT * FROM tblvehicle WHERE vehicle_no = '" & strvehicleno & "'", db, adOpenStatic, adLockOptimistic


5) Are you sure that the value of strvehicleno is exactly the same value of your vehicle_no? when I say exactly it should have the same length with the same information i.e. "Duane" = "Duane" not "Duane" = "Dua" if this is the case then you need to use the LIKE operator with the Wildcard symbol as nicktangri mentioned, so since you are using ADO I pressume and where going to use the LIKE operator in the SELECT statement not in Find method we need to use the % sign. The code should look like:

Set rs = New Recordset
rs.Open "SELECT * FROM tblvehicle WHERE vehicle_no LIKE '" & strvehicleno & "%'", db, adOpenStatic, adLockOptimistic

Hi awiezzz,

A couple a thoughts..

With these Commands

If Not rs.EOF Then
MsgBox "Record Not Found", , "ERROR"
End If

I believe you'll get the MsgBox when a Match Is Found, rather than "Not Found", as it looks like you want.

Maybe you'd prefer something like this:

If Not rs.EOF Then
MsgBox "Record Found " & rs.Fields(1) ' where (1) is your search field
Else
MsgBox "Record Not Found", , "ERROR"
End If

Also:
I'd declare the following at the top of your code Module

Option Explicit
Dim strvehicleno As String
Private Sub cmdSearch_Click()
' your code here

Plus I'd try dropping your use of "Let" before strvehicleno = InputBox(" ....

If none of that helps, I'd recommend using ADO commands in place of your DAO.

For help with that, check out the nice ADO Tutorial By Member Optikal:
http://www.xtremevbtalk.com/showthread.php?threadid=66994

Edit: Sorry Duane - I didn't mean to duplicate any of what you said. I missed seeing your post :D

Hi awiezzz,

A couple a thoughts..

With these Commands

If Not rs.EOF Then
MsgBox "Record Not Found", , "ERROR"
End If

I believe you'll get the MsgBox when a Match Is Found, rather than "Not Found", as it looks like you want.

Maybe you'd prefer something like this:
[vb]
If Not rs.EOF Then
MsgBox "Record Found " & rs.Fields(1) ' where (1) is your search field
Else
MsgBox "Record Not Found", , "ERROR"
End If


nice catch i didn't spotted that one

u 've to change the code like

Rs.Find , 0, adSearchForward or
Rs.Find , , adSearchForward
just try
Jagans

u 've to change the code like

Rs.Find , 0, adSearchForward or
Rs.Find , , adSearchForward
just try
Jagans

I don't think using the Find method is necessary in his/her code just look at his/her code again he/she opened the table and use the find method after asking the user for "vehicle no" inside the same procedure. Opening the table with the SELECT and WHERE clause with the correct value in strvehicleno should be fine.


Sorry Duane - I didn't mean to duplicate any of what you said. I missed seeing your post


no problem, anyway you have a good point in the "Not rs.EOF" part, very well spotted :)