In any form, there are usually only a few fields that the user is likely to
filter by. Some of these could be a range (such as a month or quarter.) What
I do is to place unbound controls of a different color in the Form Header
section, so the user can enter any combination of controls, and then click
the command button to filter them.
The button's Click event procedure builds a string from the non-null boxes,
and applies it to the Filter of the form. The filter string looks exactly
like the WHERE clause of a query, so you can mock up a query with any old
values and then switch it to SQL View (View menu) to see an example of what
you need to create.
The example below is structured so that it is very easy to add as many
filter controls as you need, tacking the AND onto the end of each one, and
removing the trailing AND at the end. It illustrates how to use the
delimiters for text and date fields, and to ensure the date is interpreted
correctly regardless of the user's regional settings.
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"
If Me.Dirty Then Me.Dirty = False 'Save first
If Not IsNull(Me.cboFilterCompanyID) Then 'Number field example.
strWhere = strWhere & "([CompanyID] = " & Me.cboFilterCompanyID & ")
AND "
End If
If Not IsNull(Me.txtFilterCity) Then 'Text field example
strWhere = strWhere "([City] = """ & Me.txtFilterCity & """) AND "
End If
If Not IsNull(Me.txtFilterInvoiceDate) Then 'Date field example
strWhere = strWhere & "([InvoiceDate] = " & _
Format(Me.txtFilterInvoiceDate, strcJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub
For an example of how to handle a date range, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
If you want something more powerful than that, search for "Query by Form".
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"patentinv" <(E-Mail Removed)> wrote in message
news:640FC6AA-14C4-4849-AC10-(E-Mail Removed)...
> Do you have any suggestions on how I can do this? or links to places for
> similiar questions,
> Thanks ,
> "Allen Browne" wrote:
>
>> Filter By Form is not available in the runtime.
>>
>> You will need to code another way to let the user filter the form.
>>
>> "patentinv" <(E-Mail Removed)> wrote in message
>> news:78E79819-665B-465C-84B4-(E-Mail Removed)...
>> > I'm testing my Access 2003 DB in runtime mode, I'm recieving an error
>> > on a
>> > macro that filters to a record on a form.
>> > I''ve also tried converting it to to a VB Code Module, I still recieve
>> > an
>> > error.
>> >
>> > Here's the generated VB Module code it generated
>> >
>> > Function Find_Applicant()
>> > On Error GoTo Find_Applicant_Err
>> >
>> > DoCmd.Echo False, "Please Stand By"
>> > DoCmd.Hourglass True
>> > DoCmd.RunCommand acCmdFilterByForm
>> > DoCmd.RunCommand acCmdClearGrid
>> >
>> >
>> > Find_Applicant_Exit:
>> > Exit Function
>> >
>> > Find_Applicant_Err:
>> > MsgBox Error$
>> > Resume Find_Applicant_Exit
>> >
>> > End Function
>> >
>> > Here are the error messages I receive,
>> >
>> > It brings up a little box that says the (runcommand action was
>> > cancelled)
>> > I
>> > click OK and it brings up another small box that says (OK) so i click
>> > Ok
>> > and it brings up another little box that says (Resume with out error) I
>> > click
>> > OK and it goes back to the same blank little box again, it does this
>> > forever
>> > from (Resume with out error) to Blank Box with OK.
>> >
>> >
>> > Thanks--Any help will be greatly appreciated.
Similar Threads
