Insert query results into Me.Filter string

[Scotbot]

Hi,

I have a query which returns values from a column in my table and a split form which shows the table.

I have a command button which I want to use to filter the records. I know how to use VBA filter strings for example this one which filters according to a combo box value:

Me.Filter = "strLastName = '" & Me.ComboFilterLastName.Value & "'"
DoCmd.RunCommand acCmdApplyFilterSort

My question is: How do I re-write the string so that strLastName is compared to the results of a query which is called FilterCalls, instead of being compared to the value of thye combo box?

Many thanks in advance for your help!

 

[boblarson]

You would use a method similar to this here which is for opening a report. But you can create the filter string the same way and then assign it instead of opening a report:
http://www.baldyweb.com/multiselect.htm

 

[Scotbot]

Thanks for your help. I've got stuck on the syntax I think, I'm not sure how to place the query results into the filter string. I've tried:

Me.Filter = "ActivityOutlet = DoCmd.OpenQuery(FilterCalls)"
DoCmd.RunCommand acCmdApplyFilterSort

Which returns an error stating there is an undefined function...

 

[boblarson]

Thanks for your help. I've got stuck on the syntax I think, I'm not sure how to place the query results into the filter string. I've tried:

Me.Filter = "ActivityOutlet = DoCmd.OpenQuery(FilterCalls)"
DoCmd.RunCommand acCmdApplyFilterSort

Which returns an error stating there is an undefined function...

It would seem that you didn't read the link that I gave and then digest it. You have to build the list - you can't just use the query.

So, here it is.

Dim rst As DAO.Recordset
Dim strFilter As String
 
' opens the query in a recordset
Set rst = CurrentDb.OpenRecordset("FilterCalls")
 
' loops through building the values and uses Chr(34) which is a double quote
Do Until rst.EOF
   strFilter = strFilter Chr(34) & rst(0) & Chr(34) &  ","
Loop
 
' strips the last comma off
If Right(strFilter, 1) = "," Then
   strFilter = Left(strFilter, Len(strFilter)-1)
End If
 
rst.Close
 
' uses the IN keyword to use multiple values
Me.Filter = "[strLastName] In(" & strFilter & ")"
Me.FilterOn = True
 
Set rst = Nothing

 

[Scotbot]

Thanks, I did read your link however I didn't understand it as I'm very new to VBA. I'll give your code a try - thanks for your time.

It's possible that like many newcomers to VBA I'm not yet at the level where I can fully interpret code and know what to adjust.

 

[boblarson]

Thanks, I did read your link however I didn't understand it as I'm very new to VBA. I'll give your code a try - thanks for your time.

It's possible that like many newcomers to VBA I'm not yet at the level where I can fully interpret code and know what to adjust.

Not a problem - it just stood out because there wasn't any looping going on like the code I had shared.

 

[Scotbot]

Thanks for your help. In the end I created a query and set it as the record source for the form, and then used a QueryDef to re-write the SQL and a form refresh to load the filtered values. I found that the looping was taking quite a while to execute.

Cheers, Scott.