Option Explicit
 
Private WithEvents conn As ADODB.Connection
Private WithEvents rs As ADODB.Recordset
 
Private Sub cmdcnsearch_Click()
 
Dim sql As String
sql = "SELECT companyname, companysize, country, city FROM cooperate WHERE accnumber = " & "'" & txtcnsearch.Text & "'"
 
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
 
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Subscriptions Project\subscriptions.mdb"
 
Set rs = New ADODB.Recordset
 
rs.Open sql, conn, adOpenStatic, adLockOptimistic
 
If rs.EOF = True Then
    MsgBox "Record not found", vbExclamation
    txtcnsearch.SelStart = 0
    txtcnsearch.SelLength = Len(txtcnsearch.Text)
    txtcnsearch.SetFocus
 
    Exit Sub
        Else
    txtccname = rs!CompanyName
    txtcsize.Text = rs!companysize
    txtcountry.Text = rs!country
    txtcity.Text = rs!city
    rs.Close
End If
End Sub