ADODC1 Search

OK I know this has been posted before but I am having serious troubles with it. I have written an application to search a Access 2000 database for a keyword (inputted by user). I would like my results to be displayed in a datgrid. Here is my code:


Private Sub cmdSearch_Click()

'Call_Logs is my database table name
'Call_Description is my field name in the above table
'txtSearch.Text is input on a form by the user

Dim mySql As String
Dim ConStr As String
Item = Trim(txtSearch.Text)

mySql = "select * from Call_Logs where Call_Description = " & Item

Adodc1.CommandType = adCmdText
Adodc1.RecordSource = mySql
'Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1.Recordset

what am I doing wrong? When I run this code I get no results. My datagrid still shows all entries in the table.

Thanks in advance for your help

Does the item you enter exist in the database?

I see you have adodc1.refresh commented out. You might try adodc1.requery in there somewhere, but I don't think you should have to.

Try the same sql in access itself & see if you get any records back.

Yes my item does exist in the database. I also have tryed adodc1.requery and also does not work. My code actually runs but I get the entire database showing up in my datagrid. Any other suggestions??

Have you tried stepping through with the debugger? I'd be looking for what's in the item variable.

Oh, and if your call description column is a textual datatype you need to put ' around the where condition value

I am still having problems with this. Here is what I have again:

Dim mySql As String
Dim ConStr As String
Item = Trim(txtSearch.Text)
mySql = "select * from Call_Logs where Call_Description = '" & Item & "'"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = mySql
Adodc1.Recordset.Requery
Set DataGrid1.DataSource = Adodc1.Recordset

My code runs but does not display in my datagrid. The datagrid shows the entire database instead of just
the records that my code should return.

try this again it will work perfect

Dim mySql As String
Dim ConStr As String
Item = Trim(txtSearch.Text)
mySql = "select * from Call_Logs where Call_Description = '" & Item & "'"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = mySql
Adodc1.refresh '*************
Set DataGrid1.DataSource = Adodc1.Recordset