Recordset weirdness

[mcalex]

I have a (DAO) QueryDef and RecordSet set up with standard code.

  Set qdf = CurrentDb.CreateQueryDef("", sqlGetSurname)
  qdf.Parameters(0) = candidateID
  Set rst = qdf.OpenRecordset
 
  'check we have only one row
  If Not (rst.BOF And rst.EOF) Then

Everything works fine up to here. The next line breaks VBA:

    rst.MoveFirst

The error is Runtime Error 3420: Object invalid or no longer set.

I hit debug on the msg box, and when I hover over the rst.BOF and rst.EOF in the VBA IDE, I see a tooltip telling me rst.BOF is false (as is .EOF), so the object definitely exists. Yet when i hover over the rst.MoveFirst there is no tooltip.

How does the object become invalid? Does it lose scope as it enters the if branch? Or have I done something stupid

please help, it's driving me crazy

 

[LPurvis]

That's certainly unusual compared to expected behaviour of a non-schema object dropping out of scope.
What is the content of sqlGetSurname?

None the less, and as a resonable practice anyway, you can look to persist the parent database object

Dim db as DAO.Database
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", sqlGetSurname)
... etc

 

[gemma-the-husky]

i would try adding

dim dbs as database
set dbs = currentdb

and using dbs, rather than currentdb that in the qdf assignment.

I always seem to have issues with the currentdb losing scope.



its also possible that there is an issue with this statement

If Not (rst.BOF And rst.EOF) Then

rst.bof and rst.eof can only both be true with an empty recordset, i think - any other recordcount will return false - but i never test both - you would only need to test .eof when you open a file, as it will be true at that point iff there are no records

 

[mcalex]

I gemma, LPurvis

I think I've sussed it. I've 'Dim'ed the QueryDef and Recordset at the top of the module, for reuse in the different procedures. Everything works, but in this instance, this query runs while there is an existing query running (I'm pulling the surname of a candidate - usually I have candidateID, but in this instance the candidate id is selected in a query wrapped around this one). When I 'Dim'ed the QueryDef and Recordset at the top of the procedure everything worked fine.

Also cheers with Dim'ing database and currentDB. I can do this at the top of the module coz I'm only ever using one copy of the database, yeah?

@ gemma
thanks for the heads up with BOF, EOF. I've copied that code from suggestions/tutorials/kb etc on the net, and though I thought it a bit overdefensive when I looked at the spec, i figured better be safe than sorry. Now you've confirmed what I interpreted I'll only use one or the other in future and rip 'em out as I see 'em

thanks
mcalex

 

[LPurvis]

>> "When I 'Dim'ed the QueryDef and Recordset at the top of the procedure everything worked fine."
Hard to be sure of your implementation or what's been done to cause this. Probably just a case of us not being able to see the big picture beyond the code posted.


>> "I can do this at the top of the module coz I'm only ever using one copy of the database, yeah?"
Yeah - you can use the same database object throughout your application if you so choose. (Just again being aware that CurrentDb refreshes the child collections before returning a database object - so certain object changes may not be reflected in a persisted database object).
I'm not a fan of module level variables just because the same type of object is used in all procedures.
It sounds like that's what tripped you up in your initial implementation. If it's the same object (as in the case of the Database object) then that's fine - that's the exact intent of the module level variable.
Otherwise it feels like trying to limit typing at the risk of having incorrectly opened objects in a procedure. Too much risk for too little gain IMO.


On the BOF/EOF front, yes generally one would do as a check - but it's just good practice (and a statement of intent) that you check for both or check the Recordcount. Assuming that the recordset can never be at EOF when it opens unless empty is all well and good - but it is still an assumption.
The tide turned from Recordcount some time ago when ADO came into favour, as a ForwardOnly (the default) ADO recordset doesn't support it. However RecordCount made a resurgance some time ago. I can't say I favour either one particularly - as long as you know what to expect when you use it.

Cheers.