Search Form: multiple controls (Access 2003)

[lightning]

Hi all,

I'm not very conversant with the vocabulary of Access, so please ask for clarification if necessary...

I am trying to build a search form that allows combinations of search terms. For example, let's say I have three controls on my form; year, keyword, location. Entering a valid value for all three gives expected results from the dataset. However, I'd like to interpret a blank field in the search form as ignoring that constraint. The user should be able to enter values into any combination of form fields, e.g., entering only a year returns all records associated with that year, irrespective of any values in the other two fields. Entering a value in one of the other two fields refines the search result.

I've seen forms like this one:
http://www.mvps.org/access/forms/frm0045.htm

This looks like a great utility, but I can't get it to work in my db... launching the form populates the list of objects control, but clicking any button or object name yields a 'Variable not defined' error. I don't know enough about VB/A to troubleshoot this.

Other solutions I've seen around look very close, but require more understanding of the scripts than I have currently. Here is the query I'm starting with:

SELECT tblCards01.serialNumber, tblCards01.barcode, tblCards01.cardTitle, tblCards01.cardKeywords, tblCards01.cardYear, tblCards01.locCity, tblCards01.locState, tblCards01.cardStory, tblCards01.luOrientation, tblCards01.luDimensions
FROM tblCards01
WHERE (((tblCards01.cardKeywords) Like "*" & Forms!frmQryKeyword!qryKeyword & "*") And ((tblCards01.luOrientation)=(Forms!frmQryKeyword!qryOrientation)) And ((tblCards01.cardYear)=(Forms!frmQryKeyword!qryYear)));

Clearly this is a simple Logical AND setup, so I understand it requires a value to search on for each criterion. Not entering a value is interpreted literally - the query is looking for null values in the field.

So, I'm wondering 1) if there's something relatively simple I'm missing in setting up the query to interpret no value as meaning 'ignore this parameter', 2) is my approach all wrong, 3) does anyone know of a way to get the linked form to work, or of a competing utility that will provide similar functionality.

I do some scripting in other languages, but have precious little experience w/ VB. I'm willing to do some homework here, but I've got lots of other irons in the fire, and don't want to take a couple of courses to solve this issue. Having said that, I do appreciate any insights even if they include 'take a class' :)

Thanks!

-Scott

[christinamasalha]

Dear Scott,

i used this code to create a search form on an application of mine, and it works great..... it will find the results to any number of criteria. All you need to do is customize the code to fit your application.


PLease fins below the article for the search from (i have copied the whole article for you so you can better understand the code before using it):

Experts, please don't answer this Q, this is being posted as a general tutorial question, but please feel free to add your comments and suggest any additions!

Q. We want to create a form for users to look up records in our table. Our table is called Customers and looks like this:
CusID (Autonumber field)
LastName (Text field)
FirstName (Text field)
Age (Number)
Address (Text Field)
Status (Text field, and it will only ever contain "Active", "Inactive" or "Prospect")

A. We have a form called SearchForm. It has 5 controls:
* Two combo boxes called cboFindFirstName and cboFindLastName. These show all the unique first names from the customers table, and their RowSource properties are set to SQL like this: SELECT DISTINCT FirstName FROM Customers. We want the search form to find all records matching what We choose from these combo boxes.

* A text box called txtFindAge. We want to find everyone older than the number we put in here.

* An text box called txtFindAddress. We want to find addresses that contain any text we put in here. So if we put in "19 East", we want it to find "19 East Road" and "19 East Avenue"

* A multiselect list box called lstFindStatus which shows "Active", "Inactive" or "Prospect". We want to find records matching all the statuses we pick from this box. So if we choose both Active and Inactive, we want to find both active and inactive customers.
If we leave any of these controls blank or we don't choose anything, we don't want it to search on that field.

One way of doing this is described here.

In order to achieve the above, we have to create a filtering string. We have a command button on the form, "Find records", called cmdFindRecords.

We need to put code in the OnClick event of this button. To do this, click on the command button in design view, and then click View->Properties on the menus. This brings up the Properties window. Click on the Event page, and click in the "On Click" row. A build button with "..." on it appears. Click on that button, and choose Code Builder.

