Search Functionality RunTime error 424 at Select

My requirement: To search for data in database, using VB forms and 3 text boxes.

1 TextBox, I will give Input (UserName)

1 TextBox for Location

1 TextBox for displaying output

My code is

Private Sub CommandButton3_Click()

Dim Cn As ADODB.Connection '* Connection String

Dim oCm As ADODB.Command '* Command Object

Dim sName As String
Dim rs As ADODB.Recordset
Dim uname As String
Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\test2.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn

‘Record Set
Set rs = New ADODB.Recordset

‘Select Operation
rs.Open "SampleTable", Cn, adOpenKeyset, adLockPessimistic, adCmdTable

uname = rs("UserName")

rs.Open "Select * from SampleTable where uname = '" & text1.Text & "'", ActiveConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

**'Display the Output in TextBox3**
TextBox3.Text = rs("UserName") + rs("Location")

rs.Close
Cn.Close
End Sub
开发者_StackOverflow

I can understand the population of data is not happening so getting a RunTime error 424 at Select statement. How can I retrieve the data for the corresponding Input given?

1. You do not need to open the table; rs.Open "SampleTable" before searching it.
2. Not sure what this is for; uname = rs("UserName")
3. You should just run; rs.Open "Select * ... (Running the .Open on the same RS twice without closing the first is probably whats causing your error)
4. You should escape your input; replace$(text1.Text, "'", "''")
5. You should check for rs.EOF after the .open
6. Use & not + for concatenating strings

update

sub xxx
Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sName As String

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\test2.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open

Set rs = New ADODB.Recordset

sName = replace$(text1.Text, "'", "''")
rs.Open "Select * from SampleTable where UserName = '" & sName & "'", Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

if (rs.eof) then
    msgbox "no match"
else
    TextBox3.Text = rs("UserName") & " " & rs("Location")
    rs.Close
end if

set rs = nothing
Cn.Close
set cn = nothing
end sub