Querying Datacontrol and DAO with the same query statement.

Posted on 2003-03-15
Medium Priority
Last Modified: 2011-10-03
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.

Question by:vinaylak
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 34

Expert Comment

ID: 8142974
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.


Expert Comment

ID: 8142993
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.
LVL 34

Expert Comment

ID: 8143399
Don't be stunned, he would probably know the query wouldn't work if it made it to that part of the code....
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 8145813
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.

Expert Comment

ID: 8145846
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.


Author Comment

ID: 8145881
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.

Accepted Solution

SpideyMod earned 0 total points
ID: 8174288
PAQ'd and all 50 points refunded.

Community Support Moderator @Experts Exchange

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question