Hi all,
I am using Allen's excellent form filter script to filter the results from a query.
I would now like to add some further functionality. How do I go about displaying say the top N costs where in my search form I would have a drop down to select the N value and use radio buttons to select the field the top N should be run against (e.g. cost).
Thanks for any help in advance my code is as follows:
I am using Allen's excellent form filter script to filter the results from a query.
I would now like to add some further functionality. How do I go about displaying say the top N costs where in my search form I would have a drop down to select the N value and use radio buttons to select the field the top N should be run against (e.g. cost).
Thanks for any help in advance my code is as follows:
Code:
Option Compare Database
Option Explicit
Private Sub btnSearchit_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere2 As String 'The criteria string.
Dim lngLen2 As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'***********************************************************************
'Begin the filter codes to create the query
'***********************************************************************
'------------------------------------------------
'Begin coding for date search of date occured
'------------------------------------------------
'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtfilterDateStart) Then
strWhere2 = strWhere2 & "([DteOccur] >= " & Format(Me.txtfilterDateStart, conJetDate) & ") AND "
End If
'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtfilterDateEnd) Then 'Less than the next day.
strWhere2 = strWhere2 & "([DteOccur] < " & Format(Me.txtfilterDateEnd + 1, conJetDate) & ") AND "
End If
'------------------------------------------------
' End coding for date searching of date occured
'------------------------------------------------
'------------------------------------------------
' Start coding for list box to see department raised
'------------------------------------------------
'Listbox example.
If Me.lstfilterDeptRaised.ItemsSelected.Count > 0 Then
Dim itm As Variant
Dim strList As String
For Each itm In Me.lstfilterDeptRaised.ItemsSelected
If strList = "" Then
strList = "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
Else
strList = strList & " OR " & "([DeptRaisedBy] = " & Me.lstfilterDeptRaised.Column(0, itm) & ")"
End If
Next
strWhere2 = strWhere2 & "(" & strList & ") AND "
End If
'------------------------------------------------
' End coding for list box to see department raised
'------------------------------------------------
'------------------------------------------------
' Start coding for list box to see department responsible
'------------------------------------------------
'Listbox example.
If Me.lstfilterDeptResp.ItemsSelected.Count > 0 Then
Dim itm2 As Variant
Dim strList2 As String
For Each itm2 In Me.lstfilterDeptResp.ItemsSelected
If strList2 = "" Then
strList2 = "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
Else
strList2 = strList2 & " OR " & "([DeptResp] = " & Me.lstfilterDeptResp.Column(0, itm2) & ")"
End If
Next
strWhere2 = strWhere2 & "(" & strList2 & ") AND "
End If
'------------------------------------------------
' End coding for list box to see department responsible
'------------------------------------------------
'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen2 = Len(strWhere2) - 5
If lngLen2 <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere2 = Left$(strWhere2, lngLen2)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere2
'Finally, apply the string as the form's Filter.
Forms!frmlogforfiltering.Form.Filter = strWhere2
Forms!frmlogforfiltering.Form.FilterOn = True
End If
End Sub
Private Sub btnResetit_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control
'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next
'Remove the form's filter.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show no records.
Me.Filter = "(False)"
Me.FilterOn = True
End Sub
Comment