Private Sub Go_search()
Dim sSQL As String
Dim cn As ADODB.Connection <--- Error here
Dim rs As ADODB.Recordset
Dim Str As String, itmX As ListItem
Set cn = New ADODB.Connection 'weve declared it as a ADODB connection lets set it.
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & App.Path & "\ADDBK.mdb" 'this is the connection string.
cn.Open
Set rs = New ADODB.Recordset
sSQL = "SELECT Name, Phone, Keywords, Com_No "
sSQL = sSQL & "FROM Company "
If Len(Text1(0).Text) > 0 Then
Str = "WHERE Keywords like %" & "'" & Text1(0).Text & "%'"
End If
If Len(Text1(1).Text) > 0 Then
If Len(Str) = 0 Then
Str = "WHERE Name like " & "'" & Text1(1).Text & "%'"
Else
Str = Str & "AND Name like " & "'" & Text1(1).Text & "%'"
End If
End If
sSQL = sSQL & Str
Set rs = cn.Execute(sSQL)
Do Until rs.EOF = True
Set itmX = ADLV.ListItems.Add(, , rs.Fields("Name"))
itmX.SubItems(1) = rs.Fields("Phone")
itmX.SubItems(2) = rs.Fields("Keywords")
itmX.SubItems(3) = rs.Fields("COM_No")
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing
ADLV.Sorted = True
ADLV.SortKey = 1
ADLV.SortOrder = lvwAscending
ADLV.Refresh
End Sub
