Querying Datacontrol and DAO with the same query statement.
Posted on 2003-03-15
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.