Searching for a Record

[JSD1976]

Hello

I am working with VB6 linked to an Access database via the Data button control. I can view the records and skip through them with the arrows on the Data control, so surely there must be an easy way to search the recordset. I have tried to use this:

FindPN = InputBox("Enter Part Number")
If FindPN = "" Then
Exit Sub
End If
Data1.Recordset.FindFirst "partno LIKE ' " & FindPN & "%'"
If Data1.Recordset.EOF Then
MsgBox "No titles in the database match your criteria"
End If

This works, but it only finds the first record (of course - thought I'd get away with that). Is it possible to try Seek or Filter instead of FindFirst? How about making the value entered in the Input Box the first record?

I've also tried (in another form):
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jsdegard\Desktop\PieceWeight.mdb;User Id=admin;Password=;"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * From tblmain Where Text1='" & Text0.Text & "'", cn, adOpenKeyset, adLockOptimistic, -1

I get an error 'no value given for one or more required parameters.' Frankly, I've twisted this method in so many ways to no avail that I've about given up on it. But If you see something I don't let me know. Otherwise, the first set of code in this post has been more friendly -I'd like to try to make that work. Any response to my situation would be more than helpful.

Thanks in advance.

Jeremy

 

[Swi]

Is Text1 the field name in the table. In your example above it looks as if it is called partno. If this is the case something like this should work.

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\jsdegard\Desktop\PieceWeight.mdb;User Id=admin;Password=;"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "Select * From tblmain Where partno = '" & Text0.Text & "'", cn, adOpenKeyset, adLockOptimistic, -1

If rs.BOF And rs.EOF Then
MsgBox "No records match the current criteria!", vbCritical
Else
MsgBox "Record Found!", vbInformation
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Swi