VBA: Clear ListBox if criteria doesn't match

[ssingh75]

Hi Expert,

I am using below code to populate listbox from database using textbox criteria.

Private Sub txtIssuer_Change()

Dim strSQL1, strSec, strDate As String
Dim l, lCol, lRow As Long
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
'Dim ctl As MSForms.Control
Dim ctl As MSForms.ComboBox
Dim i As Long
Dim blnAuto As Boolean
Dim strPart As String, iLoop As Integer, iStart As Integer, strItem As String
Dim strDate1, strDate2, strDate3, strDate4 As String

On Error Resume Next
' Use to remove "Code execution has been interrupted" Error
Application.EnableCancelKey = xlDisabled
' Use to to remove displaye alerts
Application.DisplayAlerts = False


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open ("User ID=" & "Testing" & "" & _
";Password=" & "Testing" & "" & _
";Initial Catalog=" & "Testing" & "" & _
";Data Source=" & "machine1" & "" & _
";Provider=SQLOLEDB.1")


If Trim(Me.txtIssuer.Text) <> "" Then

strSec = Trim(Me.txtIssuer.Text)

' strSQL1 = "select SecurityName from Security Where FinalRedemptionDate='" & strDate1 & "'" & _
' " and SecurityName Like '%" & strSec & "%'"
' delay 5

strSQL1 = "select IssuerName from IssuerMaster_CPCD_IPV" & _
" Where IssuerName Like '%" & strSec & "%'"

rs.Open strSQL1, cn

If Not rs.EOF Then Me.lstIssuer.Column = rs.GetRows
rs.Close
cn.Close

End If

End Sub

But, it is showing result in listbox if strSec doesn't match which should not.

Means, if strSec is not match then listbox should blank.