Here's an example of a problem I shouldn't get.

[Maury Markowitz]

Here's some increadibly simple code that doesn't work. When it runs, I get a
"Multiple-step {something} generated errors". All it does is set a few bits
of data, then calls a general purpose function to update the display a
little.

These are the only two functions that get called. It crashes in
SetBackgroundColor, when it attempts to read tradeType. I don't see how this
could be an error!

Private Sub DeleteButton_Click()
Application.Echo False

Dim currentOrder As adodb.Recordset
Set currentOrder = Me.Recordset
If currentOrder.EOF = True Or currentOrder.BOF = True Then GoTo ExitOut

If currentOrder("status") <> "Entered" Then
DoCmd.Beep
Else
currentOrder.CancelUpdate

currentOrder("status") = "Deleted"
currentOrder("lastModifiedTimestamp") = Time
currentOrder("lastModifiedById") = GetLoggedInUserId()

currentOrder.Update
End If

ExitOut:
Set currentOrder = Nothing
Call SetBackgroundColor
Application.Echo True
End Sub


Public Sub SetBackgroundColor()
Dim currentOrder As adodb.Recordset
Set currentOrder = Me.Recordset

If currentOrder.EOF Or currentOrder.BOF Then
Me.FormBackgroundColorbox.BorderColor = -2147483640
Me.FormBackgroundColorbox.BackColor = -2147483640
Me.FormBackgroundColorbox.BackStyle = Transparent
ElseIf currentOrder("tradeType") = "Sell" Or currentOrder("tradeType") =
"Sell Short" Then
Me.FormBackgroundColorbox.BorderColor = 12615935
Me.FormBackgroundColorbox.BackColor = 12615935
Me.FormBackgroundColorbox.BackStyle = Normal
Else
Me.FormBackgroundColorbox.BorderColor = 16777164
Me.FormBackgroundColorbox.BackColor = 16777164
Me.FormBackgroundColorbox.BackStyle = Normal
End If

Set currentOrder = Nothing
End Sub

 

['69 Camaro]

Hi, Maury.

You have several syntax errors. There are a number of ways to reference
the name of a field in a Recordset, but your code needs a bit more than
you've written. For example:

currentOrder("status")

.... could be written as:

currentOrder.Fields("status")

And when editing a record, the Edit method needs to preceed the changes to
the values in each of the fields, and then the Update method needs to be
used afterward to save the changes to the record. For example:

currentOrder.Edit
currentOrder.Fields("status").Value = "Deleted"
currentOrder.Fields("lastModifiedTimestamp").Value = Time
....
currentOrder.Update

After you make these code changes, compile the code to see whether or not
there are any other syntax errors in your code.

And when you turn Echo off, make sure that Echo is turned back on in an
error handler within the same procedure. Otherwise, when an error occurs
the screen won't be updated and you'll have to close the application and
reopen it because you can't see what's going on.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Dirk Goldgar]

Here's some increadibly simple code that doesn't work. When it runs,
I get a "Multiple-step {something} generated errors". All it does is
set a few bits of data, then calls a general purpose function to
update the display a little.

These are the only two functions that get called. It crashes in
SetBackgroundColor, when it attempts to read tradeType. I don't see
how this could be an error!

Private Sub DeleteButton_Click()
Application.Echo False

Dim currentOrder As adodb.Recordset
Set currentOrder = Me.Recordset
If currentOrder.EOF = True Or currentOrder.BOF = True Then GoTo
ExitOut

If currentOrder("status") <> "Entered" Then
DoCmd.Beep
Else
currentOrder.CancelUpdate

currentOrder("status") = "Deleted"
currentOrder("lastModifiedTimestamp") = Time
currentOrder("lastModifiedById") = GetLoggedInUserId()

currentOrder.Update
End If

ExitOut:
Set currentOrder = Nothing
Call SetBackgroundColor
Application.Echo True
End Sub


Public Sub SetBackgroundColor()
Dim currentOrder As adodb.Recordset
Set currentOrder = Me.Recordset

If currentOrder.EOF Or currentOrder.BOF Then
Me.FormBackgroundColorbox.BorderColor = -2147483640
Me.FormBackgroundColorbox.BackColor = -2147483640
Me.FormBackgroundColorbox.BackStyle = Transparent
ElseIf currentOrder("tradeType") = "Sell" Or
currentOrder("tradeType") = "Sell Short" Then
Me.FormBackgroundColorbox.BorderColor = 12615935
Me.FormBackgroundColorbox.BackColor = 12615935
Me.FormBackgroundColorbox.BackStyle = Normal
Else
Me.FormBackgroundColorbox.BorderColor = 16777164
Me.FormBackgroundColorbox.BackColor = 16777164
Me.FormBackgroundColorbox.BackStyle = Normal
End If

