- Joined
- Feb 9, 2015
- Messages
- 5
- Reaction score
- 0
Greetings. I've been looking for a way to create a search form that works as similarly to Filter by Form as possible with as little effort as possible. My database has 37 fields in its master table. All of these fields are represented on a form for adding new records, and any one or combination thereof may be required as criteria. When I searched here, I found a message similar to what I want to do on the Access forums at this site, but they were from 2008. I'm told by folks that responding/replying to such old posts is frowned on at best. So I have copied the code that was included in these old posts in this post. I haven't tried it yet because I have a question regarding fields where people need to be able to select multiple values instead of just typing in one value in order to search.
First the code from the old posts (Name attached to the first, the second one just said "Guest"):
From a post by "Steve":
Well, YOU CAN in fact do what you wanted. I have come up with
something akin to the Filter By Form feature of Access that requires
far less pain than that awful Microsoft example qbf2k.mdb, and as an
alternative to Allen's excellent search form:
1. Open the form you want the filter on (e.g. frmCustomers)
2. Create a button called cmdFind, set to OpenForm frmCustomersSearch
(to be created)
3. Create another button called cmdApplyFilter, set to Enabled.....No
(optional)
4. Click Save As and save a copy of the form, call it
frmCustomersSearch
5. Open the frmCustomersSearch form
6. Set each field Unbound (i.e. set the Control Source for each field
empty)
7. Set cmdApplyFilter to Enabled......Yes, with the following code On
Click:
Private Sub cmdApplyFilter_Click()
Forms!frmCustomers.SetFocus
DoCmd.ApplyFilter , "CustomerName like '*" & Me.CustomerName &
"*'"
DoCmd.Close acForm, "frmCustomersSearch"
End Sub
8. Now try it! Click on the Find button on the frmCustomers form, type
something into the CustomerName field, click on the ApplyFilter
button.
Obviously, we are only searching on CustomerName here but this can now
be easily expanded to include any of the unbound fields (say, any
field that is not null) by adding a little bit of code to the
cmdApplyFilter_Click() routine, and some AfterUpdate event on the
search form to make Enter key the trigger.
Steve
From a second person named only "Guest":
OK, I have come up with the following code for the cmdApplyFilter
button.
I have also set Enabled......No on all the other buttons on the page,
except Cancel. This makes it obvious to the user that they are on the
search form.
Private Sub cmdApplyFilter_Click()
For Each Control In Me.Form.Controls
If Control.ControlType = acTextBox Then
ctlVal = Control.Value
If Not IsNull(ctlVal) Then
Forms!frmSalesOrders.SetFocus
DoCmd.ApplyFilter , "[" & Control.Name & "]" & " like
'*" & ctlVal & "*'"
DoCmd.Close acForm, "frmSalesOrdersSearch", acSaveNo
Exit Sub
End If
End If
Next Control
End Sub
So what does everyone/anyone think?
And my question...I have several fields that need to allow users to check multiple values from list boxes that are set up to allow this option. If I set these fields as Unbound, will this allow folks to perform this multiple select and include their choices in the filter/search or not? And if not, how do I make it happen?
First the code from the old posts (Name attached to the first, the second one just said "Guest"):
From a post by "Steve":
Well, YOU CAN in fact do what you wanted. I have come up with
something akin to the Filter By Form feature of Access that requires
far less pain than that awful Microsoft example qbf2k.mdb, and as an
alternative to Allen's excellent search form:
1. Open the form you want the filter on (e.g. frmCustomers)
2. Create a button called cmdFind, set to OpenForm frmCustomersSearch
(to be created)
3. Create another button called cmdApplyFilter, set to Enabled.....No
(optional)
4. Click Save As and save a copy of the form, call it
frmCustomersSearch
5. Open the frmCustomersSearch form
6. Set each field Unbound (i.e. set the Control Source for each field
empty)
7. Set cmdApplyFilter to Enabled......Yes, with the following code On
Click:
Private Sub cmdApplyFilter_Click()
Forms!frmCustomers.SetFocus
DoCmd.ApplyFilter , "CustomerName like '*" & Me.CustomerName &
"*'"
DoCmd.Close acForm, "frmCustomersSearch"
End Sub
8. Now try it! Click on the Find button on the frmCustomers form, type
something into the CustomerName field, click on the ApplyFilter
button.
Obviously, we are only searching on CustomerName here but this can now
be easily expanded to include any of the unbound fields (say, any
field that is not null) by adding a little bit of code to the
cmdApplyFilter_Click() routine, and some AfterUpdate event on the
search form to make Enter key the trigger.
Steve
From a second person named only "Guest":
OK, I have come up with the following code for the cmdApplyFilter
button.
I have also set Enabled......No on all the other buttons on the page,
except Cancel. This makes it obvious to the user that they are on the
search form.
Private Sub cmdApplyFilter_Click()
For Each Control In Me.Form.Controls
If Control.ControlType = acTextBox Then
ctlVal = Control.Value
If Not IsNull(ctlVal) Then
Forms!frmSalesOrders.SetFocus
DoCmd.ApplyFilter , "[" & Control.Name & "]" & " like
'*" & ctlVal & "*'"
DoCmd.Close acForm, "frmSalesOrdersSearch", acSaveNo
Exit Sub
End If
End If
Next Control
End Sub
So what does everyone/anyone think?
And my question...I have several fields that need to allow users to check multiple values from list boxes that are set up to allow this option. If I set these fields as Unbound, will this allow folks to perform this multiple select and include their choices in the filter/search or not? And if not, how do I make it happen?