A window with:
Private Sub cmdFindRecords_Click()

End Sub
appears. All our code goes in between those two lines.

First of all, we create a string variable to hold our filtering string, and set it to an empty string. We also need a variable to represent the items we've selected from the list box:
---
Dim strFilter As String
Dim varItem As Variant

strFilter=""
---

Now we check to see if the combo boxes are empty. If they aren't, we add them to the filter string:
---
If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
---
Using Me!cboFindFirstName and Me!cboFindLastName gives us the values we've chosen in those combo boxes. We first check to see if they are empty by using the IsNull function (which gives us back True if they are). Because FirstName and LastName are text fields, we put quote characters around them in the string (this is what Chr(34) is). We could use apostrophes (') but we might have names like O'Sullivan, which would cause the filter string to be wrong so we use quotes instead.

Now we check if anything has been entered into the txtAge text box.
---
If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
---
Because Age is a number field, we don't need to put apostrophes or quote marks around the value.

The next thing to check is the address. We want to match lines containing what we've typed not exact matches, so we have to use the Like operator which lets us use wildcards:
---
If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
---

The final thing to check is the multiselect list box. This is trickier because the user might have chosen more than one item. So we have to loop through the list box's items to see which ones have been selected:
---
if Me!lstFindStatus.ItemsSelected.Count>0 Then
   strFilter = strFilter & "("
   For Each varItem In Me!lstFindStatus.ItemsSelected
      strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
   Next
   strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
End If
---

We begin the above code checking to see if anything has been selected, We then loop through each selected item and add it to the filtering string. We use OR here rather than AND because we want to find records that have a status of say, Active OR have a status of Inactive. After the loop finishes, we have a stray OR at the end, so we take that off and put a close bracket on the end.