Set currentOrder = Nothing
End Sub

Maury, the very first question that has to be asked is, is this form in
an MDB file or in an ADP file? If it's an MDB file, then the form's
Recordset is *not* an ADODB recordset (unless you took special steps to
create one and set the form's Recordset property to it), and your code
cannot possibly work. You might not know exactly where the error
occurs, though, because you have turned Echo off, and I don't see any
error-handling code to handle the errors which must naturally arise.

Regardless of whether your form is in an MDB or ADP, why wouldn't you
use much simpler code for your Delete button, like this:

'----- start of suggested code -----
Private Sub DeleteButton_Click()

If Not Me.NewRecord Then

If Me.status <> "Entered" Then
DoCmd.Beep
Else
If Me.Dirty Then Me.Undo
Me.status = "Deleted"
Me.lastModifiedTimestamp = Time
Me.lastModifiedById = GetLoggedInUserId()
RunCommand acCmdSaveRecord
End If

End If

ExitOut:
Call SetBackgroundColor

End Sub

Public Sub SetBackgroundColor()

If Me.NewRecord Then
Me.FormBackgroundColorbox.BorderColor = -2147483640
Me.FormBackgroundColorbox.BackColor = -2147483640
Me.FormBackgroundColorbox.BackStyle = Transparent
ElseIf Me.tradeType = "Sell" Or Me.tradeType = "Sell Short" Then
Me.FormBackgroundColorbox.BorderColor = 12615935
Me.FormBackgroundColorbox.BackColor = 12615935
Me.FormBackgroundColorbox.BackStyle = Normal
Else
Me.FormBackgroundColorbox.BorderColor = 16777164
Me.FormBackgroundColorbox.BackColor = 16777164
Me.FormBackgroundColorbox.BackStyle = Normal
End If

End Sub
'----- end of suggested code -----

 

[Maury Markowitz]

Maury, the very first question that has to be asked is, is this form in
an MDB file or in an ADP file?

Everything I do is ADODB.

The key difference, if I read it correctly, is that you do not use the
recordset reference, and refer directly to fields. Is this correct?

I'll try your suggestions, of course, but I still don't understand why
this could possibly be causing errors. What "multi-step" process is it
talking about? There's nothing _theoretically_ wrong with the code that I can
see, and your version simply uses different handles to get to the same data.

This isn't the only example of this sort of thing. Equally innocuous code
causes Access to crash out entirely in other places. Clicking the button
twice works the first time and returns an error the next. It's all very
random.

Maury

 

[Maury Markowitz]

I implemented the changes you suggested, and I no longer get the "multi-step"
error I previously noted. Now I get an equally odd error about someone
changing the primary key. The app once again dies on this line...

I assume there is another code I should be testing for, something like "if
this line is deleted"?

Maury

 

[Maury Markowitz]

My bad, it's working fine now.

I am a little curious about how to properly save these changes. Your code
used a docmd, but for some reason I tried me.recordset.update, which didn't
work. OMore annoyingly, Me.Recordset.AddNew does not work either, as it
attempts to insert and save a row without some fields set.

I'm still very confused by the relationship between recordset and "me". Why
does working with me.recordset not work, yet me.xxx does? And since the two
should really be the same thing, why does one work and the other not?

Maury

 

[Dirk Goldgar]

Everything I do is ADODB.

I beg your pardon, but I'm not sure that answers the question. Are you
saying "I only work with ADPs", or are you saying, "I always use ADODB
to manipulate data, even though this is an MDB file"? I ask because, in
an MDB file, the form's Recordset is a DAO recordset, not an ADODB
recordset, and these are incompatible types with some different methods,
properties, and behaviors; thus you must use a DAO Recordset object to
work with it. In an ADP, conversely, the form's recordset is an ADODB
recordset and you must use an ADODB Recordset object to work with it.

In either case, of course, you can also use late binding -- Dim rs As
Object -- but you must still be sure to refer only to the methods and
properties appropriate to the type of object you're going to get from
the Recordset property.

Forgive me for going on about this if you already know it, but what
you've said so far and the kinds of errors you are reporting leave me
uncertain about the circumstances.

The key difference, if I read it correctly, is that you do not use
the recordset reference, and refer directly to fields. Is this
correct?

Right. Wherever possible, Access makes both the controls on the form
(bound or unbound) and the fields in the form's recordset available as
properties of the form. Sometimes there's a name conflict with an
existing property, in which case it can't make the field or control into
a property, but the control (and its value) are still available by way
of the form's Controls collection.

I'll try your suggestions, of course, but I still don't understand
why this could possibly be causing errors. What "multi-step" process
is it talking about?

I'm not sure, because you didn't post the full message and besides, I
might not have been able to figure it out from the message anyway. But
various things are probably going on behind the scenes as -- at best --
you and Access are both manipulating the form's recordset at the same
time. Also, by default Access wraps form updates in a transaction, so
it may be that the failure of that transaction is what Access is
complaining about.

There's nothing _theoretically_ wrong with the
code that I can see,

I'm not at all convinced that of that. Certainly, if you're working in
an MDB, there's a *lot* wrong with your code. If you're working in an
ADP, there's a lot less wrong with it, but I'm not sure about your
manipulations of the form's recordset and their interaction with
whatever may be going on with the form itself. I see that you execute
CancelUpdate on the recordset before making your own changes, but I'm
not sure that you don't also have to call Me.Undo (before, after, or
instead), so that the controls on the form don't have dirty values. As
I think I said before, if you're going to work with the same recordset
the form is working on, you're going have to take great care not to
interfere with the operation of the form.

