search

[mdelo]

I've tried a few different methods (namely applying a filter), but have had no luck in creating code that actually works the way I want it to.

I'm trying to use a form (I'm not sure if it should be bound to the table I'm using or not) to allow the user to enter search criteria. I want to allow the user to enter the criteria, click a button, and return all records that match the entered criteria on a pop-up form.

Can anyone help? I've spent many hours with no success on this.

 

[LQ]

I would use a form bound to a query rather than a table. Make a query with all the fields that you need and then drag and drop those fields onto your form. In order to allow users to search on specific criteria, you will have to tie a textbox in your form to the criteria in your query. You can do this by going into the query and under the appropriate field, type into the criteria section:

Like[Forms]![Yourformname]![yourtextbox]

Then whatever the user types into the textbox in your form will be used as criteria for the query. If you want to have one form for searching and another for results, then make the search form have an unbound textbox (that will be the textbox you refer to in the criteria section of your query) and have the second form be bound to the query so it can display the results.

Don't know if I have explained this clearly...if you need more clarification, let me know.

LQ

 

[mdelo]

I think I understand what you're saying, but could you elaborate a bit more on how the query will run from the form. Going on what you said I created a form with the necessary fields bound to the query and populated the query criteria fields with the LIKE statement. I'm just not sure how I can get the query to execute and display the results.

 

[mdelo]

Ok. I got the query to execute, but it now list the search criteria as a record in the table. How can avoid this?

 

[LQ]

You will have to use an unbound textbox as your search field. Otherwise, as you point out, you will be adding info to your original table that you based the query on.

HTH

 

[mdelo]

Could you just elaborate a little bit in some more detail about how to do what you describe below. I think I get it, but I'm not completely sure.

"Then whatever the user types into the textbox in your form will be used as criteria for the query. If you want to have one form for searching and another for results, then make the search form have an unbound textbox (that will be the textbox you refer to in the criteria section of your query) and have the second form be bound to the query so it can display the results."

 

[LQ]

There are probably many ways of doing this. I am certainly no expert, but since no one else was answering this question, I thought I'd give it a shot.

You could have two forms (I think you originally mentioned that you wanted to do it this way). The first form would have an unbound textbox on it. The user would type in the criteria for the search in that textbox. Your second form would be based on a query. You would pull the appropriate fields from the query down onto your form. And in the query, under criteria in the particular field that the user wants to search on, you would enter:

Like[Forms]![Yourformname]![yourtextbox]

The textbox and form name are from the first form. On the first form, you would also have to add a command button that would open the second form and close the first form (or else users would not be able to see the results of their search).

Another way you could do it would be on one form, with an unbound textbox at the top where users would input search criteria, and a subform below that would display the results of the search.

If you are still having problems, let me know and I can email you a db that I made that has several samples of search forms. That might give you some ideas.

LQ

 

[accesswatch]

I've got a sample database with commented code which shows a number of search and filtering techniques.
Check it out here http://www.sparkdb.co.uk/html/download10.html
It's the third download (bottom of the page)
It uses both Search techniques and Filtering.

Hope it helps
Regards Trevor from http://www.sparkdb.co.uk

 

[mdelo]

I was unable to download the .exe file. Something appeared to be wrong with the server.

 

[mdelo]

I'm slowly making progress on this question using your suggestions and my own ideas. Here's where I'm at:

I have the following on a form:

5 text boxes named (TXTFname, TXTonwer, TXTprodsrv, TXTdevsrv, & TXTstatus)

4 check boxes named (CHKfp, CHKv, CHKrf, CHKid)

A query uses what is entered on the form as its criteria with the following statement in the criteria field:

forms!formname!TXTcontrolname.text or .value(?) for the check boxes

I need the query to show all records that match the criteria entered on the form. For example show all records that meet the following criteria:

*any fname
*any owner
*any prodsrv
*any devsrv
*any of the chk boxes
AND
*what ever status is entered

