What's wrong with my "searching" code?

[nor_d83]

Hello everybody...
I'have bound a database to my form, and it displays the record of some fields from my database. I want to make the displayed record so that it can be searched by clicking the "find" button by the related searching categories. Below are coding that i've used..

[Private Sub Command2_Click()
prompt$ = "Sila masukkan nombor rujukan permohonan yang berkenaan."
'get string to be used in the Nombor Rujukan field search
SearchStr$ = InputBox(prompt$, "Mencari rekod berdasarkan nombor rujukan")
Data1.Recordset.Index = "ReferenceNumb" 'use reference numb database field
Data1.Recordset.Seek "=", SearchStr$ 'and search
If Data1.Recordset.NoMatch Then 'if no match
Data1.Recordset.MoveFirst 'go to first record
End If
End Sub]

[Private Sub Command2_Click()
prompt$ = "Sila masukkan nombor rujukan permohonan yang berkenaan."
'get string to be used in the Nombor Rujukan field search
SearchStr$ = InputBox(prompt$, "Mencari rekod berdasarkan nombor rujukan")
Data1.Recordset.Index = "ReferenceNumb" 'use reference numb database field
Data1.Recordset.Seek "=", SearchStr$ 'and search
If Data1.Recordset.NoMatch Then 'if no match
Data1.Recordset.MoveFirst 'go to first record
End If
End Sub]

[Private Sub Command4_Click()
prompt$ = "Sila masukkan status permohonan berkenaan. Samada Approved atau Not approved"
'get string to be used in the status field search
SearchStr$ = InputBox(prompt$, "Mencari rekod berdasarkan status permohonan")
Data1.Recordset.Index = "Status" 'use status database field
Data1.Recordset.Seek "=", SearchStr$ 'and search
If Data1.Recordset.NoMatch Then 'if no match
Data1.Recordset.MoveFirst 'go to first record
End If
End Sub]

Sorry for using some Malay language words in the above coding. I hope anybody experts in this field could kindly plz tell me, what's wrong with the coding, since i got the coding from a Microsoft Press book. (Microsoft VB6 Professional Step By Step, 2nd Edition).

For your information, i'm using VB6.0 with service pack 6.0, and create the database using Microsoft Access 2003.

[QVeen72]

Hi,

If the field you are trying to SEEK is a String, then you have to wrap the search string with single quotes.. Also, SEEK works if the Recordset is open in TABLE mode, and INDEX has been defined..

Data1.Recordset.Seek "=", "'" & SearchStr$ & "'"

Regards
Veena

[nor_d83]

Thanx for your reply.. Could you plz tell me how to define the INDEX, and what is it actually? plz...

[nor_d83]

So far, i had used [Data1.Recordset.Seek "=", "'" & SearchStr$ & "'"] as recommended. And then, the recordset was and is in TABLE mode.. But, the result is still the same error.. Which is 'Run-time error 3800'. '..is not an index in this table'..
The problem is, could u plz guide me how to define the index.. Plz... My teacher want me to solve this by myself..

[Juanon]

You have to go to visual data manager assuming your using vb6.0, its on the add-ins menu, then open your database, right click on the table you need and select design, on the bottom left corner you will see the index fields, click add index, next a window will pop-up and you just have to select the categories on which you wish to make your searches on, be sure to leave the check boxes unselected click ok and your done.

[nor_d83]

Thanx lot Juanon for the knowledges given to me.. I tried it... But VisData cannot even display the database. It said,"Unrecognized database format. Number 3343, Display the data access errors collection?" For your information, i've made the database (a very simple one) using Access 2003. Or is it incompatible with VB6.0? Plz sumbody help me..

p/s: Here i attach my database in zip file.. It';s very simple one..

[Juanon]

Create the database from VisData not from access

[QVeen72]

Hi,

Index should be the Index name and not the Fieldname..

Data1.Recordset.Index = "indStatus"

Regards
Veena

[nor_d83]

Both of u are great VB teachers! Thanx lot for your generously help and guides. May God bless u always...