0
votes

I have a form called "Search Issues' and a Subform within call "Browse All Issues". Browse All Issues Record source is a table that contains all the data called Issues. I'm trying to create search features within "Search Issues" where I can select multiple criteria from List box and when I click Search Browse all issues filter on the criteria I selected. I currently I the following code:

Private Sub Search_Click()

On erorr GoTo errr

Me.Search.Form.RecordSource = "SELECT * From Browse_All_IssuesSubform " & BuildFilter

Me.Search.Form.Requery

Exit Sub

errr:
    MsgBox Err.Description

End Sub

Private Function BuildFilter() As Variant

Dim strWhere As String

  strWhere = IIf(Len(Me.AssignedTo & "") <> 0, "([AssignedTo] Like ""*" & Me.AssignedTo & "*"") AND", "") & _

IIf(Len(Me.OpenedBy & "") <> 0, "([OpenedBy] Like ""*" & Me.OpenedBy & "*"") AND", "") & _

IIf(Len(Me.Status & "") <> 0, "([Status] Like ""*" & Me.Status & "*"") AND", "") & _

IIf(Len(Me.Category & "") <> 0, "([Category] Like ""*" & Me.Category & "*"") AND", "") & _

IIf(Len(Me.Priority & "") <> 0, "([Priority] Like ""*" & Me.Priority & "*"") AND", "") & _

IIf(Len(Me.OpenedDateFrom & "") <> 0, "([EnteredOn] >= #" & Format(Me.OpenedDateFrom, "mm/dd/yyyy") & "#) AND", "") & _

IIf(Len(Me.DueDateFrom & "") <> 0, "([EnteredOn] <= #" & Format(Me.DueDateFrom, "mm/dd/yyyy") & "#) AND", "")

If Len(strWhere & "") = 0 

Then
    MsgBox "No criteria", vbInformation, "Nothing to do."


Else

Me.Filter = Left(strWhere, Len(strWhere & "") - 4)

Me.FilterOn = True

Me.Requery

End If

BuildFilter = strWhere
End Function

How can I get his to work? When I run the event I get the message "Compile Error" : Method or data member not found.

Please help

1
Looks like a typo: On erorr GoTo errr, try On Error instead. ;)The Blue Dog
Now its running but it isn't filtering correctly.user3666237
Sorry, I can't help you there - I've never used MS-Access.The Blue Dog
Thanks for your input.user3666237
No problem at all, hopefully someone can help you further.The Blue Dog

1 Answers

0
votes

If it runs without errors but doesn't filter correctly, you probably got the WHERE clause wrong.

First of all, the word WHERE is completely missing in the SQL string that you're building!

But I don't know if that's all.
Without knowing your database tables (definition and the data inside!), it's very hard for us to find any mistakes in your query.

So you should try to find out by yourself, step by step:

  1. In the Search_Click() sub, output the final SQL string to the Immediate Window:

    Debug.Print "SELECT * From Browse_All_IssuesSubform " & BuildFilter
    
  2. Create a new Access query, switch to SQL view, copy & paste the SQL string from the Immediate Window. Try to run the query.

  3. Does it return more rows than you expected?
    Then there's something missing in the WHERE clause. Look at the rows which you didn't expect to be returned, and think about what needs to be added to the WHERE clause to make them disappear from your result set.

  4. Does it return less rows than you expected (or none at all)?
    Then you are filtering too much. Try to delete parts of your WHERE clause, step by step, and each time run the query again until you get all the rows that you want.

This should be enough to point you into the right direction.
If you still aren't able to find the mistake, we need much more information.