Odd recordset behavior

[Mark]

Hello.

Before going further, I should mention that I have found a
workaround for this problem. However, I thought it was
sufficiently interesting to try to find out what is causing it.

I have a form which launches when the application is started.
The form is not initially databound. . .the user must first select
a value from a combobox on the form. When the user selects
a value, an appropriate recordset is created and cloned to the
form's Recordsource property. The ControlSource properties
for the form's controls are then set, and we're good to go.

On odd thing that is happening however is that the recordset
fields all return NULL on the very first record fetch, with the
exception of the primary key. Like so:

cObj.customer_id = me!customer_id 'Primary key--no error
cObj.last_name = me!last_name 'INVALID USE OF NULL
cObj.first_name = me!first_name 'INVALID USE OF NULL

All subsequent fetches during the current application session
do not exhibit this problem. If the user selects a different entry
from the combobox (which changes the form's recordsource)
the problem does not reoccur then either.

The workaround was to fully-qualify the non-PKID field
references:

cObj.customer_id = me!customer_id 'Primary key--no error
cObj.last_name = me.Recordset!last_name 'no error
cObj.first_name = me.Recordset!first_name 'no error

If anyone here knows why this is happening, I would like to
know.

Thanks
-Mark

[Jamey Shuemaker]

DAO or ADO?

i have no clue, either way, just wanted to replicate the error to play
with it a while.

[Mark]

"Jamey Shuemaker" <ca*********@yahoo.com> wrote:

DAO or ADO?

I am using DAO and Access 2000.

A few more details:

The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsource blank in design mode, I get compiler errors.

The various controls have their ControlSource values set
in design mode as well. The form_load() procedure saves
these into a collection and then erases them. (I could also
leave them blank in design mode and just assign them when
the user makes a selection from the combobox.)

When the user makes a selection from the combo box,
a DAO recordset is created with the appropriate query
reference. This recordset is cloned to the form's
Recordset property, and the reference is deleted.
Finally, the procedure walks the collection of
ControlSource properties and assigns them to the
appropriate controls. The form is now data-aware.

This may be a totally bizarre way to go about this, but it
is what I was able to come up with. I would welcome any
simpler alternatives that I may have missed (I prefer not
to use a "startup switchboard" form to make the
Recordsource selection.)

-Mark

[Wayne Gillespie]

On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksanyway.org> wrote:

"Jamey Shuemaker" <ca*********@yahoo.com> wrote:

DAO or ADO?

I am using DAO and Access 2000.

A few more details:

The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsource blank in design mode, I get compiler errors.

Your code would need to be in the Form_Open event instead of Form_Load.
The load event fires after the open event, and at this point data has been
loaded and the fields bound to the recordsource. If you delete the recordsource
in the load event you are breaking the link between the bound controls and their
data. Hence the errors.

If you do delete the recordsource in the load event, you would first need to
delete the control's ControlSource before you deleted the recordsource.
The various controls have their ControlSource values set
in design mode as well. The form_load() procedure saves
these into a collection and then erases them. (I could also
leave them blank in design mode and just assign them when
the user makes a selection from the combobox.)

When the user makes a selection from the combo box,
a DAO recordset is created with the appropriate query
reference. This recordset is cloned to the form's
Recordset property, and the reference is deleted.
Finally, the procedure walks the collection of
ControlSource properties and assigns them to the
appropriate controls. The form is now data-aware.

This may be a totally bizarre way to go about this, but it
is what I was able to come up with. I would welcome any
simpler alternatives that I may have missed (I prefer not
to use a "startup switchboard" form to make the
Recordsource selection.)

-Mark

It sounds to me like you are making alot of extra work for yourself.
I would create a separate form for each possible selection from the combo, each
bound to it's own query.
Make each of the forms the same size, and add a subform control to your main
form of equivalent size. On the AfterUpdate of the combo just change the
SourceObject of the subform contol to the name of the appropriate form to
display.
Wayne Gillespie
Gosford NSW Australia

[Mark]

"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> wrote:

On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksanyway.org>
wrote:

The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsource blank in design mode, I get compiler errors.
Your code would need to be in the Form_Open event instead of Form_Load.
The load event fires after the open event, and at this point data has been
loaded and the fields bound to the recordsource. If you delete the
recordsource
in the load event you are breaking the link between the bound controls and
their
data. Hence the errors.

If you do delete the recordsource in the load event, you would first need
to
delete the control's ControlSource before you deleted the recordsource.

I am getting the same result even if I delete the recordsource in
form_open(). The form loads, and the controls all display #NAME?.
In order to prevent this I need to erase all the ControlSource properties,
whether I delete the recordsource in form_open() or in form_load().

Incidentally, the compiler error was a different issue. That had to do with
a procedure that was referencing fields in the form's recordset.
Even though the procedure would only run in a context in which the form
actually _had_ a recorset, the compiler did not know that. So I had to
assign a recordsource in design mode to please the compiler.
It sounds to me like you are making alot of extra work for yourself.

Possibly. If there is a way I can delete the form's recordsource without
the bound controls displaying an error, then I will use it. Regarding your
multiple-forms proposal, I really prefer to have additional code instead
of multiple copies of the same form. It's just a preference I guess. I can
see how your approach would work as well.

Thanks
-Mark

[Mark]

"Mark" <no****@thanksanyway.org> wrote:

The workaround was to fully-qualify the non-PKID field
references:

cObj.customer_id = me!customer_id 'Primary key--no error
cObj.last_name = me.Recordset!last_name 'no error
cObj.first_name = me.Recordset!first_name 'no error

Well darnit, in following up on Wayne's recommendations I
discovered that my workaround is NOT working. When I
include the Recordset qualifier in the object reference, I actually
get the data from the PREVIOUS record! The wrong data is displayed.

So my new workaround is to check for ERR_INVALID_USE_OF_NULL on
the non-PKID field reference. If ERR_INVALID_USE_OF_NULL is
encountered, I create an entirely new DAO recordset and retrieve
the desired non-PKID value for the current PKID.

UGLY!

But at least it works, and it only occurs on the very first
record fetch of the application session.

-Mark

[salad]

Mark wrote:

"Wayne Gillespie" <be*****@NOhotmailSPAM.com.au> wrote:

On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksanyway.org>
wrote:

The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsource blank in design mode, I get compiler errors.

Your code would need to be in the Form_Open event instead of Form_Load.
The load event fires after the open event, and at this point data has been
loaded and the fields bound to the recordsource. If you delete the
recordsource
in the load event you are breaking the link between the bound controls and
their
data. Hence the errors.

If you do delete the recordsource in the load event, you would first need
to
delete the control's ControlSource before you deleted the recordsource.

I am getting the same result even if I delete the recordsource in
form_open(). The form loads, and the controls all display #NAME?.
In order to prevent this I need to erase all the ControlSource properties,
whether I delete the recordsource in form_open() or in form_load().

Incidentally, the compiler error was a different issue. That had to do with
a procedure that was referencing fields in the form's recordset.
Even though the procedure would only run in a context in which the form
actually _had_ a recorset, the compiler did not know that. So I had to
assign a recordsource in design mode to please the compiler.

It sounds to me like you are making alot of extra work for yourself.

Possibly. If there is a way I can delete the form's recordsource without
the bound controls displaying an error, then I will use it. Regarding your
multiple-forms proposal, I really prefer to have additional code instead
of multiple copies of the same form. It's just a preference I guess. I can
see how your approach would work as well.

Thanks
-Mark

I have/had a situation where, when the main form, unbound, opens
initially it doesn't recognize NZ() function calls, etc. Once another
form has to be opened and then it works OK. I had to write some special
SQL statements for the form and use others throughout, even tho they all
do the same.