Filter using text boxes and a button

[Rudi Groenewald]

Hi all,

Scenario:

I have got a form with a subform, the subform shows all records in a table.
On my main form, I have three unbound listboxes where users can select
"optional search criteria". When the user clicks on a button, a filter
should be applied to the subform which filters using the selections entered
by the user. If a textbox is not used, that field should not be used in the
filter or have all records.

Please help

R

 

[Allen Browne]

This example is designed so that it is easy to add as many conditions as you
need. It also illustrates how to use the different delimiters for the
different field types.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen as Long

'Text field example
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname & """)
AND "
End If

'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & Me.FilterAmount & ") AND "
End If

'Date field example
If Not IsNull(Me.txtFilterSaleDate) Then
strWhere = strWhere & "([SaleDate] = " &
Format(ME.txtFilterSaleDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

 

[Rudi Groenewald]

Hi Allen,

Thanks for the quick response. I pasted and edited your script as follows:

========================================================================
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long


'Number field example
If Not IsNull(Me.CATEGORYID) Then
strWhere = strWhere & "([CATEGORYID] = " & Me.CATEGORYID & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
====================================================================================

I'm only using one field to filter at the moment because I first want to
test if this works. I am getting an error around the line: If Me.Dirty
Then Me.Dirty = False 'Save first


The error is: Runtime Error 2455. You entered an expression that has an
invalid refrence to the property Dirty.

Please help
R

This example is designed so that it is easy to add as many conditions as
you need. It also illustrates how to use the different delimiters for the
different field types.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen as Long

'Text field example
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname &
""") AND "
End If

'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & Me.FilterAmount & ") AND "
End If

'Date field example
If Not IsNull(Me.txtFilterSaleDate) Then
strWhere = strWhere & "([SaleDate] = " &
Format(ME.txtFilterSaleDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

Scenario:

I have got a form with a subform, the subform shows all records in a
table. On my main form, I have three unbound listboxes where users can
select "optional search criteria". When the user clicks on a button, a
filter should be applied to the subform which filters using the
selections entered by the user. If a textbox is not used, that field
should not be used in the filter or have all records.

Please help

R

 

[Allen Browne]

The Dirty property applies only if the form is bound (i.e. has something in
its ControlSource property). The property is True if the record needs to be
saved. Setting it to False is one technique to force the record to be saved.

If it is unbound, the Dirty property does not apply, but I don't see that
applying a filter to an unbound form will do much good.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for the quick response. I pasted and edited your script as
follows:

========================================================================
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long


'Number field example
If Not IsNull(Me.CATEGORYID) Then
strWhere = strWhere & "([CATEGORYID] = " & Me.CATEGORYID & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
====================================================================================

I'm only using one field to filter at the moment because I first want to
test if this works. I am getting an error around the line: If Me.Dirty
Then Me.Dirty = False 'Save first


The error is: Runtime Error 2455. You entered an expression that has an
invalid refrence to the property Dirty.

Please help
R

This example is designed so that it is easy to add as many conditions as
you need. It also illustrates how to use the different delimiters for the
different field types.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen as Long

'Text field example
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname &
""") AND "
End If

'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & Me.FilterAmount & ") AND "
End If

'Date field example
If Not IsNull(Me.txtFilterSaleDate) Then
strWhere = strWhere & "([SaleDate] = " &
Format(ME.txtFilterSaleDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

Scenario:

I have got a form with a subform, the subform shows all records in a
table. On my main form, I have three unbound listboxes where users can
select "optional search criteria". When the user clicks on a button, a
filter should be applied to the subform which filters using the
selections entered by the user. If a textbox is not used, that field
should not be used in the filter or have all records.

Please help

R

 

[Rudi Groenewald]

Hi Allen,

I removed the dirty property, but when I click on the button, nothing
happens. It doesnt filter or even give an error. Ant ideas?

Thanks

R

The Dirty property applies only if the form is bound (i.e. has something
in its ControlSource property). The property is True if the record needs
to be saved. Setting it to False is one technique to force the record to
be saved.

If it is unbound, the Dirty property does not apply, but I don't see that
applying a filter to an unbound form will do much good.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Thanks for the quick response. I pasted and edited your script as
follows:

========================================================================
Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long


'Number field example
If Not IsNull(Me.CATEGORYID) Then
strWhere = strWhere & "([CATEGORYID] = " & Me.CATEGORYID & ") AND
"
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub
====================================================================================

I'm only using one field to filter at the moment because I first want to
test if this works. I am getting an error around the line: If Me.Dirty
Then Me.Dirty = False 'Save first


The error is: Runtime Error 2455. You entered an expression that has an
invalid refrence to the property Dirty.

Please help
R

This example is designed so that it is easy to add as many conditions as
you need. It also illustrates how to use the different delimiters for
the different field types.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen as Long

'Text field example
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFilterSurname &
""") AND "
End If

'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & Me.FilterAmount & ") AND "
End If

'Date field example
If Not IsNull(Me.txtFilterSaleDate) Then
strWhere = strWhere & "([SaleDate] = " &
Format(ME.txtFilterSaleDate, "\#mm\/dd\/yyyy\#") & ") AND "
End If

'etc for other text boxes.

'Remove trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen < 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then Me.Dirty = False 'Save first
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all,

Scenario:

I have got a form with a subform, the subform shows all records in a
table. On my main form, I have three unbound listboxes where users can
select "optional search criteria". When the user clicks on a button, a
filter should be applied to the subform which filters using the
selections entered by the user. If a textbox is not used, that field
should not be used in the filter or have all records.

Please help

R

 

[Allen Browne]

If the form does not have a Dirty property, then it is unbound.

If the form is unbound, there is nothing to filter.