Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / January 2005

Tip: Looking for answers? Try searching our database.

Filter Limitations in Runtime

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Karen Hart - 13 Jan 2005 21:22 GMT
Why is the filter by form feature not available in the runtime version?  Is
there a workaround?

The right click dialog box when in a field in a form is so valuable to my
end users.  Especially the "Filter For" option.  Any workaround for this as
well?

Thanks in advance,
Karen
Allen Browne - 14 Jan 2005 01:25 GMT
The Filter By Form view is not available in the runtime, for the same
reasons that design view is not available.

What we do is to provide another set of controls in the Form Header section
where the user can enter the filtering values, and click a command button to
apply the filter. Most forms only have 3 - 5 fields that need to be filtered
on. Although it involves a bit more work, ultimately it is much more
efficient for the user than Filter By Form is.

Signature

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.

> Why is the filter by form feature not available in the runtime version?
> Is there a workaround?
[quoted text clipped - 5 lines]
> Thanks in advance,
> Karen
Karen Hart - 14 Jan 2005 18:05 GMT
Thanks for your reply.  Would this allow for multiple criteria filtering,
ie, 2 or more fields on the form simultaneously?

> The Filter By Form view is not available in the runtime, for the same
> reasons that design view is not available.
[quoted text clipped - 14 lines]
>> Thanks in advance,
>> Karen
Allen Browne - 15 Jan 2005 02:48 GMT
Sure.

Here's an example that filters by a GroupID, and also a date range. A
trailing " AND " is tacked onto each one, so you can very easily add further
filter boxes as needed, and then the trailing " AND " is removed at the end.

-------------------code starts---------------------------
Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
   Dim strWhere As String      'String to use as filter.
   Dim lngLen As Long          'Length of string.
   Const conJetDate = "\#mm\/dd\/yyyy\#"

   'Save any changes before filtering.
   If Me.Dirty Then
       Me.Dirty = False
   End If

   'Filter on the Group.
   If Not IsNull(Me.cboFilterGroup) Then
       strWhere = strWhere & "(GroupID = " & Me.cboFilterGroup & ") AND "
   End If
   'Start date.
   If Not IsNull(Me.txtStartDate) Then
       strWhere = strWhere & "(ActivityDateTime >= " & _
           Format(Me.txtStartDate, conJetDate) & ") AND "
   End If
   'End Date: Less than the next day, to get all times.
   If Not IsNull(Me.txtEndDate) Then
       strWhere = strWhere & "(ActivityDateTime < " & _
           Format(Me.txtEndDate + 1, conJetDate) & ") 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 found.", vbInformation, "No filter."
   End If

Exit_cmdApplyFilter_Click:
   Exit Sub

Err_cmdApplyFilter_Click:
   MsgBox "Error " & Err.Number & " - " & Err.Description
   Resume Exit_cmdApplyFilter_Click
End Sub
-------------------code ends---------------------------

For your Remove Filter button, just set its On Click property to exactly
this:
   =ClearFilterAndHeader([Form])
Copy the function below into a general module, and it removes the form
filter and clears all unbound controls in the Form Header section of the
form:

-------------------code starts---------------------------
Public Function ClearFilterAndHeader(frm As Form)
On Error GoTo Err_ClearFilterAndHeader
   'Purpose:   Remove the filter, and clear all the unbound contorls in the
form header.
   Dim ctl As Control      'Each controls in the form header.

   'Save if necessary.
   If HasProperty(frm, "Dirty") Then
       If frm.Dirty Then
           frm.Dirty = False
       End If
   End If

   'Remove the filter.
   frm.FilterOn = False

   'Clear all the unbound controls in the form header.
   For Each ctl In frm.Section(acHeader).Controls
       If HasProperty(ctl, "ControlSource") Then
           If Len(Nz(ctl.ControlSource, vbNullString)) = 0& Then
               If Not IsNull(ctl.Value) Then
                   ctl.Value = Null
               End If
           End If
       End If
   Next

Exit_ClearFilterAndHeader:
   Exit Function

Err_ClearFilterAndHeader:
   MsgBox "Error " & Err.Number & " - " & Err.Description
   Resume Exit_ClearFilterAndHeader
End Function
-------------------code ends---------------------------

HTH

Signature

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.

> Thanks for your reply.  Would this allow for multiple criteria filtering,
> ie, 2 or more fields on the form simultaneously?
[quoted text clipped - 17 lines]
>>> Thanks in advance,
>>> Karen
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.