and your version simply uses different handles
to get to the same data.

That's certainly true. I'm using the handles provided for the purpose.

This isn't the only example of this sort of thing. Equally
innocuous code causes Access to crash out entirely in other places.
Clicking the button twice works the first time and returns an error
the next. It's all very random.

I'm sorry, but your assertion that your code is "innocuous" isn't
sufficiently convincing at this point. No one is arguing that Access is
without bugs. But all the code you've posted so far reflects sufficient
ignorance about the Access object model and the way Access works that it
seems much more likely that you're making serious mistakes in your code.
All of us in this newsgroup are willing to help, but you'd be much
better taking the standpoint that "my code isn't working; what's wrong
with it?", rather than "my code isn't working; what's wrong with
Access?"

I've been programming for 28 years now, and I've found my share of bugs
in compilers, operating systems, and application development
environments -- including Access. But still I've learned that the most
productive starting point when I get unexpected results is *always*
"What am I doing wrong?"

 

[Dirk Goldgar]

I implemented the changes you suggested, and I no longer get the
"multi-step" error I previously noted. Now I get an equally odd error
about someone changing the primary key. The app once again dies on
this line...


I assume there is another code I should be testing for, something
like "if this line is deleted"?

It would be easier to debug if you posted the exact error message. Did
you know you can use Ctrl+C to copy the text of those error dialogs to
the clipboard? Then you can paste them into messages.

It looks like you changed the DeleteButton_Click procedure but didn't
change the SetBackgroundColor routine. So maybe there's a conflict
between the changes you made to the form itself and the contents of the
form's recordset. I'm not at all sure about this, though, because I
don't see at the moment how there could be, if you didn't remove the
line I had in there to RunCommand acCmdSaveRecord.

I also don't see how you could get the sort of error you describe on
that specific line you quoted. Are you *sure* that's where it happens?

What is the recordsource of this form, anyway, and what is the primary
key of that table? Does your code change that field (or fields)?

 

[Maury Markowitz]

you know you can use Ctrl+C to copy the text of those error dialogs to

the clipboard? Then you can paste them into messages.

No, that's very handy!

It looks like you changed the DeleteButton_Click procedure but didn't
change the SetBackgroundColor routine.

No, actually I just cut and pasted the wrong lines from the original
message...

The code you gave me worked fine once it was used universally, that is,
once I used the "me.whatever" style of talking to the records.

Or to be exact, it worked fine on my machine, running Access 2003. When we
then ran it on the machine next to me which had Access 2002, Access crashes
on the very first reference to "me.whatever = some new value". So now the app
doesn't work at all on 1/2 the machines in the office, and works fine on the
other half.

Sigh.

Maury

 

[Dirk Goldgar]

No, that's very handy!


No, actually I just cut and pasted the wrong lines from the original
message...

The code you gave me worked fine once it was used universally, that
is, once I used the "me.whatever" style of talking to the records.

Or to be exact, it worked fine on my machine, running Access 2003.
When we then ran it on the machine next to me which had Access 2002,
Access crashes on the very first reference to "me.whatever = some new
value". So now the app doesn't work at all on 1/2 the machines in the
office, and works fine on the other half.

Sigh.

