How to filter

Hi
I want to filter by DAO (.filter), after the recordset is created I want to show the result in DBGrid for five fields. How to go about the best possible way???????
fmuftiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
There is no Filter method in DAO (this method exists in ADO only).

If you want to reproduce it, you have 2 solutions depending on the amount of data you have in your recordset:
1- Use the FindFirst and FindNext method to loop through the existing recordset manually adding lines to the grid.
2- Create a new recordset with the Where clause specifying the condition you want.

I prefer the second proposition!
0
fmuftiAuthor Commented:
I created the second recordset but it show only one record. Cau write for me the syntax for that ( a code of 2-3 line for this purpose). Also same code in ADO if its not very difficult. Because I feel using Datacontrol in between DAO is no good. suggest me with example of each. I'll be very obliged. Thanx anyway.
0
Éric MoreauSenior .Net ConsultantCommented:
Your code should not be different than the one you already have.

The only thing to change is your RecordSource property (of the DataControl).

It should look like:
data1.recordsource = "Select * from Table1 Where field1 = 'test'"
data1.refresh

The Refresh method must be called to get the new recordset!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

fmuftiAuthor Commented:
No I used DAO not the datacontrol. I applied filter on recordset, created another one (the filtered). Now I want to display all the filtered data in the fileds.
0
Éric MoreauSenior .Net ConsultantCommented:
But you've talk about a DBGrid for 5 fields!?!?!
Where do you show your data and how do you populate those controls?

Can you show some of your code.
0
fmuftiAuthor Commented:
The way u r saying is using SELECT with WHERE that I had already done so, but I want to use filter with criteria. Infact I am stuck for my filter code and have resort to Datacontrol as DBgrid does not give me flexibility for DAO. Because I thought that assigning different Recordsource=SELECT statements
I'll achive filtering. Is this what u mean.
Now comes another probem because of DataControls Now as I have two table and I wanted to initialize the text boxes again failed to do. Fol code of FORM_Load procedure
Data2.DatabaseName = App.Path & "\dbEcg.mdb"
Data2.RecordSource = ("Detail")
'txtCommentsS.Text = Data2.Recordset.Fields("Comments")
receives
"Object variable or With block variable not set (Error 91)" error.  Why am I failing at Form_load procedure. I cannot supply my required filtering cirireia as FormLoad proceudre.(Above is not filtering, but if above error remove then filtering will be no problem. OR I HAVE TO LERAN/RESORT TO ADO(do I have to:(((((
0
Éric MoreauSenior .Net ConsultantCommented:
>Because I thought that assigning different Recordsource=SELECT statements
I'll achive filtering. Is this what u mean.

Yes. But don't forget to Refresh your datacontrol.

>Infact I am stuck for my filter code and have resort to Datacontrol as DBgrid does not give me flexibility for DAO.
You seem to mix some things. The DataControl will allow you to retreive data and the DBGrid will display the data (data that comes from the DataControl).

For this problem:
Data2.DatabaseName = App.Path & "\dbEcg.mdb"
Data2.RecordSource = ("Detail")
'txtCommentsS.Text = Data2.Recordset.Fields("Comments")

You must add:
Data2.Refresh
after changing your RecordSource property.

So it will becomes:
Data2.DatabaseName = App.Path & "\dbEcg.mdb"
Data2.RecordSource = ("Detail")
Data2.Refresh
txtCommentsS.Text = Data2.Recordset.Fields("Comments")
0
fmuftiAuthor Commented:
How can filter using query, can someone write a simple query to filter
0
Éric MoreauSenior .Net ConsultantCommented:
Instead of giving your table name as the RecordSource, enter a SQL statement like this:
Data2.RecordSource = ("SELECT * FROM Detail WHERE field1 = 'test'")

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fmuftiAuthor Commented:
pl give me a day or two as I am very busy, I'll let you know, once I evaluate.Thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.