How do you provide a search function on a form?

[JustMaybe]

Can anybody tell me how to provide a search function in a form, so users can search for either an e.g. invoice number(Primary Key) or by a customer name??

 

[wjoc1]

Not sure how to do this, because you're trying to have one form and two different ways of searching for information.

My way of doing this ( and it may be completely off the mark!) is to have two copies of the form and each is based on an underlying query.

The fields in the two queries are completely identical except for one small difference.

In the criteria cell for the InvoiceNo field of the first query put the following:

[Specify the Invice No]

Every other criteria cell is left blank.

Now in the criteria cell for the CustomerName field in the second query put:

[Specify a Customer name]

Again, every other criteria cell in this query is left blank.

Base a copy of the form on each query and using the wizard create 2 buttons on the main form which opens each of these forms respectively.

When you click the button a dialog box will pop up asking you to input the relevant criteria, either InvoiceNo or CustomerName and the correct information will appear in the form when it opens.

If this is what you're looking for I can go into this in more details. Hope this helps!

Liam

 

[pono1]

J,

To add to Liam's post, as another option: Create a couple of unbound combo boxes on your form using the wizard, choosing "Find rec on form based on the value I selected in my combo box" at the first dialog box...

Regards,
Tim

 

[Rakier]

Another way of doing this is to do the following:

Create an option group with two options (toggles, radio, check, etc...).

The option values should be 1 and 2 when they are created.

Create an unbound text box that will be the search criteria.

Create two queries, one to search for the invoice number based on the search criteria entered in the text box and one to search for the customer name based on the info typed in the text box.

Create a command button that will modify the rowsource on where you wish to display the data found (i.e. form, list box, etc...). If option one is selected, it makes the first query (invoice) be the record source and if the second option is chosen it makes the second (customer) query be the rowsource.

That way the user can choose what they want to search by (either invoice or name) and then enter the criteria to search for.

Code example for the button. Run it on the on-click event.

Select case me.grpOptions

Case 1
With me.lstSearch
.RowSourceType = "Table/Query"
.RowSource = "qryInvoice"
End With
Case 2
With me.lstSearch
.RowSourceType = "Table/Query"
.RowSource = "qryName"
End With

End select

me.grpOptions is the name of your option group.

me.lstSearch is the name of an unbound list box on your form which is where the results of the query will be displayed.


HTH

 

[DES]

Try having a look at the attached sample there is one in there that will give you what you want.

Hmmm guess you should have accepted the offer for help when I had more time available to me a couple of days ago.

DES