Crashes how? Closes and wants to phone home, or just whines and gives
you an error message?

I can't be sure on so little information, but you may now be stepping
into the territory of a known bug -- or at least, "undesirable
behavior". ;-) Often when databases work on some machines and not
others, it's because of a broken or missing reference. See if the steps
outlined here help:


http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Dirk Goldgar]

My bad, it's working fine now.

I am a little curious about how to properly save these changes. Your
code used a docmd, but for some reason I tried me.recordset.update,
which didn't work. OMore annoyingly, Me.Recordset.AddNew does not
work either, as it attempts to insert and save a row without some
fields set.

AddNew would be attempting to create a new record, not saving the one
you may have been modifying. Was that what you had in mind?

I'm still very confused by the relationship between recordset and
"me". Why does working with me.recordset not work, yet me.xxx does?
And since the two should really be the same thing, why does one work
and the other not?

I can't give you the details because I don't know what goes on behind
the scenes. But the main thing is, the form object itself is using its
recordset to do various things including supporting the user interface
it projects. You *can* also use that very same recordset to do a few
things of your own, but if you do you had better take great care not to
interfere with what the form is trying to do. If you don't, you may get
odd results.

When you work with the other properties and methods of the form object
itself, via the Me reference, you are not interfering directly with the
inner state of the form, but rather asking the form to perform actions
on your behalf. When you do that, the form can handle your request and
adjust its inner state to match. That's just my interpretation, but
(naturally) it makes sense to me. It's not unreasonable on your part to
expect that operations carried out directly on the form's recordset
would work well, but you're getting pretty deep under the hood there.

 

[Dirk Goldgar]

My bad, it's working fine now.

I am a little curious about how to properly save these changes. Your
code used a docmd, but for some reason I tried me.recordset.update,
which didn't work. OMore annoyingly, Me.Recordset.AddNew does not
work either, as it attempts to insert and save a row without some
fields set.

AddNew would be attempting to create a new record, not saving the one
you may have been modifying. Was that what you had in mind?

I'm still very confused by the relationship between recordset and
"me". Why does working with me.recordset not work, yet me.xxx does?
And since the two should really be the same thing, why does one work
and the other not?

I can't give you the details because I don't know what goes on behind
the scenes. But the main thing is, the form object itself is using its
recordset to do various things including supporting the user interface
it projects. You *can* also use that very same recordset to do a few
things of your own, but if you do you had better take great care not to
interfere with what the form is trying to do. If you don't, you may get
odd results.

When you work with the other properties and methods of the form object
itself, via the Me reference, you are not interfering directly with the
inner state of the form, but rather asking the form to perform actions
on your behalf. When you do that, the form can handle your request and
adjust its inner state to match. That's just my interpretation, but
(naturally) it makes sense to me. It's not unreasonable on your part to
expect that operations carried out directly on the form's recordset
would work well, but you're getting pretty deep under the hood there.

 

[Maury Markowitz]

Crashes how? Closes and wants to phone home, or just whines and gives
you an error message?

Instant exit. A second later the dialog appears asking if we'd like to send
a bug report to Microsoft.

when databases work on some machines and not
others, it's because of a broken or missing reference.

I don't really have anything like what this page is referring to, at least I
don't think I do. The one DLL could be a problem I suppose, but it seems to
be causing problems in odd places if that is the case. More evidence against
this is that the app was running on those same machines yesterday.

Somehow changing from currentOrder("tradeType") to Me.tradeType has caused
it to start crashing on 2002. If "references" (nice ambigous term there!) can
also apply to names within the project or the database, then this is still a
potential cause, as it seems that the system is confused about the data I'm
asking it to access.

I think I'm still missing part of the big picture here. In your code
examples you suggested I change from currentRecord("something") to
Me.something. I'm curious what you were referring to here. If I have a field
in the db in called something, does me.something refer to it? Or does the
me.something syntax only refer to objects on the form, like fields and such?

Generally should I talk to the controls on the form, or the fields in the
database? Or does this make no difference?