Finally, we have to check to see if our filter string has anything in it at all (because someone might have clicked on Find Records but not typed anything in or chosen anything from the combos and listbox. If they've chosen or typed anything, our filter string will have a stray AND, so we have to take that off.
---
If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
---
So if the user has chosen "Smith" in the last name combo, "Joe" in the first name combo, typed "20" in the age box, "Main" in the address box and chosen "Active" and "Inactive" from the list box, our filter string looks like this:
[FirstName]="Joe" AND [LastName]="Smith" AND [Age]>20 AND [Address] Like "*Main*" AND ([Status]="Active" OR [Status]="Inactive")

Now that we have our filtering string, what do we do with it ?

We can use it to open another form which shows all of the records from the Customers table. For example:
---
DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
---
This opens frmCustomerDetails (which is based on the Customers table and would normally shows all customers) and applies the filter to only show records matching our criteria.

Or instead, we might have a subform inside our search form which shows all the customer details in a tabular format (a continuous or datasheet subform). This subform might be called frmSubCustomerDetails, and is held inside a subform control called subCustomers. We can use our filter string to set a  filter for the subform:
---
If strFilter="" Then
   Me!subCustomers.Form.FilterOn = False
Else
   Me!subCustomers.Form.Filter = strFilter
   Me!subCustomers.Form.FilterOn = True
End If
---
This checks to see if our filter is empty. If it is, we just switch any existing filter on our subform off. Otherwise, we set the subform's filter to our string, and then switch the filter on.

Our completed code looks like this:
===================================

Private Sub cmdFindRecords_Click()
Dim strFilter As String
Dim varItem As Variant

strFilter=""

If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
if Me!lstFindStatus.ItemsSelected.Count>0 Then
   strFilter = strFilter & "("
   For Each varItem In Me!lstFindStatus.ItemsSelected
      strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
   Next
   strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
End If

If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
End Sub

Hope this helped,
Christina

[MMcCarthy]

Hi Scott

Check out this tutorial on Example Filtering on a Form

Mary

[lightning]

Wow!

Thanks to both of you for great replies. I'm going to spend some time digesting these so I can find the right solution for me.

Christina - I've taken the liberty of beginning to convert that article to a PDF for easy reference. If you'd like it, let me know and I'll post it somewhere for you. It's only got basic formatting for the moment, but I can do some doctoring if you like.

Mary - I presume that thread you noted combines a query result with filtering? I'm not sure how to approach that method since I'm looking for a way to avoid a hierarchy in my query. Essentially, I need to leave out any search criteria that are not explicitly called for. Am I misunderstandin g the nature of that thread?

[MMcCarthy]

Mary - I presume that thread you noted combines a query result with filtering? I'm not sure how to approach that method since I'm looking for a way to avoid a hierarchy in my query. Essentially, I need to leave out any search criteria that are not explicitly called for. Am I misunderstandin g the nature of that thread?

This kind of filtering usually work on the recordsource of the opening form.

Presumably you want your results in a form or datasheet. All you do is set up the form or datasheet to return all records without criteria. Then use the tutorial to set filtering criteria and open your form based on this criteria.

DoCmd.OpenForm "FormName", , , "Criteria"

Mary

[lightning]

Presumably you want your results in a form or datasheet. All you do is set up the form or datasheet to return all records without criteria. Then use the tutorial to set filtering criteria and open your form based on this criteria.

Gotcha.

Ok, I'm working through this approach, and have started with just a single search for a keyword. I have done this with a simple query, but am having trouble with this part of the script:

If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" Chr(34) & Me!txtFindTitle & Chr(34) & " AND "

The error I get says "Compile Error. Expected: end of statement" and the Chr(34) is highlighted. Does this have something to do with the VB version, or did I just type something incorrectly?

[MMcCarthy]

Gotcha.

Ok, I'm working through this approach, and have started with just a single search for a keyword. I have done this with a simple query, but am having trouble with this part of the script:

If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" Chr(34) & Me!txtFindTitle & Chr(34) & " AND "

The error I get says "Compile Error. Expected: end of statement" and the Chr(34) is highlighted. Does this have something to do with the VB version, or did I just type something incorrectly?

You didn't use the & before the first Chr(34). Just use the single quotes, it's easier:

If Not IsNull(Me!txtFindTitle) Then 
  strFilter = strFilter & "[cardTitle]='" & Me!txtFindTitle & "' AND "

Also don't forget to remove the last "AND" in the build.

Mary

[lightning]

You didn't use the & before the first Chr(34).

Thanks again, Mary. I added &, but also declared a variable for Chr$(34) which seems to make everything happy.

For reference, here's what I ended up with:

Private Sub cmdFindRecords_Click()

Dim strFilter As String
Dim strQt As String

strFilter = ""
strQt = Chr$(34)

If Not IsNull(Me!txtFindTitle) Then strFilter = strFilter & "[cardTitle]=" & strQt & "*" & Me!txtFindTitle & "*" & strQt & " AND "
If Not IsNull(Me!txtFindKeyword) Then strFilter = strFilter & "[cardKeywords]Like" & strQt & "*" & Me!txtFindKeyword & "*" & strQt & " AND "
If Not IsNull(Me!cboOrientation) Then strFilter = strFilter & "[luOrientation]=" & strQt & Me!cboOrientation & strQt & " AND "
If Not IsNull(Me!cboFindYear) Then strFilter = strFilter & "[cardYear]=" & Me!cboFindYear

'End If

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
DoCmd.OpenForm "frmSearchResults", , , strFilter

End Sub

This search allows for keywords (from a memo field), title search, and selection based on distinct entries (image orientation and year of publication). The purpose of this db is to cull images for printing from a template, so I needed a variety of selection options, but also to restrict possible combinations that are incompatible (two orientations, for example, because each orientation uses a different template). Possible enhancements to this are allowing multiple years, and adding more keywords - right now, you can only enter a single search term in each of the title and keyword boxes.

Thanks very much for all the help!

My next step is to export the results to a tab-delimited text file, which I'll ask about in a new thread.

-A very appreciative n00b (Scott)

[MMcCarthy]

Thanks for posting the final solution.

When you get into dealing with multiple values you can use multi select listboxes and access them for the build in a for...loop as per this example.

Mary

[NeoPa]

A hijack post has been split across to Search Form. Interested parties can follow the progress there.