Set listbox row source in VBA but results always blank

[Robyn_P]

Hello,

I was wondering if anyone can tell me what's wrong with the following code. It looks right to me, but when I execute it the listbox always shows up empty. I've attached a screenshot of the underlying query containing dummy data.

Private Sub cboFilterStatus_AfterUpdate()
    Me.lstBlankets.RowSource = build_SQL_query()
    Me.lstBlankets.Requery
End Sub


Private Sub cboFilterType_AfterUpdate()
    Me.lstBlankets.RowSource = build_SQL_query()
    Me.lstBlankets.Requery
End Sub




Function build_SQL_query() As String
    
    Dim SQL As String
    
    ' Always include the blanket name filter
    SQL = "SELECT * FROM gryCurrentBlanketStatus WHERE [BlanketName] LIKE *" & [Forms]![frmBlankets]![txtFilterName] & "*"


    ' If the type is not null include this
    If Not IsNull(Me.cboFilterType) Then
        SQL = SQL & " AND [Type]='" & Me.cboFilterType & "'"
    End If
    
    ' If the status is not null, filter on this
    If Not IsNull(Me.cboFilterStatus) Then
        SQL = SQL & " AND  [LastOfStatus1]=" & Me.cboFilterStatus
    End If


    build_SQL_query = SQL
End Function

[Welshgasman]

Have you tried with Debug.Print the sql string and paste that into a query window and try and execute that?

[Gicu]

Can you try this (you don't need the .Requery as setting the RowSource would requery the list box anyway):

Function build_SQL_query() As String    
    Dim sSQL As String
    
    ' Always include the blanket name filter
    sSQL = "SELECT * FROM gryCurrentBlanketStatus WHERE [BlanketName] LIKE '*" & [Forms]![frmBlankets]![txtFilterName] & "*'"




    ' If the type is not null include this
    If Not IsNull(Me.cboFilterType) Then
        sSQL = sSQL & " AND [Type] = '" & Me.cboFilterType & "'"
    End If
    
    ' If the status is not null, filter on this
    If Not IsNull(Me.cboFilterStatus) Then
        sSQL = sSQL & " AND  [LastOfStatus1] = " & Me.cboFilterStatus
    End If




    build_SQL_query = sSQL
End Function

[ssanfu]

@Robyn_P,

Be aware that:

SQL = SQL & " AND [Type]='" & Me.cboFilterType & "'"

"Type" is a reserved word (Field property) in Access

Dim SQL As String

"SQL" is a reserved word in Access (JET reserved (kb248738);ODBC (kb125948);ANSI-92 Reserved (kb287417))

Note: Vlad changed it to

Dim sSQL As String

in his example....

[Robyn_P]

Thanks everyone. I'll replace the reserved words and remove the re-queries and get back to you

[Gicu]

Hi Robyn,
It was not only the reserved words, I also added single quotes after the Like which you were missing:

LIKE '*" & [Forms]![frmBlankets]![txtFilterName] & "*'"

Cheers,

[Robyn_P]

Hi Robyn,
It was not only the reserved words, I also added single quotes after the Like which you were missing:

LIKE '*" & [Forms]![frmBlankets]![txtFilterName] & "*'"

Cheers,

Yes, that was the actual problem. Thanks for spotting that