Searching a data bound grid

hirschhouse
hirschhouse used Ask the Experts™
on
Hello experts, I need some “a bunch” of help with a small search tool. I need to connect to an MSAccess DB containing about 300,000 records in one table and display 3 columns of that table… account number, Customer Name & Address. I would like to use an ADO data control with a data bound grid. My form currently has a text box a button a data grid and control. I want to be able to type a name or portion of a name in the text box and select the button and have the data grid display alphabetically all customer names in the db.

Can anyone help?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Here is an example against the Pubs DB from SQL Server (i dont like the ADODB control so here are 2 ways... 1st yours , then mine ;) ):
---Your way---
Private Sub Command1_Click()
Dim mystr As String
mystr = " where au_lname like '" & Text1.Text & "%'"
Adodc1.RecordSource = "select top 1000 * from authors" & mystr
Adodc1.Refresh
End Sub

---my Way---
Dim myRS As ADODB.Recordset

Private Sub Command1_Click()
Dim mystr As String
mystr = " where au_lname like '" & Text1.Text & "%'"
myRS.Close
myRS.Open "select top 1000 * from authors" & mystr
Set DataGrid1.DataSource = myRS
End Sub
Private Sub Form_Load()
Set myRS = New ADODB.Recordset
myRS.CursorLocation = adUseServer
myRS.CursorType = adOpenStatic
myRS.ActiveConnection = "dsn=pubs"
myRS.Open "select top 1000 * from authors"
Debug.Print myRS.RecordCount
DoEvents
Set DataGrid1.DataSource = myRS
End Sub

Commented:
OOOPs ...

I overlooked the "alphabetically"

Change ther line where i build myStr to this:


mystr = " where au_lname like '" & Text1.Text & "%'" & " order by au_lname"

Author

Commented:
Hi  rdrunner,
I  am experiencing some problems with my form I don’t know how to solve. I am getting a “Syntax error in the FROM clause” then a run-time error –Method “refresh” of “Iadodc” failed.
I modified your code with my table and field name

Private Sub Command1_Click()
Dim mystr As String
mystr = " where CUSTNAME like '" & Text1.Text & "%'" & " order by CUSTNAME"
Adodc1.RecordSource = "select top 1000 * from TBLREFUS" & mystr
Adodc1.Refresh
End Sub

Can you see the problem?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi  rdrunner,
I  am experiencing some problems with my form I don’t know how to solve. I am getting a “Syntax error in the FROM clause” then a run-time error –Method “refresh” of “Iadodc” failed.
I modified your code with my table and field name

Private Sub Command1_Click()
Dim mystr As String
mystr = " where CUSTNAME like '" & Text1.Text & "%'" & " order by CUSTNAME"
Adodc1.RecordSource = "select top 1000 * from TBLREFUS" & mystr
Adodc1.Refresh
End Sub

Can you see the problem?

Author

Commented:
Hi  rdrunner,
I  am experiencing some problems with my form I don’t know how to solve. I am getting a “Syntax error in the FROM clause” then a run-time error –Method “refresh” of “Iadodc” failed.
I modified your code with my table and field name

Private Sub Command1_Click()
Dim mystr As String
mystr = " where CUSTNAME like '" & Text1.Text & "%'" & " order by CUSTNAME"
Adodc1.RecordSource = "select top 1000 * from TBLREFUS" & mystr
Adodc1.Refresh
End Sub

Can you see the problem?

Author

Commented:
I got it.. It was my command type property!

http://support.microsoft.com/default.aspx?scid=KB;en-us;q238279

SYMPTOMS
When you set the RecordSource property of an ADO Data Control to a different SQL SELECT statement and then try to execute the Refresh method, the following error is returned:

Syntax error in FROM clause.
which is followed by:

Run-time error '-2147217900(80040e14)':
Method 'Refresh' of object 'IAdodc' failed
or:

Method 'Refresh' of object 'IAdodc' failed when attempting to refresh an ADODC after setting the recordsource property to another value.
The errors occur if the CommandType property of the ADO Data Control is set to adCmdTable.
CAUSE
When the CommandType of the ADO Data Control is set to adCmdTable, "SELECT * From" is automatically prepended to the RecordSource value.

Setting the RecordSource to a table name results in a valid SQL statement, such as SELECT * FROM Tablename.

Setting the RecordSource to a SQL SELECT statement, such as Select * From Tablename, results in a SQL statement of Select * From Select * From Tablename, which is an invalid SQL statement.
RESOLUTION
There are several possible workarounds:
Use a table name instead of a SQL SELECT statement for the RecordSource property, so that SELECT * FROM Tablename is generated
At design time, use a CommandType value of adCmdUnknown. You could then use a table name at design time, and a SQL SELECT statement at run time
At run time, explicitly specify a CommandType of adCmdText or adCmdUnknown, so that SELECT * FROM is not automatically prepended.
Certain providers accept SELECT * FROM (SELECT * FROM Tablename) as a valid SELECT statement, so you could try enclosing the SQL SELECT in parentheses. For example, the Jet OLE DB Provider 4.0 accepts this syntax.

Commented:
Ahh

Glad it works :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial