Me.Recordset vs a recordset variable.

[LAS]

In a different thread I was strongly advised not to do this: Set irst_StudentTracking = Me.RecordSet (in load event), and then use the recordset variable for subsequent sork.. I'd like to know what others think. Below are the pros and cons that I have been able to figure out. I'm using Access 2007. I'm interested in this because I certainly didn't invent the idea of Set <recordset variable> = Me.Recordset. I got it from an example somewhere.

Pros
- If you use a variable, you get a list of its properties and methods as soon as you type the dot at the end. This is not true with Me.Recordset. In fact, when you type your first letter, a tiny beep is emitted. It certainly feels like it's invalid. It's only because of the advice I got that I even tried to compile, say Me.Recordset.RecordCount.

Cons
- The reason given for not using a variable was that I would have two recordsets that behaved separately, e.g., "You are checking that the first recordset is on the new record but then trying to edit the second recordset which is not on a new record."

Since working with a variable has been satisfactory in a number of situations, I tested this idea by doing the following. I put the code before and after Me.requery, .AddNew and in the Current event. I put stop statements after all assignments below were made, and then issued the print command in the immediate window. I did not find any situation where the Me.Recordset values differed from variable's values. I did this when there were no rows and when there were one or more rows. Interestingly, .NewRecord seems to be a property of the form. irst_StudentTracking.NewRecord was not compilable for me. Although both Me.NewRecord and Me.Recordset.NewRecord were. Can someone give me a situation where the variable's properties and the form's recordset properties would diverge?

print lb_recordseteof, lb_recordsetbof,li_recordsetcount,lb_rsteof,lb_rstbof,li_rstrecordcount

Dim li_recordsetcount As Integer
Dim lb_recordsetBOF As Boolean
Dim lb_recordsetEOF As Boolean
Dim li_rstRecordCount As Integer
Dim lb_rstBOF As Boolean
Dim lb_rstEOF As Boolean

lb_recordsetEOF = Me.Recordset.EOF
lb_recordsetBOF = Me.Recordset.BOF
li_recordsetcount = Me.Recordset.RecordCount
lb_rstEOF = irst_StudentTracking.EOF
lb_rstBOF = irst_StudentTracking.BOF
li_rstRecordCount = irst_StudentTracking.RecordCount

 

[David W. Fenton]

Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

In a different thread I was strongly advised not to do this: Set
irst_StudentTracking = Me.RecordSet (in load event), and then use
the recordset variable for subsequent sork.. I'd like to know
what others think. Below are the pros and cons that I have been
able to figure out. I'm using Access 2007. I'm interested in
this because I certainly didn't invent the idea of Set <recordset
variable> = Me.Recordset. I got it from an example somewhere.

It's valid for certain kinds of operations, but it's more the
exception than the rule -- you wouldn't choose editing the form's
recordset as the preferred method for editing the current record
unless there was some reason that the standard methods for doing so
did not work.

Pros
- If you use a variable, you get a list of its properties and
methods as soon as you type the dot at the end. This is not
true with Me.Recordset.

This is the tail wagging the dog -- that is, lack of Intellisense is
not a reason to choose a particular programming method.

And if you would just use the form's default collection (i.e., the
union of the Fields and Controls collections), you'll get
Intellisense for all the members of that collection (two different
kinds of Intellisense depending on whether you use the ! or .
operator; the Intellisense is better with . and you get compile-time
checking, but I recommend against using it and using !; you can use
Ctrl-space to get a different Intellisense list with Autocomplete,
and avoiding compile-time checking makes your VBA code less prone to
corruption because you're not depending on the behind-the-scenes
property wrappers created by the VBA compiler that implement the
compile time checking).

In fact, when you type your first letter, a tiny beep is emitted.
It certainly feels like it's invalid. It's only because of the
advice I got that I even tried to compile, say
Me.Recordset.RecordCount.

The reason Intellisense doesn't work is because in design view, the
form's Recordset and RecordsetClone don't exist yet. But when you
use a recordset variable, VBA knows what the properties of a
recordset are.

But as I said, this is the tail wagging the dog.

If you need access to the form's recordcount, you use
Me.RecordsetClone.RecordCount. To make sure it's accurate, you do a
..MoveLast before checking the count. But you only need to do that if
you want the actual count of records -- if you only need to know if
records are returned, you don't need to .MoveLast. The DAO
RecordCount property is never 0 when any records are returned. It
won't be a reliable count until after a MoveLast, but in general,
you more often need to know that an unspecified number of records
have been returned than you need to know exactly how many there are.

