473,834 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Odd recordset behavior

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_i d = 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_i d = me!customer_id 'Primary key--no error
cObj.last_name = me.Recordset!la st_name 'no error
cObj.first_name = me.Recordset!fi rst_name 'no error

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

Thanks
-Mark
Feb 19 '06 #1
6 1802
DAO or ADO?

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

Feb 19 '06 #2
"Jamey Shuemaker" <ca*********@ya hoo.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

Feb 19 '06 #3
On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksa nyway.org> wrote:
"Jamey Shuemaker" <ca*********@ya hoo.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
ControlSourc e 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
Feb 19 '06 #4
"Wayne Gillespie" <be*****@NOhotm ailSPAM.com.au> wrote:
On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksa nyway.org>
wrote:
The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsourc e 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
Feb 20 '06 #5
"Mark" <no****@thanksa nyway.org> wrote:
The workaround was to fully-qualify the non-PKID field
references:

cObj.customer_i d = me!customer_id 'Primary key--no error
cObj.last_name = me.Recordset!la st_name 'no error
cObj.first_name = me.Recordset!fi rst_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

Feb 20 '06 #6
Mark wrote:
"Wayne Gillespie" <be*****@NOhotm ailSPAM.com.au> wrote:
On Sun, 19 Feb 2006 14:22:49 -0800, "Mark" <no****@thanksa nyway.org>
wrote:

The form has a Recordsource assigned in design mode,
which is deleted in the form_load() procedure. If I leave
Recordsour ce 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
recordsourc e
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.
Feb 20 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
10705
by: Roland Hall | last post by:
I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already found one bug, although stated the bug was only in ODBC, which I'm not using. It appears to be in the OLEDB driver also. My connection was: conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" & "Extended Properties='Text;HDR=NO;FMT=Delimited'"
14
4050
by: deko | last post by:
For some reason this does not seem to be working... Am I missing something basic? Dim rst As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here? ' qryEmailS contains only one column - "strS" ' I want to loop through each item in the rst
4
5537
by: MNC | last post by:
I'm using Access2002, and can't seem to get an updateable recordset going :-( What am I doing wrong, here's the code. The form's controls are not locked, the recordset type is Dynaset (changing to Dynaset inconsistent updates does not work), I'm allowing edits, and I'm at a loss ... Please help! Option Compare Database
6
6559
by: lenny | last post by:
Hi, I've been trying to use a Sub or Function in VBA to connect to a database, make a query and return the recordset that results from the query. The connection to the database and the query works fine, but passing the resulting recordset back to the sub's caller is not working out.
0
1648
by: sneal | last post by:
A little background: We have an Access 2002 based user interface to our SQL Server 2000 database. The interface is a tabbed form with two of the tabs containing a subform. Data is pulled from the SQL Server using several stored procedures, one sproc for the main form and an additional sproc for each subform. All data is pulled from the SQL Server using ADO; the resulting client side (disconnected) ADODB.Recordset is then applied to each...
9
4733
by: Lauren Quantrell | last post by:
Does changing the Recordset Type property for a form in an Access ADP have any effect on possible record locking? I have a continuous form that is bound to a table but I don't want it editable, so if I make it Snapshot instead of updateable Snapshot will this in any way improve performance and reduce/remove record locking? Thanks, lq
5
5472
by: mail | last post by:
Urgent help needed! I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I have the following problem: If the join on two tables results on duplicate colum names (which appear in both tables) I could reference them by using: RECORDSET("TABLENAME.COLUMNAME")
2
10458
by: Tom Clavel | last post by:
Scratching my head. I am using the filter property on a combo box .AfterUpdate event to get to a single client record. This is causing a some strange behavior: As I enter a subform, I get a no current record error. This is strange to me because: I can see the record contents displayed on the form, and debug.printing them gets me the values. Only the form.recordset is giving me this error. form.recordset .bof and .eof are false. ;...
2
5522
by: wallconor | last post by:
Hi, I am having a problem using Dreamweaver CS3 standard recordset paging behavior. It doesn’t seem to work when I pass parameter values from a FORM on my search page, to the recordset on my results page. - Recordset Paging works if no parameters are used in the recordset sql code (ie. simple sql code): SELECT * FROM db_name WHERE (db_field1 LIKE ‘%text1%’ OR db_field2 LIKE ‘%text2%’)
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9643
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10503
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10544
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10214
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9326
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5790
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3079
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.