Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 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....
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

581 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