I'm generating a report from a db, want a certain section of a table to be displayed only if there is data in a particular recordset field. The following code is intended to produce table rows only if a recordset field "Echo_Rpt_Comments" is not null. Execution of the code produced the "Comments:" headline, but the "Echo_Rpt_Comments" recordset data was not displayed.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

I tested by adding another "If Not IsNull" statement for the "Echo_Rpt_Comments" field immediately following the preceding code. The result indicated that the value of the recordset field had changed to null. Why?

My successful workaround is to use a variable to hold the contents of the recordset field, and use the variable instead of the recordset field. The following code works as expected.

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

This problem (and workaround solution) was observerd on 2 different web servers (TEST and PROD), each running WinNT4 (SP6a), IIS4. The db is Access97, web servers use ODBC driver (DSN) to hit it; MDAC v2.5 SP1 is installed on the boxes, as spec'd by the db vendor. Servers have both Jet v3.51 SP3 and Jet v4.0 SP6, but since I'm using ODBC to hit the db this is probably irrelevant.

Hi,

try to use one of the constants to define the type of cursor used:
Const adOpenDynamic = 2
Const adOpenStatic = 3

when opening the recordset, like this:

RSObj.Open strSqlQuery, strDataConn, adOpenStatic

The constants are defined in the adovbs.inc or just use the values.


The default value is adOpenForwardOnly, used when only one pass through a Recordset is needed.

adOpenDynamic - Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them.

adOpenStatic - Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible.

adOpenForwardOnly - Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset.

thank you - <DUH!>...

I think I've been bitten by that bug before... or one like it, anyway. I knew immediately you were right - testing just confirmed it.

thanks again for the reminder!