Filter results from access data base to show in Listbox

[owen4512]

Hi all,

I currently have a userform that allows the user to input data in excel and the data will copy into an access data base. I currently have a field in access labeled "FOLLOWUPS". This field will only contain "Yes" or "No" and i'm looking to create a macro that will search this field and return all results for "Yes" and list them in "Listbox1" Here's what i've got so far but i keep getting a syntax error on the line highlighted in red. Any help is always greatly appreciated

Private connect As Object, rs As Object

Sub connection()
Set connect = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
connect.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\Customer Tracker.mdb"
#Else
connect.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\Customer Tracker.mdb"
#End If
End Sub


Private Sub CommandButton6_Click()
Set connect = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Dim Followup As String

Followup = "YES"

Call connection
[COLOR=rgb(226, 80, 65)]    rs.Open "select * from [CUSTOMER_DATA] WHERE [CUSTOMER_DATA].FOLLOWUPS SHOW '" & Followup & "%'", connect, 1, 1[/COLOR]
With Listbox1
    .RowSource = Empty
    .ColumnCount = 15
    .ColumnWidths = "30;30"
    .Column = rs.getrows
End With
    rs.Close
Set rs = Nothing
If TextBox1.Value = Empty Then
Listbox1.Clear
End If
End Sub

 

[owen4512]

doesnt look like the vba copied over properly so please refer to the below;

Private connect As Object, rs As Object

Sub connection()
Set connect = CreateObject("adodb.connection")
#If VBA7 And Win64 Then
connect.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\Customer Tracker.mdb"
#Else
connect.Open "provider=microsoft.jet.oledb.4.0;data source=" & ThisWorkbook.Path & "\Customer Tracker.mdb"
#End If
End Sub

Private Sub CommandButton6_Click()
Set connect = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
Dim Followup As String

Followup = "YES"

Call connection
    rs.Open "select * from [CUSTOMER_DATA] WHERE [CUSTOMER_DATA].FOLLOWUPS SHOW '" & Followup & "%'", connect, 1, 1
With Listbox1
    .RowSource = Empty
    .ColumnCount = 15
    .ColumnWidths = "30;30"
    .Column = rs.getrows
End With
'Call date_format
    rs.Close
Set rs = Nothing
If TextBox1.Value = Empty Then
Listbox1.Clear
End If
End Sub