Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Searching for a Record

Posted on 2004-11-14
8
Medium Priority
?
556 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:JermyD
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 12579281
hi
change ur recordset opening statement like this

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


;-)
Shiju

0
 
LVL 5

Expert Comment

by:brianb99999
ID: 12579993
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
 

Author Comment

by:JermyD
ID: 12580444
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:anv
ID: 12581746
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
 
LVL 10

Expert Comment

by:anv
ID: 12581748
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
 
LVL 19

Expert Comment

by:Stephen Manderson
ID: 12596432
Hello again Jermy, think i know what you are looking for, yet another example on its way.
0
 

Author Comment

by:JermyD
ID: 12596472
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
 
LVL 19

Accepted Solution

by:
Stephen Manderson earned 200 total points
ID: 12596630
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question