Creating text box in VB6 to search Access Database

[at51178]

Hey guys

I would like to create a text box that is linked to an access database and use it to search for records by typing in the record that I am looking for in another text box and then press search to bring up the record. basically I want to creat a search engine can this be done let me know if it can or can not be done

thanks

 

[zemp]

Yes it can be done. Look into using the 'LIKE' keyword in the SQL statements you will need. Thanks and Good Luck!

zemp

 

[at51178]

Sorry I am kind of new to vb6
but can you elaberate on your answer
I understand on setting up a like statement in sql in access within a query
but I am not sure how to attach it to a text box.

 

[THOMASNG]

I'm doing something very similar. Get the text : ACCESS 2000, by Virginia Anderson (She also has one for ACCESS 2002).
Available from

http://www.amazon.com,

for less than $40.

 

[zemp]

In the search button click event concatenate the text from the text box into your SQL.

"SELECT * FROM Table1 WHERE Field1 LIKE '" & Text1 & "'"

You can even throw in a wildcard character '%'.

"SELECT * FROM Table1 WHERE Field1 LIKE '" & Text1 & "%'"

With the wild card if 'B' is entered in the text box then all values that begin with 'B' will be returned. This would require the results to be shown in some sort of list that the user can selet from. Thanks and Good Luck!

zemp

 

[zemp]

You can also search

http://www.planetsourcecode.com

for some code examples. Thanks and Good Luck!

zemp

 

[rorubin]

I was helping someone else with something similiar before take a look at this thread thread705-463175 In it there is a link to how to use the filter function of a recordset.

 

[CCLINT]

I do not think the filter or a new Select statment is the best method in this case.

I assume you are using a data control as the data source for your data, and all of the the data is already available in a recordset object or Adodc object.

Then, just use the Find method of the recordset object, or better yet, of a cloned recordset object.

Try the following code for a search on a Text field (You need to set a reference to the ADO object library if you do not already have one):

'Dim bkmrk As Variant
Dim rsClone As ADODB.Recordset.Clone
Set rsClone = Adodc1.Recordset

rsClone.MoveFirst
If Not rsClone.BOF Then
rsClone.Find "TheFieldName='" & Text1.Text & "'", , adSearchForward

If rsClone.EOF then
'Action for no (further) records found
'MsgBox "No Records Found"
Else
Adodc1.Recordset.Bookmark = rsClone.BookMark
End If
End If
If Not rsClone.Status Then rsClone.close
Set rsClone = Nothing

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}