Recordsets seem EXTREMELY fragile?!

[Guest]

I have a "button pushing" app in Access that keeps crashing out of Access
(completely, the old "do you want to send a report to MS") after doing
completely innocuous changes. I'm trying to figure out how to avoid these
problems.

Here's an example of something I think shouldn't crash (typos because I'm
re-typing, not because they're in the app):

call makeNew
dim currentRecord as recordset
currentRecord = me.form.recordset
currentRecord("type") = "Sell"
currentRecord.Update

BOOOOOM!

And this isn't the only example. It seems whereever there are two functions
that get called in a row that look at me.form.recordset, it will eventually
crash. And it's a hard crash, no trapping allowed.

Even some of the most innocuous code causes this to happen, like EXAMINGING
fields in the record. For instance I have a sub called updateStyles, all it
does is look at the current type, and if it's a Buy it turns the background
color green, and if it's a Sell, red. Every so often, BOOOM.

Why does this happen? How do I avoid it?

 

[Allen Browne]

Are you interested in more stable ways of achieving the same thing?

How about:
Me("type) = "Sell"
Me.Dirty = False

If you want to continue with the approach you have:
1. Rename your variable. Forms have a CurrentRecord property.

2. Be explicit about whether you want a DAO recordset or ADO recordset,
e.g.:
Dim curRec As DAO.Recordset

3. Use the RecordsetClone of the form instead of its Recordset. That's been
around much longer and is much more stable.

4. Make sure the form itself is not dirty before you try to modify values in
its RecordsetClone, to prevent concurrency issues.

5. Include the AddNew or Edit before you try to change the value.

6. If you use RecordsetClone with Edit, set the Bookmark first.

7. Be sure to clean up after yourself, by closing anything you opened (and
only what you opened), and settting all objects to nothing, e.g.:
Set curRec = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Van T. Dinh]

I rarely have problems with Recordsets.

1. "CurrentRecord" is actually a Property of the Form. I
would suggest using a different name. I will use rs in my
codes following.

2. I would recommend disambiguate the Recordset
declaration such as:

Dim rs As DAO.Recordset

3. I would normally use Set with Recordset like:

Set rs = Me.Recordset

Note that I don't use Me.Form.Recordset

4. Assuming that we are talking about DAO Recordset, I
would use:

rs.Edit

before I change any value of the current row in the
Recordset and then end the edit and save the Record with:

rs.Update

5. If you are only changing the CurrentRow and doesn't
need to navigate to another row in the Recordset, you can
set the values directly to the bound Controls or even the
Fields (in the Form's Recordset) using Me rather than
having to use the Recordset. For example, you can simply
use:

Me.BoundControlForType = "Sell"

or

Me.[Type] = "Sell"

HTH
Van T. Dinh
MVP (Access)

 

[Guest]

Are you interested in more stable ways of achieving the same thing?
Yes!

How about:
Me("type) = "Sell"
Me.Dirty = False

Tried this too, same problem. It appears to be interactions between
various parts of the event handling chain, many of whom use current...

If you want to continue with the approach you have:
1. Rename your variable. Forms have a CurrentRecord property.

Tried that. currentOrder blows up too.

2. Be explicit about whether you want a DAO recordset or ADO recordset,
e.g.:
Dim curRec As DAO.Recordset

Ok, I'll try this.

3. Use the RecordsetClone of the form instead of its Recordset. That's been
around much longer and is much more stable.

I think this is what I want to do.

Ok, if I go this route, how do I keep changes to the table up to date? For
instance, if my 'currentOrders' is a clone and I do an AddNew, how to I get
the main form to notice this change and update it's display properly?
Similarily, if I change fields in the current record in the clone, how to I
force those to appear on the screen? In my current experiments the screen and
db get out of sync with clone, or alternately the system ends up selecting
the wrong row after an update which REALLY confuses the users.

4. Make sure the form itself is not dirty before you try to modify values in
its RecordsetClone, to prevent concurrency issues.

For this solution, do you mean I should send out Updates? This is what I
would like to avoid at all costs. I _hate_ systems that do this, because the
user never has a chance to do a real Undo.

5. Include the AddNew or Edit before you try to change the value.

I do. BOOM.

6. If you use RecordsetClone with Edit, set the Bookmark first.

What does this do?

7. Be sure to clean up after yourself, by closing anything you opened (and
only what you opened), and settting all objects to nothing, e.g.:
Set curRec = Nothing

Ok, I'll do this too.

Frankly I'm pretty unimpressed with Access in this regard. Inserting a
record and then changing a few values should not crash Access no matter what
order I do them in!

Maury

 

[Guest]

2. I would recommend disambiguate the Recordset
declaration such as:

Dim rs As DAO.Recordset

What is the ADO version? ADO.Recordset isn't it -- I guess that would be
too obvious.

Maury

 

[Allen Browne]

If you Edit or AddNew the RecordsetClone of the form, the form automatically
gets to know about the edit/insert as soon as it saves.

The RecordsetClone can have a different current record than the record in
the form. That's what it's for. That means you must synchronize them
yourself if you want them pointing to the same record. You do that by
setting the Bookmark.

It is possible to get this running in a very stable way. However, it is also
easy to trigger off a set of events that interact in horrendous ways, and it
sounds like this may be what you are experiencing. For example, there is no
good reason to dirty any bound control in the form in its Current event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Dirk Goldgar]

What is the ADO version? ADO.Recordset isn't it -- I guess that
would be too obvious.

<g>

ADODB.Recordset

 

[Dirk Goldgar]

Tried this too, same problem. It appears to be interactions between
various parts of the event handling chain, many of whom use current...


Tried that. currentOrder blows up too.


Ok, I'll try this.


I think this is what I want to do.

Ok, if I go this route, how do I keep changes to the table up to
date? For instance, if my 'currentOrders' is a clone and I do an
AddNew, how to I get the main form to notice this change and update
it's display properly? Similarily, if I change fields in the current
record in the clone, how to I force those to appear on the screen? In
my current experiments the screen and db get out of sync with clone,
or alternately the system ends up selecting the wrong row after an
update which REALLY confuses the users.


For this solution, do you mean I should send out Updates? This is
what I would like to avoid at all costs. I _hate_ systems that do
this, because the user never has a chance to do a real Undo.


I do. BOOM.


What does this do?


Ok, I'll do this too.

Frankly I'm pretty unimpressed with Access in this regard.
Inserting a record and then changing a few values should not crash
Access no matter what order I do them in!

FWIW, your experience is not typical. Either you're doing something
seriously wrong -- and some of the wrong things have been pointed out to
you -- or there's something wrong with your installation. Access works
like a champ for me and my clients, day in and day out, with nary a
"BOOM" -- and I do a *lot* of VBA work with recordsets in my apps.

 

[Guest]

FWIW, your experience is not typical. Either you're doing something
seriously wrong -- and some of the wrong things have been pointed out to
you -- or there's something wrong with your installation. Access works
like a champ for me and my clients, day in and day out, with nary a
"BOOM" -- and I do a *lot* of VBA work with recordsets in my apps.

You may have learned to not make the mistakes I'm making though. I'm
assuming I'm making some newbie mistakes that are causing this to happen,
ones so basic that you don't even remember them any more. But still, if this
isn't something to do with the installation itself, newbie mistakes shouldn't
cause app crashes either!

Maury

 

[Dirk Goldgar]

You may have learned to not make the mistakes I'm making though. I'm
assuming I'm making some newbie mistakes that are causing this to
happen, ones so basic that you don't even remember them any more. But
still, if this isn't something to do with the installation itself,
newbie mistakes shouldn't cause app crashes either!

I agree that out-and-out crashes should be rare indeed, and generally
reflect a bug in the application. That's why I think there may be
something wrong with your installation. On the other hand, the fact
that you're mucking about with recordsets and VBA code means you've
already gotten out the power tools, so to speak, so you can do yourself
damage if you use them improperly.

That said, the form's Recordset property was not formerly made available
to users/developers, and I think there are still some quirks in it. I
use it frequently, but only for navigation. It was daring of the MS
Access team to allow users to work directly with this critical property.
As Allen said, you can expect RecordsetCone to be more stable. However,
I haven't experienced what you report, that "wherever there are two
functions that get called in a row that look at me.form.recordset, it
will eventually crash." I think whatever is really going on is probably
much more specific than that. If you can come up with a minimum
scenario to reproduce the problem, then if it has the same effect for
others, we can report it as a bug -- and if not, we can try to repair
your installation.

I also agree with Allen that you may well be trying to accomplish your
ends with much more elaborate code than is really necessary. So while
your code may in fact be running into a bug, you may find that you can
accomplish your goal without ever stepping into bug-infested territory.
But you didn't post enough of the relevant code to let us figure out
what you're doing that may be causing the problem, or how to work around
it.

By the way, I don't see that you ever mentioned what version of Access
you're running, and what service level you're up to with Access and Jet.
That may be helpful in diagnosing your problem.

 

[Guest]

All good points. For reference, I'm on Access 2003, SP1. Also I should point
out that I am a "power programmer", but entirely in the OpenStep, C++ and
Java world. I've worked on several million-line apps with some reasonable
level of success, so it's a little frustrating do get these sorts of problems
on a much simpler platform. Anyhoo...

I think the real solution here is Recordsetclone. However my experiments
with this in the past have always led to the screen and data getting out of
sync. Generally speaking, what is the process of using one? What do I need to
do to make sure that the screen gets the updates after I've AddNewed or
Deleted (or even just changed things). What do I do to make sure the selected
row doesn't change?

I'm sure these are simple rules of thumb.

Maury

 

[Dirk Goldgar]

All good points. For reference, I'm on Access 2003, SP1. Also I
should point out that I am a "power programmer", but entirely in the
OpenStep, C++ and Java world. I've worked on several million-line
apps with some reasonable level of success, so it's a little
frustrating do get these sorts of problems on a much simpler
platform. Anyhoo...

I understand, but a new platform is a new platform. Most advanced
programmers who come to Access try to write elaborate code to accomplish
things that are already built-in. That's not to say that there aren't
things that are simple in other environments that are hard to accomplish
with Access -- there are tradeoffs made for Access's optimization for
datacentric applications.

I think the real solution here is Recordsetclone. However my
experiments with this in the past have always led to the screen and
data getting out of sync. Generally speaking, what is the process of
using one? What do I need to do to make sure that the screen gets the
updates after I've AddNewed or Deleted (or even just changed things).
What do I do to make sure the selected row doesn't change?

I'm sure these are simple rules of thumb.

Normally, operations made on the form's RecordsetClone shouldn't have
any synchronization problems with the records displayed on the form
itself. If you seem to be having such problems, you'll have to give me
a specific example I can test out. But here's a very simple example of
adding a record by way of the RecordsetClone:

'----- start of example code -----
Private Sub cmdAddRec_Click()

With Me.RecordsetClone
.AddNew
!Description = "Added via RecordsetClone"
!Modified = Date
.Update
End With

End Sub

'----- end of example code -----

When I execute this code on a form, the new record is added and appears
immediately on the form (this particular form being in continuous forms
view), but the form's current record remains the same as it was before
the addition. I'm using Access 2002, not 2003, but I wouldn't expect
any different behavior for 2003.