[RESOLVED] VB6 Making A Textbox The "Searchbox" For A "RS.Find" Event

Hi,

I wan't to make a searchbox, where users can search for a unique number in a database (access 2007, saved as older version .mdb). The numbers are held in column 4 (named 'Product Code'). A Cmdbutton (Check) activates the search, and puts the text/data, that is found in the same Record (column 2 & 3) in label1 & label2.

My code about this:

Code:

---

(General)-(Declerations)
Private RS As ADODB.Recordset

(Form)-(Load)
Set RS = New ADODB.Recordset
With RS
  .Open "Product_List", CN, ....
End with


Private Sub cmdCheck_Click()
  ...
  RS.Find ("Product Code = .............")  'Should be like txtSearch.text, i thought:(
  lbl1.Caption = "RS.Fields.Item("Product Name")
  lbl2.Caption = "RS.Fields.Item("Price")
  ...

---

Questions:
1. Do i need to 'RS.Open or Close anywhere? or is that just for writing to a database?
2. How do i declare the Searchbox's text as the 'To-Be-Searched' information?

I've tried the FAQ's but could find it.

Thanks in advance!

If the Product Code is numeric data type then use :

Code:

---

RS.Find ("Product Code = " & txtSearch.text & ")"
If Not RS.EOF Then
  lbl1.Caption = "RS.Fields.Item("Product Name")
  lbl2.Caption = "RS.Fields.Item("Price")
Else
  MsgBox "Product doesn't exists"
End If

---

If it's string :

Code:

---

RS.Find ("Product Code = ' " & txtSearch.text & " ' )"
If Not RS.EOF Then
  lbl1.Caption = "RS.Fields.Item("Product Name")
  lbl2.Caption = "RS.Fields.Item("Price")
Else
  MsgBox "Product doesn't exists"
End If

---

(Never tried field names with blanks, probably you have to use it inside-> [] )
JG

Hi,

thanks for the reply!

The first method is applicable for my program, but:

Code:

---

RS.Find ("Product Code = " & txtSearch.text & ")"

---

Isn't working. the last ) and " should be switched, i think.

now, when i enter the product code that's the first record in the database, everthing is OK. if i type any other one, it gives the message "product doesn't exist", your message.

what's the cause of this?

thanks

In ADO before using a Find you must use the MOVEFIRST method, so the search begins from the BOF, as :

Code:

---

'
'
'
If RS.EOF = False or RS.BOF = False then RS.MoveFirst
RS.Find ("Product Code = " & txtSearch.text & ")"
'
'
'

---

JG

Sweet, another step closer to victory!
thanks a lot!

Thread Solved!