Finding records in access using a vb button
 

ive made a program that links into an access 2000db.
ive written the code for the find button as

Private Sub cmdFind_Click()

 Dim x As String



 x = InputBox("Input the first few characters of the Company Name ", "Find on Name")

 Data1.Recordset.FindFirst "Name like '%" & x & "%'"



cmdFind_ok:

     Exit Sub



cmdFind_Error:

     Dim y As String

     y = "Error " & Err.Number & ": " & Error(Err.Number)



     MsgBox y, 48, "Find On Company Name"



     Resume cmdFind_ok

End Sub

And i keep getting Runtime Error ''
Operation is not supported for this type of object.
And when i debug
Data1.Recordset.FindFirst "Name like '%" & x & "%'"
Is highlighted.
Ive tried using ADO but then i can only display the results in a grid and i use sql then. Why wont this code work? Is it wrong or am i missing a refrence of component?
Thank you

Re: Finding records in access using a vb button
 

You didn't say where you were showing the results, -
to a multi line textbox or datagrid?

TEXT1.TEXT ??

also you cannot use Like command in that fashion [Within ADO]
here's a Sample method using SQL
'----------------------------------
Dim strPrompt As String
Dim strInput As String
Dim strSQL As String
Dim fSearch As FSearchResults

strPrompt = "Enter all or the beginning of the Company name:"
strInput = InputBox$(strPrompt, "Search for Companies")
If strInput <> "" Then
' search
strSQL = "SELECT * FROM database " & _
"WHERE CoName LIKE '" & strInput & "*';"

Set fSearch = New FSearchResults
fSearch.Search "CoName", strSQL, Me
If Not fSearch.Cancelled Then
TEXT1.Text = fSearch.KeyValue '<<< output
End If
End If
'----------------------------------

This should help, you will need to modify variable to fit your need.

aka Kegtapper

Re: Finding records in access using a vb button
 

The results should display to multiple textboxes each linked to a field in the table.
text1.text
text2.text
ect...
What exactly is the fsearch for?
thanks for the help

Re: Finding records in access using a vb button
 

Have you tried it? does that work for you.

fSearch=field search results (see Dim Statement)
(built-in function)

Re: Finding records in access using a vb button
 

I modified it to point at my objects but i keep getting errors on
Dim fSearch As FSearchResults
And then obviously everything that uses fsearch and fsearchresults.
Not sure what values to assign them if i remove all the fsearch stuff it run and throws a promt but it stops there then. Not to sure how the fsearch command words i have never used it and i havent been able to find any examples of how your supposed to use it in context.
thanks

Re: Finding records in access using a vb button
 

Sorry I omitted the cls Items that were needed.
So I created a tutorial project that you can walkthrough to see how to sort when typing.

**** Note Code is word wrapped ********

1 Exe file (completed project compiled)

DB format
ACCESS 97

OCX Controls/References:
MS Common Controls (Sp4) MSCOMCTL.OCX

frm file has:
1 textbox
2 labels
1 Listview
1 Command button (exit/end)
No ERROR TRAPPING (ADD yours at location specified)

bas Module has
2 lines pointing to global Database path
1 path is for design time
1 path is for exe when complete (usually in same dir)

---------- The code that does the work ---------------------

Private Sub FindStr()
Dim dbfTempDialog As Database, recTempDialog As Recordset
Dim strSQL As String
Dim resItem2 As ListItem
ListView.ListItems.Clear
'On Error GoTo LoadErr '<<<TRAP Error Here

Set dbfTempDialog = Workspaces(0).OpenDatabase(DataPath)
strSQL = "SELECT * FROM Company Where CoName LIKE '" & Text1 & "*';" 'Company ='" & Text1.Text & "*';"

Set recTempDialog = dbfTempDialog.OpenRecordset(strSQL)
If recTempDialog.RecordCount Then
recTempDialog.MoveFirst
Do Until recTempDialog.EOF
If Not IsNull(recTempDialog![CoName]) Then
Set resItem2 = ListView.ListItems.Add(, , recTempDialog.Fields("CoName"))
Else
ListView.ListItems.Add ""
End If
recTempDialog.MoveNext
Loop
Else
MsgBox "There are No Companies " & Text1.Text, vbInformation
End If
ListView.Enabled = True

End Sub
'----------------------------------

When a letter is typed in the text1.text box
Private Sub Text1_Change()
FindStr ' jumps to sub routine above
End Sub

The location of the ZIP project
www.kegtapper.net/Tips/CoList.zip