Getting no records in Rst object

[MLH]

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

[Fred Zuckerman]

"MLH" <CR**@NorthState.netwrote in message
news:r1********************************@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

[Fred Zuckerman]

>

On Wed, 23 Jan 2008 07:50:04 -0800, "Fred Zuckerman"
<Zu********@sbcglobal.netwrote:

DAO.Recordset

>"MLH" <CR**@NorthState.netwrote in message
news:9i********************************@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

[Salad]

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

[lyle fairfield]

MLH <CR**@NorthState.netwrote in news:r1nep3tggrnoov0kt2lg56obbceb4cp6dn@
4ax.com:

GetPortionPaid([VehicleJobID]>=[ProcFee])

Really?

and not

GetPortionPaid([VehicleJobID])>=[ProcFee]

?