You're right: Microsoft's example code is minimalist.
Some of this is a question of style, and some is experience. Error handling
in VBA lets you handle an error after it occurs, so you are not compelled to
consider what could go wrong and handle those cases. In practice, there are
cases where that's not a good approach. One error can trigger another which
triggers another, and so the message you ultimately get it meaningless and
unhelpful to the user, and the developer has no idea what actually went
wrong. An experienced developer should therefore identify the places where
things are likely to go wrong, head some of those off, and provide
meaningful messages.
For an example relevant to this thread, consider the code that the wizard
creates when you add a combo to your form and choose 'record navigation' in
the wizard. (Assumes A2003 or earlier, as A2007 creates embedded macros.)
This code can leave the user with nonsense messages such as:
You tried to execute an Update without a corresponding Edit.
What actually happens is that the code in the AfterUpdate event of the combo
attempts to find another record (the FindFirst.) Before it can move record,
the current record has to be saved, so this triggers a cascade of events for
the text box and the record, and one of those can fail (e.g. if the record
cannot be saved.) So, something in the chain of events you triggered went
wonky, and so the attempt to move record fails, and so the AfterUpdate code
can't complete, and somewhere in that circuitious expedition a message is
triggered that has no real relevance or explanation of what went wrong.
To prevent this, you explicitly save the current record (if it needs it)
*before* moving to another one. This triggers the pending events and gives
you a more meaning idea of what's wrong. The code to save *this* form's
record if it needs it is:
If Me.Dirty Then Me.Dirty = False
Now if the record cannot be saved, the error message says that the property
could not be set. That means that the attempt to set the form's Dirty
property to False failed (i.e. the record is still dirty), which is
meaningful to the developer, but not to an end user. Consequently, you will
want to catch the error, and substitute your own error message.
There are actually several of these errors that you may want to trap: 3314,
2101, 2115, as shown in the sample error handler code here:
http://allenbrowne.com/ser-23a.html
So, the upshot is to always explicitly save before any operation that
requires the record to be saved (such as a Requery, applying a filter,
changing the sorting, reassigning the RecordSource.) This triggers the
pending events, gives more meaningful errors, and simplifies debugging.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
cinnie said:
Allen - thank you for a very thorough answer. I can see now that the code
I
had been using had several deficiencies. Interestingly enough, that code
came from the Microsoft Help & Support site (Article ID: 209537). Go
figure!
--
cinnie
Allen Browne said:
There are several things that can go wrong with using just your 2 lines:
a) If the form is dirty and can't be saved yet (e.g. required field not
entered),
the move can't take place.
b) If the combo is blank, the FindFirst string will be mal-formed.
c) If the FindFirst doens't match (e.g. because the form is filtered),
moving to the bookmark won't work.
d) If the move doesn't work, your user gets no message about what went
wrong.
e) There's an issue with using Recordset objects from different
libraries.
f) Execution is (slightly) more efficient to delcare a variable if you
are
going to refer to something multiple times. (That's purely academic for
this
case, but good programming practice and does make a difference inside a
loop.)
So, what I actually recommend is this code that deals with all those
issues:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
greetings
To improve my coding,I've been studying examples of the gurus and using
these ideas to update an old database I wrote some years ago. My
question
has to do with using a combobox to find records. Experts like Allen
Browne
show code in the cbo's AfterUpdate Event like this...
Dim rs As DAO.Recordset
...
Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
Me.Bookmark = rs.Bookmark
...
Set rs = Nothing
The code above is clear and works great, but is there some reason why
it
is
preferable to what I've been using... (which also seems to work great)
Me.RecordsetClone.FindFirst "[CustomerID] = " & Me.cboMoveTo
Me.Bookmark = Me.RecordsetClone.Bookmark
In this case there are no Dim or Set statements. Are these two code
fargments actually equivalent. Are there any advantages to the longer
version?