The reason to use the form's RecordsetClone instead of the form's
Recordset is that .MoveLast on the form's Recordset moves the
current record pointer of the form's display and edit buffers to the
last record. This is an undesirable result in most cases. The
RecordsetClone is independent of the form's edit/display buffer, so
you can move the recordset pointer around in it without having an
effect on the current record in the form itself.

Cons
- The reason given for not using a variable was that I would
have two recordsets that behaved separately, e.g., "You are
checking that the first recordset is on the new record but
then trying to edit the second recordset which is not on a new
record."

I'm not sure that reason is correct. I will admit to being foggy on
the relationship of the new record to the form's underlying
recordsource and its .Recordset and RecordsetClone. But your problem
is caused by trying to edit the new record via the form's Recordset,
instead of just editing it through the form's Fields and Controls
collections, as is the default practice in Access.

Since working with a variable has been satisfactory in a number of
situations, I tested this idea by doing the following. I put the
code before and after Me.requery, .AddNew and in the Current
event. I put stop statements after all assignments below were
made, and then issued the print command in the immediate window.
I did not find any situation where the Me.Recordset values
differed from variable's values. I did this when there were no
rows and when there were one or more rows. Interestingly,
.NewRecord seems to be a property of the form.
irst_StudentTracking.NewRecord was not compilable for me.
Although both Me.NewRecord and Me.Recordset.NewRecord were. Can
someone give me a situation where the variable's properties and
the form's recordset properties would diverge?

You're completely ignoring the default collection of the form, i.e.,
the union of the form's Fields and Controls collections. That
collection is what you should be using for editing the form's data
in code, and you get Intellisense with it.

print lb_recordseteof,
lb_recordsetbof,li_recordsetcount,lb_rsteof,lb_rstbof,li_rstrecordc
ount

Dim li_recordsetcount As Integer
Dim lb_recordsetBOF As Boolean
Dim lb_recordsetEOF As Boolean
Dim li_rstRecordCount As Integer
Dim lb_rstBOF As Boolean
Dim lb_rstEOF As Boolean

lb_recordsetEOF = Me.Recordset.EOF
lb_recordsetBOF = Me.Recordset.BOF
li_recordsetcount = Me.Recordset.RecordCount
lb_rstEOF = irst_StudentTracking.EOF
lb_rstBOF = irst_StudentTracking.BOF
li_rstRecordCount = irst_StudentTracking.RecordCount

You shouldn't be using the form's Recordset for editing, whether
directly or via a recordset variable (which, after all, is just as
pointer to the same structure).

You've made this problem for yourself by doing things the wrong way.

Let's go back to first principles:

What is it you want to do when you're on a new record? Assign a
value to a field? If so, just asign the value, e.g.:

Me!FieldFromRecordsource = "New Value"

That will not cause any problems, and you don't have to muck about
with setting recordset variables or updating the recordset. When
you're ready to save the edits to the form's edit buffer, you just
issue this command:

Me.Dirty = False

....and your edits are saved.

 

[LAS]

When I wrote this, I wasn't having any particular problems. This was just a
theoretical posting. I discovered a new problem last evening and posted it
in "Getting Started." Then I re-read this this a.m. Since you referred to
"new record" problems, I'm removing all references to the recordset
variable. My problem posted in Getting Started has gone away. But not all
problems have gone away. Either my fixing will work or I will re-post, but
wanted to say here that using Me! vs a variable does seem to make a
difference. I just can't articulate what it is yet (and may never be able
to)

 

[David W. Fenton]

When I wrote this, I wasn't having any particular problems. This
was just a theoretical posting. I discovered a new problem last
evening and posted it in "Getting Started." Then I re-read this
this a.m. Since you referred to "new record" problems, I'm
removing all references to the recordset variable. My problem
posted in Getting Started has gone away. But not all problems
have gone away. Either my fixing will work or I will re-post, but
wanted to say here that using Me! vs a variable does seem to make
a difference. I just can't articulate what it is yet (and may
never be able to)

Don't hesitate to post back. I like the challenge of explaining
these things that I take for granted -- there's nothing at all
intuitive about it if you don't already know what the possibilities
are.

I'm sure there are plenty of others happy to help out as well.

 

[LAS]

