Filter Limitations in Runtime

  • Thread starter Thread starter Karen Hart
  • Start date Start date
K

Karen Hart

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
 
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.
 
Thanks for your reply. Would this allow for multiple criteria filtering,
ie, 2 or more fields on the form simultaneously?
 
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
 
Back
Top