Filter Form

[Guest]

I have a Form that list our current Orders and the stage the order is at.
When the form opens it lists all orders - one field is Status and I need to
be able to apply a filter to break the list down

I found a filter on the net and have added it to the form but it does not
work and I can not understand why.

The event is as follows
Private Sub cmdFilterRecords_Click()

'Variable to hold filtered SQL string
Dim strFilterSQL As String

'Set default record source of form

Select Case Me!optFilterBy
'Filter record source dependant on option checked
Case 1
strFilterSQL = strSQL & " Where [StatusID] = 'Declined';"
Case 2
strFilterSQL = strSQL & " Where [StatusID] = 'Finished';"
Case 3
strFilterSQL = strSQL & " Where [StatusID] = 'Approved';"

'If filter applied with no option selected use default record source
Case Else
strFilterSQL = strSQL & ";"
End Select

' Set record source with filtered SQL

Me.RecordSource = strFilterSQL
Me.Requery

End Sub

It comes up with an error and highlights Me.RecordSource = strFilterSQL

I do not know how to overecome the problem

 

[Guest]

Hi

What is strSQL? It doesn't look like it's being set to anything.
If it is set, does it already have a ";" at the end? This needs to be
removed before adding the where clause.

Alternatively, instead of changing the record source you could try the
following...

Private Sub cmdFilterRecords_Click()
Dim strFilter as String
Select Case Me!optFilterBy
'Filter record source dependant on option checked
Case 1
strFilter = "[StatusID] = 'Declined'"
Case 2
strFilter = "[StatusID] = 'Finished'"
Case 3
strFilter = "[StatusID] = 'Approved'"

'If filter applied with no option selected use default record source
Case Else
strFilter = ""
End Select

Me.Filter = strFilter
Me.FilterOn = True

End Sub


Regards

Andy Hull