The reason I ask is that on a different 2002 machine, one with SP1, the
application still crashes, but into the debugger instead. The error (sorry,
no paste, it's a different machine) says something about a missing "Value of
_field" (just that, not a field name or anything). If they mean a form field
that's very weird, because the line is "Me.tradeType" which is a field in the
database only.

Maury

 

[Maury Markowitz]

Another guy here suggested that the problem was non-linear -- the crashes
looked like they were coming from the line the debugger was tripping on, but
that was not the real problem.

He commented out one line of code...

Me.AllowEdits = True

This is part of an IF, with a corresponding FALSE. This method was called
when the user navited, so closed orders couldn't be edited. It seems to have
fixed the problem.

PHHHEEEWWWW

Any known bugs with AllowEdits? I can't find anything on MSDN. I'll have to
come up with some other way to make the record go read-only I guess.

Maury

 

[Dirk Goldgar]

I think I'm still missing part of the big picture here. In your code
examples you suggested I change from currentRecord("something") to
Me.something. I'm curious what you were referring to here. If I have
a field in the db in called something, does me.something refer to it?
Or does the me.something syntax only refer to objects on the form,
like fields and such?

Generally should I talk to the controls on the form, or the fields in
the database? Or does this make no difference?

There's a difference, but generally it makes no *practical* difference.
Here's something I once wrote on the subject:

It's not so much a question of one or the other being "proper syntax",
but that they mean different things that nevertheless almost always give
the same result. As I understand it, the bang (!) notation specifically
denotes that what follows is a member of a collection; in this case, a
member of the form object's default collection, the Controls collection.
The dot (.) notation denotes that what follows is a property or method
of the preceding object. That would logically make the bang notation
"proper" and the dot notation improper.

But wait. Wherever possible, Access makes the controls on a form and
the fields in its recordsource all available as properties of the form.
It also makes the fields of the recordsource available via the bang
notation. I'm not sure exactly how it does this; maybe if a name is
not found in the Controls collection it checks the Fields collection of
the form's recordset as a fallback position. So for most practical
purposes Me!ControlName and Me.ControlName evaluate to the same thing,
and the timing tests I've seen suggest that there is little to choose
between them as far as execution efficiency is concerned. I seem to
recall that there is a very slight difference, but I can't remember
which way the advantage lies, and it's not much. There's a coding-time
advantage, however, to using the dot notation, as it makes the
"intellisense" dropdown lists available. That's a strong argument for
using the dot notation, in my book.

But wait again! I said above that Access makes the controls available
as properties "wherever possible". There are cases where it can't do
that. Specifically, it can't do it when there is already a property of
the same name as the control in question. For example, if your form
"Form1" has a control or a field foolishly named "Name", currently
displaying the value "J. R. User", then executing this statement in the
form's code module:

Debug.Print Me!Name, Me.Name

will print

J. R. User Form1

in the Immediate Window. So you must be careful not to use any reserved
words or built-in properties as names for your controls, if you want to
use the dot notation to refer to them. But then, you should avoid doing
that anyway, as it tends in general to confuse poor Access.

 

[Dirk Goldgar]

Another guy here suggested that the problem was non-linear -- the
crashes looked like they were coming from the line the debugger was
tripping on, but that was not the real problem.

He commented out one line of code...

Me.AllowEdits = True

This is part of an IF, with a corresponding FALSE. This method was
called when the user navited, so closed orders couldn't be edited. It
seems to have fixed the problem.

PHHHEEEWWWW

Any known bugs with AllowEdits? I can't find anything on MSDN. I'll
have to come up with some other way to make the record go read-only I
guess.

Well, now, that's the first time we've seen *that* line in this
discussion. You've been holding out on me again. I don't think I'm
going to make any further attempts to debug your code unless you post
all of it.

There are no bugs with AllowEdits that I've ever heard of, but I suspect
that you may be (a) setting AllowEdits to False when you still have a
dirty record, and/or (b) still mucking around with the form's recordset
while you've got AllowEdits set to False. I have certainly manipulated
AllowEdits to do exactly the sort of thing you're talking about, with no
problems at all. However, I don't believe I've ever used the technique
in an ADP, so there could conceivably be some pitfalls there. But I'd
still start with the premise that it's something you're doing wrong, and
work your way up to the idea that it's a bug.

 

[Brendan Reynolds]

I can't remember for sure whether I already posted this link in this or
another of your threads. If I didn't, it is well worth a read. The title is
"Form Recordsets vs. Form RecordsetClones in Access 2000", but it is still
very much relevant to later versions.

http://www.trigeminal.com/usenet/usenet022.asp?1033

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Maury Markowitz]

Well, now, that's the first time we've seen *that* line in this
discussion.

That's because...

a) it's in a different module
b) not called from any of the code in question
c) never crashed there

You can't blame me too much -- I don't normally have to debug code that
isn't being called in the proc chain!

There are no bugs with AllowEdits that I've ever heard of, but I suspect
that you may be (a) setting AllowEdits to False when you still have a
dirty record,

This is certainly possible.

Maury