vinaylak
asked on
Querying Datacontrol and DAO with the same query statement.
Hi,
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:\salescall
machinedatasearch.RecordSo
Set salesamttot = salesdata.OpenRecordset("S
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.records
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.
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:\salescall
machinedatasearch.RecordSo
Set salesamttot = salesdata.OpenRecordset("S
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.records
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.
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.
("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....
ASKER
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.
Mr.QJohnson,
Thanks again.Your suggestions have really helped me.Sorry about the "stunning" part.I hope you feel OK now!
Mr.Arbert,
Thanks for being nice.I wouldn't certainly like to stun anyone with my programming skills any time in near future.
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.
Mr.QJohnson,
Thanks again.Your suggestions have really helped me.Sorry about the "stunning" part.I hope you feel OK now!
Mr.Arbert,
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.
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Go to the Project menu--choose references and make sure you have Microsoft Data Acess Objects selected.
Brett