I can't seem to figure this out, i.e. I can't even get the query to show me all of the records by entering * in the text fields.

Do I need to change the Jet SQL in query?

I hope I've explained this well, it seems very confusing when I try and write it out.

 

[shacket]

Do you want your users to only search one field at a time, or can they choose more than one?

If it is one at a time, I put my results into an unbound ListBox on the form by dynamically changing the RowSource for the ListBox. They choose which field they want to search (in a drop down) and then type in the criteria. Upon clicking the command button, the code changes the ListBox RowSource to:

Select * FROM tblAnyTable WHERE " & Me.cboFieldToSearch & " LIKE '" & Me.txtCriteria & "'"

Then refresh the ListBox.

To incorporate check boxes (Yes/No), I would have two "halves" of the search form with a different command button for each search (text field, or yes/no field). The Yes/No code would look like:

"Select * FROM tblAnyTable WHERE " & Me.cboYesNoToSearch & " = True"

To search more than one field would be the same idea, but creating the SELECT statement would be a little more complex.

 

[mdelo]

I am looking to be able to search more than one field. I'm looking for some help on the SEARCH statement.

 

[LQ]

Under criteria in your query, try putting something like

Like "*" & [Forms]![YourFormName]![txtbox] & "*"

This will allow you to use partial words or wildcards in your search.

Also, you said that your query criteria looked something like

forms!formname!TXTcontrolname.text

I don't think you should include ".text" after the name of your text box.

 

[mdelo]

Still making progress....

There 4 check boxes named (CHKfp, CHKv, CHKrf, CHKid) that indicate something called "webtype". Only one of them can be selected per record, but I need to be able to return results that are not limited by which box is checked.

Does anyone know of a wildcard that could be entered into the criteria field if none of the check boxes are selected so it would return all records regardless of "webtype"?

 

[shacket]

If you don't specify criteria for a field, all results will show up. So as long as you don't tell it anything about the webtype, you will get all matching records regardless of web type.

Just as an FYI: if only one of the four check boxes can be checked for any record, an option grouped linked to ONE field is a better format in which to store the data, rather than 4 Yes/No fields.

 

[OliviaS]

Option Compare Database

Private Sub btnRunQuery_Click()
On Error GoTo Err_btnRunQuery_Click


Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant

Set MyDatabase = CurrentDb()
On Error Resume Next
' Delete the existing dynamic query; trap the error if the query does
' not exist.
'If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
On Error GoTo 0
'End If
' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].

where = Null
'where = where & " AND [Productno]= '" + Me![Productno] + "'"
where = where & " AND [Productno] Like '" + Me![Productno] + "*" + "'"
'where = where & " AND [Denomination]= '" + Me![Denomination] + "'"
where = where & " AND [Denomination] Like '" + Me![Denomination] + "*" + "'"
'where = where & " AND [Supplier]= '" + Me![Supplier] + "'"
where = where & " AND [Supplier] Like '" + Me![Supplier] + "*" + "'"


'NOTE: In Microsoft Access 97, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.

' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
'If Left(Me![Productno], 1) = "*" Or Right(Me![Productno], 1) = "*" Then
'where = where & " AND [Denomination] like '" + Me![Productno] + "'"
'Else
'where = where & " AND [Productno] = '" + Me![Productno] + "'"
'End If

' Note the number signs (#) surrounding the date field [Order Date].

' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
' Select * from Orders where AND [CustomerID] = 'CACTU'

Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select ProductHyper,Alteration,Denomination,Kit,Supplier,Date from Productnr1 " & (" where " + Mid(where, 6) & ";"))

DoCmd.OpenQuery "qryDynamic_QBF"


Exit_btnRunQuery_Click:
Exit Sub

Err_btnRunQuery_Click:
MsgBox Err.Description
Resume Exit_btnRunQuery_Click

End Sub

Private Sub btnRunQuery_Enter()

End Sub

 

[mdelo]

Thanks for the help all. Got it working just right last week.