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.ItemsSele
cted.Count
>0 Then
strFilter = strFilter & "("
For Each varItem In Me!lstFindStatus.ItemsSele
cted
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(strFil
ter)-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.Filte
rOn = False
Else
Me!subCustomers.Form.Filte
r = strFilter
Me!subCustomers.Form.Filte
rOn = 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.ItemsSele
cted.Count
>0 Then
strFilter = strFilter & "("
For Each varItem In Me!lstFindStatus.ItemsSele
cted
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(strFil
ter)-5)
DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
End Sub
