Filter SubForm using "Like"

[Cotty42]

Hi All

I am trying to apply a filter to a subform using the text filter Like "*text*" by setting up a dynamic SQL query for the subform, see code. The issue I have is that the SQL doesn't seem to apply to the form because it isn't filtering the form.

    If Not IsNull(Me.ID) Then
        pubRecCountPubs = 0
        Dim strSQL, strSelectText, strWhereText, strOrderText, strFilterText, strAuthorText As String
 
        strAuthorText = Me.Surname & " " & Left$(Me.Initials, 1)
        strFilterText = "(((Authors) Like " & Chr(34) & "*" & Me.Surname & " " & Left$(Me.Initials, 1) & "*" & Chr(34) & "))"
 
        strSelectText = "SELECT Publications.* FROM Publications "
        strWhereText = "WHERE " & strFilterText & " "
        strOrderText = "ORDER BY Publications.PubYear DESC;"
 
        strSQL = strSelectText & strWhereText & strOrderText
 
        MsgBox strSQL, vbOKOnly, "DEBUG"
 
        Me!PublicationsList.Form.RecordSource = strSQL
        Me.PublicationsList.Form.Refresh
    End If

I have this working fine in a 2010 database using the DoCmd.ApplyFilter method, but need to set up a similar database using 2003 and DoCmd.ApplyFilter doesn't work with subforms and I can't figure out why this code isn't working.

All suggestions welcomed

Thanks

Dave

 

[pr2-eugin]

Try a Debug.Print and see what you get.

 

[Cotty42]

I have tried this and also copied and pasted examples of the created SQL code into a new query and also into the subform and it works fine in both cases, it just seems to be when I run it through VBA code that it doesn't work.

 

[pr2-eugin]

Did you try using a Requery?

Me.PublicationsList.Form.Requery

 

[Cotty42]

Yes, I originally had Requery in place of Refresh but the result is the same.

 

[spikepl]

        strFilterText = "(((Authors) Like  '*" & Me.Surname & " " & Left$(Me.Initials, 1) & "*'))"

 

[Cotty42]

Thanks, still not working.

I've decided to change the subform to a list box and the dynamic sql works fine with this. However, is there a way to count the number of records returned by the SQL so that I can hide the list box if no records are returned.

Thanks

Dave

 

[Cotty42]

Ignore last post - just realised I can use 'ListCount'.

Thanks for your help

Dave