Querying Datacontrol and DAO with the same query statement.

I have been trying to develop an application in VB with Access backend.
When I give the code as below:
(machinedatasearch is a datacontrol and salesamttot is a recordset.)

Set salesdata = OpenDatabase("C:\salescalls\salesdata.mdb")

machinedatasearch.RecordSource = "SELECT * FROM machinedata WHERE  custcode LIKE '" & custamt & "' AND action  LIKE '" & actiony & "' AND year(calldate)='" & salesyr & "';"

Set salesamttot = salesdata.OpenRecordset("SELECT * FROM machinedata WHERE custcode LIKE '" & custamt & "' AND action  LIKE '" & actiony & "' AND year(calldate)='" & salesyr & "'", dbOpenDynaset)
As you can see,both the data control and the recordset object are queried with the same statement.
But I get the currect set of records only with the datacontrol.The DAO gives the number of records as 2 while the datacontrol gives it as 1 which is the correct one.
Then again,how can I get the recordcount when I am using a datacontrol alone?When I give the statement as "machinedatasearch.recordset.recordcount"
I get an error which says "Object Variable or With Block variable Not set!"
How can I resolve this?
I had tried the DAO query statement without the dbopendynaset part too.That didn't work either.
Please help me out with this.

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.

Do you have a reference added to your project for the DAO library?

Go to the Project menu--choose references and make sure you have Microsoft Data Acess Objects selected.

Frankly, I'm stunned that the query would work at all:

("SELECT * FROM machinedata WHERE custcode LIKE '" & custamt & "' AND action  LIKE '" & actiony & "' AND year(calldate)='" & salesyr & "'",

The Year() function return a number and you are comparing to a string value which should be causing a type mismatch error at run time.

Also, minor point here, since you are not using any wildcards in your LIKE comparison, you may as well use Equals, right?  I believe Equals will run faster.

On the issue of the error code 91 (object or with block variable not set), I believe all you have to do is use the datacontrol's .Refrech method after setting the .RecordSource property and you should be good to go.
Don't be stunned, he would probably know the query wouldn't work if it made it to that part of the code....
Ultimate Tool Kit for Technology Solution Provider

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 now.

vinaylakAuthor Commented:
To Mr.QJohnson and Mr.Arbert,
Thanks a lot for your kind help.It was something that I had missed out on.I forgot the .MoveFirst before working on a loop which extracts the fields and adds their values.
Since the record pointer was at the EOF it was always giving a NO CURRENT RECORDS message.
Thanks for the tip regarding the .Refresh .Now that part works fine as well.
As for the string comparison part,I have converted the year(calldate) to String.I hope it isn't wrong.Is there any other way to go about it?
It works just fine with the LIKE or "=" comparison operators.I would rather use the "=" anyway.I had used the other one just to see if that would give me the right records.
Thanks again.Your suggestions have really helped me.Sorry about the "stunning" part.I hope you feel OK now!
Thanks for being nice.I wouldn't certainly like to stun anyone with my programming skills any time in near future.
Glad to hear you seem to have found the problem.  

Just a couple of notes for your consideration.

.MoveFirst will not solve your .RecordCount problem - but .MoveLast will.  However, both will cause a run time error if your recordset is empty.  So you should test for .BOF and .EOF before attempting either navigation method.  

Also, the record pointer wasn't at EOF before your first navigation statement - it was at BOF.  If it was at both, you would have an empty recordset (and zero would be the correct recordcount).  It was giving you NO current records because it hadn't traversed any of them yet.  .MoveLast forces it to do just that.

Good luck.

vinaylakAuthor Commented:
Dear QJohnson,
I had given a .MoveLast previously for the .RecordCount.And Stupid me,I had forgotten to move the pointer to the first record for the next loop.
Anyway,thanks again for the suggestion.
PAQ'd and all 50 points refunded.

Community Support Moderator @Experts Exchange

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
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.