Searching for a Record

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

Here I tried to match a value in an unbound text box 'Text0' to a record in tblmain with field 'Text1.'  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
JermyDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shiju SasidharanAssoc Project ManagerCommented:
hi
change ur recordset opening statement like this

     rs.Open "Select * From tblmain Where Text1='" & Replace(Text0.Text,"'","''")  & "'", cn, adOpenKeyset, adLockOptimistic, adCmdText


;-)
Shiju

0
brianb99999Commented:
Hi Jeremy,

Is it possible to try Seek or Filter instead of FindFirst?
You can use a filter (rs.filter = SQL Statement) but it has almost the same overhead as a open (without the database call).

FindFirst can be followed by FindNext statements within a loop (something simple like setting up a static counter within your procedure):

Static lngTest As Long
FindPN = InputBox("Enter Part Number")
If FindPN = "" Then
    Exit Sub
End If
if lngTest = 0 then
    Data1.Recordset.FindFirst "partno LIKE ' " & FindPN & "%'"
    lngTest = lngTest + 1
else
    'Not to sure about this code - it is something like the following
    Data1.Recordset.FindNext "partno LIKE ' " & FindPN & "%'"
end if
If Data1.Recordset.EOF Then
    MsgBox "No titles in the database match your criteria"
End If

In saying all that - data binding in VB is horrible and hard to maintain/debug, I would use your second solution with shijusn's changes.

Brian.
0
JermyDAuthor Commented:
Hello again,

I've tried both of your suggestions, and still no error, no search executed.  Is there something else that should be set up that I don't know about?  I also have a DataEnvironment for a report, could that be conflicting with this?  I even tried adding an Adodc connection, which works, but still no error, no search.  Is there anything else that could be conflicting with this causing it to not work?

Thanks for all your help.  I'm sure you'd rather be watching football than helping me.
Let me know if you care to share any more insight.

Jeremy
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

anvCommented:
hi JermyD

try this

rs.Filter = "partno LIKE ' " & FindPN & "*'"

now the recordset rs will contain all the records having part no starting with the value entered into the variable FindPN

also if u want to make the search case-insensitive then use this..

rs.Filter = "Lcase(partno) LIKE ' " & Lcase(FindPN) & "*'"
0
anvCommented:
one change that i made as per your code is use * instead of %..

as % is not a wildcard used in Access..Use * instead for Access Database.
0
Stephen MandersonSoftware EngineerCommented:
Hello again Jermy, think i know what you are looking for, yet another example on its way.
0
JermyDAuthor Commented:
You already helped me with this one.  If you'd like, I need help Simulating a DLookup in VB.  What I have is an Adodc link to a table with part numbers in my own Access db.  I need to look up values associated with each part number from a tbl on our network.  I created a second Adodc connection using ODBC to the tbl I needed on our network.  Is there a way to relate the two connections?   I want to view related data in ODBC linked tbl to part numbers in my own Access tbl, say in a Change event.

Can this be done?  Any help would be appreciated.

Thanks

0
Stephen MandersonSoftware EngineerCommented:
The query that you would need to filter would be this one. As for your other question i shall have a look at it.


"SELECT * From tblmain WHERE Text1 = '" & text1.Text & "';"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.