searching using adodb

[siu17]

I have been trying the following code for a code to search for a record and display it in a textbox
Option Explicit
Dim conn As ADODB.Connection, rec1 As ADODB.Recordset
Dim esql1 As String

Private Sub Form_Load()
Set conn = New ADODB.Connection
Set rec1 = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\id\Project.mdb;Persist Security Info=False"
conn.Open

esql1 = "select * from table"
rec1.Open (esql1), conn, adOpenDynamic, adLockOptimistic
GetText
End Sub
Dim searchvar As Integer
Private Sub Command1_Click()
Text1 = ""
Text2 = ""
Text3 = ""
searchvar = InputBox("Enter item to find")
rec1.Close
rec1.Open ("select * from table where 'Key = searchvar'"), conn, adOpenStatic, adLockReadOnly
If rec1.Fields(0) <> "" Then
Text1 = rec1.Fields(1)
Text2 = rec1.Fields(2)
Text3 = rec1.Fields(3)
Else
MsgBox ("No matching records found")
rec1.Close
rec1.Open ("select * from table "), conn, adOpenDynamic, adLockOptimistic
GetText
End If
End Sub

Private Sub GetText()
If rec1.BOF = True Or rec1.EOF = True Then Exit Sub
Text1 = rec1.Fields(1)
Text2 = rec1.Fields(2)
Text3 = rec1.Fields(3)
End Sub

but the mathcing record is not getting displayed
please help

[vb5prgrmr]

Okay siu17,

Please take the following as constructive comments from someone who has been a team leader and an integrator.

Even though there is a default property for a lot of things like your text boxes, you should not rely upon them but explicitly use the dot syntax to show exactly which property you are modifying...

Do not do this...

Text1 = ""

But do this instead...

Text1.Text = ""

Or better yet...

Text1.Text = vbNullString

Then when it comes to accessing your recordset/fields you should do something like this...

Text1.Text = Rs.Fields("FieldName").Value

These just make it easier to actually read the code when it comes time to perform maintenence upon it.

Now, I also see that you declared a variable between two subs...

End Sub
[B]Dim searchvar As Integer[/B]
Private Sub Command1_Click()

Now that declaration shoud be at the top in the "general declarations" section of the form (just under Option Explicit).

Now lets hope I can answer your question...

This...

rec1.Open ("select * from table where 'Key = searchvar'"), conn, adOpenStatic, adLockReadOnly

is where your problem is...

Since searchvar is declared as an integer, you do not need the single ticks as you are stating that you are not searching for a string (and you have the single ticks in the wrong place.).

It should be something like...

rec1.Open "select * from table where Key = " & searchvar, conn, adOpenStatic, adLockReadOnly

However, if you were to search on a string it would look something like this

rec1.Open "select * from table where Key = '" & searchvar & "'", conn, adOpenStatic, adLockReadOnly

Also, use the [ code ] [ /code ] (no spaces) tags when posting code.

Good Luck