Searching question

[Matt Brown]

What is the best method as regards searching table data through a form?
I have been looking at the archives on this subject and there seems to be different ways of doing this.

In my db i do it by several different ways:

1.Using the built in search code

2.Create a query and base the form on that query, create another form with an unbound text box and a Do.Cmd open blah de blah code on the on_click event on the button, refer to the unbound text box on the query column that you are searching so when a search string is entered in the text box and the search button pressed the the form only displays the filtered criteria.

3.Use Filters.(queries)

The second method works well but is it possible to search all fields in a table for matching criteria from a text box on the same form?
Any VBcode examples would be much appreciated.

i.e.

Create text box on main form with command button, when command button is pressed searches all text boxes on same form and filters the results out onto the same form.

Thanks

Matt

 

[Mile-O]

It depends on what sort of search you are wanting to do.

Are you wanting to search a recordset for one record?
Are you wanting to return all records that meet a certain criteria?
Another search?

 

[Matt Brown]

Yes i am wanting to return all records that meet a certain criteria.

Basically what i want to do is to allow the user to search a complete table via say the main form by entering a search word into a text box on the main form.

A little similar to the built in search that access has where the user can search any field that is displayed on a form.

Is this possible if the form already has a query for its record source?

 

[Mile-O]

Have a look at this thread.

 

[Matt Brown]

Hi Yes i have seen this thread.

I did try it out and it works fine but..

instead of loading the listbox up with data can the search results be loaded into the main form, so, say after searching there are 20 records which match the criteraia and the user clicks through the 20 records via the form?

i.e.

With lstResults <--------can the be changed to ref a form
.RowSource = "SELECT * FROM tbCustomer WHERE [YourSearchField] Like '*" & Me.txtFilter & "*';"
.ReQuery
End With

Matt

 

[Mile-O]

You could change the form's RecordSource to the SQL statement generated as opposed to the listbox's RowSource.

 

[Matt Brown]

Yes that would be fine, but would that be similar to the way i do it in the second option that i listed?

 

[Matt Brown]

Just been quietly getting on with my little task here and have managed to sort of do what i am looking for by creating a search results form using the following sql:

SELECT tblInventory.[ASSET ID], tblInventory.[SERIAL NO HIDDEN], tblInventory.[QTY SUFFIX], tblInventory.DESCRIPTION, tblInventory.[ASSET SUPPLIER], tblInventory.MANUFACTURER, tblInventory.[MANUFACTURERS PART NO], tblInventory.[SERIAL No], tblInventory.[CTL LOCATION], tblInventory.[DATE WHEN PURCHASED], tblInventory.[CTL TOOL NUMBER], tblInventory.[MAINTENANCE SERVICE], tblInventory.[CRIMP TOOL DATA AVAILABLE], tblInventory.[DATE OF ENTRY], tblInventory.[OPERATOR NAME], tblInventory.[AUDIT BOX], tblInventory.[CTL SERVICE DATE], tblInventory.[CONTRACTOR SERVICE DATE], tblInventory.[TOOL PREFIX], tblInventory.[GROUP LOCATION], tblInventory.[AB COLLECTION NUMBER], tblInventory.[MAINTENANCE SERVICE DATE], tblInventory.[JOB ALLOCATED TO], tblInventory.[OH TRACKING NUMBER], tblInventory.[PURCHASE PRICE], tblInventory.[DEPRECIATION TYPE], tblInventory.[DEPRECIATION LIFE], tblInventory.[CURRENT DEPRECIATION VALUE], *
FROM tblInventory
WHERE ((((tblInventory.[ASSET ID]) Like [Forms]![frmCcsEditInventory]![txtSearch] & '*') OR (tblInventory.[MANUFACTURER]) Like [Forms]![frmCcsEditInventory]![txtSearch] & '*') OR (tblInventory.[ASSET SUPPLIER]) Like [Forms]![frmCcsEditInventory]![txtSearch] & '*');

The sql searches the fields fine after the "WHERE" clause.

On doing all of this an idea entered my head...

What if i could select what field i wanted to search via an option group.

i.e.

Enter search string in text box, select a text box to search on the form via option group.

Is this possible and how would i integrate it into the sql?

cheers

Matt