Getting no records in Rst object

The SQL string returns 4 records if pasted into a QBE grid and run
manually. But in this DAO setting, I can't seem to get it to retrun
any records. When line #340 executes, error tells me No Current
Record. Ideas?

160 PString = "SELECT tblVehicleJobs.VehicleJobID,
tblVehicleJobs.ProcFee, tblVehicleJobs.VehicActive,
tblVehicleJobs.BilledOut FROM tblVehicleJobs "
180 PString = PString & "WHERE tblVehicleJobs.BilledOut=False AND
GetPortionPaid([VehicleJobID]>=[ProcFee]) AND
tblVehicleJobs.VehicActive=True"
240 Dim DB As Database, Rst As Recordset, QD As QueryDef
260 Set DB = CurrentDb
280 Set QD = DB.CreateQueryDef("", PString)
300 Set Rst = QD.OpenRecordset(dbOpenDynaset)
340 Rst.MoveFirst

Re: Getting no records in Rst object

"MLH" wrote in message
news:r1nep3tggrnoov0kt2lg56obbceb4cp6dn@4ax.com...
> The SQL string returns 4 records if pasted into a QBE grid and run
> manually. But in this DAO setting, I can't seem to get it to retrun
> any records. When line #340 executes, error tells me No Current
> Record. Ideas?
>
> 160 PString = "SELECT tblVehicleJobs.VehicleJobID,
> tblVehicleJobs.ProcFee, tblVehicleJobs.VehicActive,
> tblVehicleJobs.BilledOut FROM tblVehicleJobs "
> 180 PString = PString & "WHERE tblVehicleJobs.BilledOut=False AND
> GetPortionPaid([VehicleJobID]>=[ProcFee]) AND
> tblVehicleJobs.VehicActive=True"
> 240 Dim DB As Database, Rst As Recordset, QD As QueryDef
> 260 Set DB = CurrentDb
> 280 Set QD = DB.CreateQueryDef("", PString)
> 300 Set Rst = QD.OpenRecordset(dbOpenDynaset)
> 340 Rst.MoveFirst

I would write the code as:

Dim rst as DAO.Recordset
Set rst = CurentDB.OpenRecordset(PString)
rst.MoveFirst

Fred Zuckerman

Re: Getting no records in Rst object

Yep, I've seen the DAO.Recordset thing before. Why is
it better to declare it that way - as opposed to simply
declaring it AS RecordSet?

Oh, and BTW, the SQL string had a misplaced parentheses
that was causing my screw-up.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


On Wed, 23 Jan 2008 07:50:04 -0800, "Fred Zuckerman"
wrote:

>DAO.Recordset

Re: Getting no records in Rst object

MLH wrote:

> The SQL string returns 4 records if pasted into a QBE grid and run
> manually. But in this DAO setting, I can't seem to get it to retrun
> any records. When line #340 executes, error tells me No Current
> Record. Ideas?
>
> 160 PString = "SELECT tblVehicleJobs.VehicleJobID,
> tblVehicleJobs.ProcFee, tblVehicleJobs.VehicActive,
> tblVehicleJobs.BilledOut FROM tblVehicleJobs "
> 180 PString = PString & "WHERE tblVehicleJobs.BilledOut=False AND
> GetPortionPaid([VehicleJobID]>=[ProcFee]) AND
> tblVehicleJobs.VehicActive=True"
> 240 Dim DB As Database, Rst As Recordset, QD As QueryDef
> 260 Set DB = CurrentDb
> 280 Set QD = DB.CreateQueryDef("", PString)
> 300 Set Rst = QD.OpenRecordset(dbOpenDynaset)
> 340 Rst.MoveFirst
>

Why not create a querydef at 280 (give it a name) and exit on 281 (exit
sub) and attempt to run the query?

North Shore
http://www.youtube.com/watch?v=BicBocJhYW8

Re: Getting no records in Rst object

>
> On Wed, 23 Jan 2008 07:50:04 -0800, "Fred Zuckerman"
> wrote:
>
>DAO.Recordset



>"MLH" wrote in message
>news:9ioep3polan0n4se46m498ai6sjhiilc29@4ax.com...
> Yep, I've seen the DAO.Recordset thing before. Why is
> it better to declare it that way - as opposed to simply
> declaring it AS RecordSet?
>
> Oh, and BTW, the SQL string had a misplaced parentheses
> that was causing my screw-up.
>

I'm a little clue-less about recordsets, I've learned the technique by
reading this group. I've NEVER seen the QueryDef technique that was in your
OP.
Fred

Re: Getting no records in Rst object

MLH wrote in news:r1nep3tggrnoov0kt2lg56obbceb4cp6dn@
4ax.com:

> GetPortionPaid([VehicleJobID]>=[ProcFee])

Really?

and not

GetPortionPaid([VehicleJobID])>=[ProcFee]

?

Re: Getting no records in Rst object

MLH wrote in
news:9ioep3polan0n4se46m498ai6sjhiilc29@4ax.com:

> Yep, I've seen the DAO.Recordset thing before. Why is
> it better to declare it that way - as opposed to simply
> declaring it AS RecordSet?

The DAO (Data Access Object) has a child recordset object and ADOdb
(Access Database Object) also has a child recordset object.
Access 2000, introduced the ADOdb object
Different installations of Access reference the two objects in
different orders. Access uses the one with higher priority unless
you specify which by specifying the DAO/ADOdb reference to force the
correct type.



>
> Oh, and BTW, the SQL string had a misplaced parentheses
> that was causing my screw-up.
>
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
>
> On Wed, 23 Jan 2008 07:50:04 -0800, "Fred Zuckerman"
> wrote:
>
>>DAO.Recordset
>



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com