JermyD
asked on
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.OL
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
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.OL
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
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.
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.
ASKER
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
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
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) & "*'"
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) & "*'"
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.
as % is not a wildcard used in Access..Use * instead for Access Database.
Hello again Jermy, think i know what you are looking for, yet another example on its way.
ASKER
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
Can this be done? Any help would be appreciated.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
change ur recordset opening statement like this
rs.Open "Select * From tblmain Where Text1='" & Replace(Text0.Text,"'","''
;-)
Shiju