OK. I completely removed my recordset variable from the form. Everything
was Me! or Me. or Me.Recordset. I finally got my form to work the way I
wanted it to. Then I went back and re-created my recordset variable. Put
back the set irst_studenttracking = me.recordset and did a search/replace to
change Me.REcordset to irst_studentracking. The form continues to work
just fine. And now I have intellisense back. (Please see body of your
response for questions I had about your reply). I did retain the Me!
format for table fields. I doubt that this made any difference.

Note again that whoever said that using this approach meant I'd have "two
recordsets with their own NewRecord" was in error, because NewRecord is a
property of a form, not a recordset.

Bottom line, I didn't actually see anything in your response that would
argue against using a recordset variable where me.Recordset is used. You
just say don't do it and that I've made trouble for myself. What trouble?
The troubles I've had don't seem to be related to using a recordset
variable. What troubles might I expect, specifically?

 

[David W. Fenton]

Bottom line, I didn't actually see anything in your response that
would argue against using a recordset variable where me.Recordset
is used. You just say don't do it and that I've made trouble for
myself. What trouble? The troubles I've had don't seem to be
related to using a recordset variable. What troubles might I
expect, specifically?

I'm saying you shouldn't be using the form's recordset for editing
or navigation, regardless of whether or not you use it directly or
via a recordset variable.

Intellisense is a complete red herring, and choosing your
programming methods based on that is really an egregious case of the
tail wagging the dog.

 

[LAS]

But WHY???? What, specifically, can go wrong by using a variable set to
Me.Recordset????

 

[David W. Fenton]

But WHY???? What, specifically, can go wrong by using a variable
set to Me.Recordset????

You're making more work for yourself. Edit the controls/fields
directly.

There is no valid reason I can think of to ever edit the form's
recordset directly. Indeed, I don't trust the relationship between
that object (which was introduced only in A2000) and the form's edit
and display buffers. When editing via the form's default collection,
the results are completely predictable and the behaviors well-known,
since that's the way it's worked since Access version 1.

To me, the only value to the form's recordset property is that you
can set two forms to use the same recordset created in code, but
once the recordset is set, you would use the traditional methods for
editing the recordset, i.e., Me!MyField or Me!MyControl. The other
two places where being able to set the form's recordset is valuable
if is you are using a synthetic recordset or a disconnected
recordset. But again, in both cases, once the form's recordset is
assigned, you'd still edit the data using traditional Access
methods, via the form's default collection.

 

[David Short]

This post seems to match up with something I am working on with my database.

I have a table called "Person" with "PersonID" being an autonumber primary key.

Then I have a form called "frmPersonID" which ultimately displays data from the table "Person" as single form.

There are a number of related tables and therefore subforms withhin frmPerson.

The PersonID is not on frmPerson because there is no need for users to see this (or get confused by a field they cannot update). However, it is useful for me to see it so I have put together some VBA code:

=======================================================
10 On Error GoTo ErrorCode

20 If Me.Form.Recordset.AbsolutePosition <> -1 Then
30 Me.Form.Caption = "Person (" & _
IIf(Me.Form.CurrentRecord > Me.Form.Recordset.RecordCount, "new", _
Me.Form.Recordset.PersonID) & ")"
40 End If

50 ExitCode:
60 Call DoCmd.SetWarnings(True)
70 Exit Sub

80 ErrorCode:
90 Call fcnErrorHandler(ByVal "417AEEAA1A1F4A1481DDFFBA4151F835." & Erl, ByVal Err.Number)
100 Resume ExitCode
=======================================================

Line 20 was added as I kept getting run-time error 3021 (No current record.). This line does not stop the caption update but does stop the 3021 (but admittedly I do not know why - something to do with the subforms perhaps?)

Anyhow the code works. Back to the 'Me vs. Dim' - is the above code acceptable or should a "dim rs as dao.recordset" etc be added and can you offer any insight to the 3021 error? And any other comments above the VBA code?

Many thanks,

D.A.Short

 

[David-W-Fenton]

Anyhow the code works. Back to the 'Me vs. Dim' - is the above
code acceptable or should a "dim rs as dao.recordset" etc be added
and can you offer any insight to the 3021 error? And any other
comments above the VBA code?

The recordset you're using already exists, so there's no reason I
can think of to assign it to a recordset variable. If you were using
it several times in multiple lines, I wouldn't do it then, either,
but use a WITH block.

I don't know where the code you posted is running, but why doesn't
testing for Me.NewRecord take care of what you need to do?