Data entry fields not holding values when bound to recordset based on empty table

[Dan Tharp]

We're having a problem using ADODB recorsets that are bound to fields on a
form.


We have a form with fields bound to a recorset which is defined in the data
environment. The data environment has a connection to a SQL Server 2000
database. The recordset, as defined in the data environment, is initially
opened with a query like 'select top 1 * from part', so we only have 1 record
in the recordset and the screen doesn't take too long to load.

When the user adds a record to the recorset, we close the recordset and re-open
it using a query that will return no records (....where 0=1) and then issue
a recordset.ADDNEW to add a record.
Fields on the form are then re-bound to the new recordset and the user can
data enter values into the bound fields.
When the user clicks save, then we issue a recorset.UPDATE to write out to
the database.

This works fine....except, when the recordset is pointing to a table that
is empty.
Even when we "zero" out the recorset with a "where 0=1" clause to have no
records in the new recorset, as long as the table underneath has at least
one record in it, everything works fine.
If, the table is empty, however, the data entry fields on the form will not
hold values. As the user tabs from field to field, the values are not retained.

We dumped all of the properties of the recorset as well as all of the properties
of the recorset's "Active Command" both in the case where the table had 1
record in it and when the table had no records in it.
There were no differences in ANY of the properties--the recorsets are identical.
The only thing different is that when the underlying table is empty, fields
don't hold values.


Anyone have any ideas as to why this is happening?

[Dan Tharp]

FYI, Update: I'm not exactly sure why it works, but we've figured out a solution
(work-around) to this problem, that I thought you might be interested in:

Whenever we set the (field).DataField, (field).DataMember, and (field).DataSource
properties on a bound field on a form--IN DESIGN MODE--the dataenvironment
automatically opens the recordset, based on the SQL string constructed in
the data environment when the application runs.
We don't need to initially open the recorset on the Form_Load, since the
data environment does this for us.
In the case where the table behind the recordset is empty, bound fields don't
behave correctly after a (recordset).AddNew has been issued.
Once the field loses focus, the value is not held.
We tried this with fields bound, unbound, and all sorts of situations.
We even (programmatically) set (field).DataField to "", (field).DataMember
to "", and (field).DataSource to Nothing, yet the field STILL would not hold
a value if the underlying table behind the recordset was empty.

Again, I'm not sure why this works, but here's what we did to address this:
In DESIGN mode, we set (field).DataSource to the data environent variable,
(field).DataMember to the recordset variable, and (field).DataField to the
field...but then, went back and set (field).DataSource to Nothing.
We did this for every bound field on the form.
Now, since there are no fields (in DESIGN mode) with (field).DataSource set
to the data environment, the recordset does not automatically open.
So, we have to add a line of code in the Form_Load to initially open the
recordset.
Once we did this, we could issue a (recordset).AddNew and all fields hold
their values regardless of whether or not the underlying table has records
or not.

Again...I'm not sure why this works, but setting (field).DataSource to Nothing
(blank) in DESIGN mode and then forcing the recordset open on Form_Load,
allows us to have bound fields that behave normally whether or not the underlying
table has records in it or not.

Just thought you might be interested...

Dan Tharp, dtharp@gdtr.com