"MVP - WannaB" wrote in message
...
Thanks Cliff, you've been a big help.

You're welcome. Comments throughout.

The most changes I've made are to this sub, other suggestions made
I've
addressed below with ------

Private Sub Ub_LostFocus()
On Error GoTo ProcError
If Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc
Dim rst As Recordset

************
Dim fld As Field
looks like you're not using fld --- you can drop this line.

Dim strSearchName As String
Me.ctl_prvUb = Me.Ub
Me.ctl_prvID = Me.ID
Set rst = Me.RecordsetClone
strSearchName = Str(Me!ID) + 1
rst.FindFirst "ID = " & strSearchName

********
Me.RiskDescipt.SetFocus
Focus pertains to the Me.Recordset record currently displayed on the
form. RecordsetClone is a completely independant recordset and has no
interaction with focus. I'd separate SetFocus and RecordsetClone
activity simply for reasons of clarity (disambiguation?).

Also -- is your intent to force the user into the RiskDescipt control
regardless of where on the form they clicked?

Another thought on LostFocus -- it is possible in the standard Access UI
to navigate between records without changing the control with the focus:
ie, I believe that it is possible for the user to enter a new value for
Ub and navigate to a different record without ever triggering the Lost
Focus event for Ub.

If rst.NoMatch Then GoTo ExitProc
Me.Bookmark = rst.Bookmark
If Me.ID = (Me.ctl_prvID + 1) Then
If Me.Lb Me.ctl_prvUb Then
Me.Lb = Me.ctl_prvUb
Me.CreateDate = Now()
rst.MovePrevious
Me.Bookmark = rst.Bookmark

**********
you just lost the changes you made to Me.Lb and Me.CreateDate. If I'm
reading your intent correctly, you need Me.Dirty = False (I think
Me.[Bound Control] = [some value] sets the Me.Dirty flag) to commit your
changes to the current record before using the bookmark to navigate to a
different record.

End If
End If

ExitProc:
Set rst = Nothing
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub


snip
I'm using ONLOSTFOCUS in order to capture any differences that may
occur,
which would not be noticed if this only ran on a value change.


Not quite sure what you meant by that statement. Are you perhaps
saying
that you want this code to test for any changes that might have
happened
to the data outside of this form? If that's the case, then I'd sure
like
to hear from some other more experienced users on the subject of
custom
data integrity enforcement!

Otherwise nothing comes to mind as a possible difference outside of a
value change.

------
No. This would not be to test for changes made from another form!!
There is no other way to change this data
This is just in case there is a breakdown on the form and a user
looses
Connection to the table before the changes can take effect.
This is why I am using the LostFocus rather then the AfterUpdate event

(See my note above on losing or not losing focus.)

Sounds like you're trying to create an independent data validity test.
If that's what you really want, then I think you'd be better served by
having a routine driven from navigation to the parent record which walks
through each of the 11 child records and verifies validity.

My programming experience has taught me that it's impossible to predict
all the possible things that can go wrong -- in most cases when a data
validity error is detected it's better to provide tools to allow the
user to correct the error rather than attempting to code a comprehensive
correction routine.

Prevention is far easier than correction -- that is why they gave us the
BeforeUpdate event.

snip
in general I use form Load instead of form Open because all the
initialization is not complete during the Open event. Look at the
sequence of events provided in the help topic "Load Event", for
instance.

------
Thank you, I was curious about which event to use !!

[I find myself frequently going to the help file to remind myself of
event sequence! g]

snip

You might set the bookmark first, and SetFocus second.
------
Found the right way to use .bookmark (I THINK)
rst.MovePrevious
Me.Bookmark = rst.Bookmark

(See comkment above.)


snip

------
Will consider, but can it be used like this

With Me.RecordsetClone
.FindFirst "ID = " & strSearchName
Me.RiskDescipt.SetFocus
If .NoMatch Then GoTo ExitProc
Me.Bookmark = .Bookmark
End With

Yes, early exits from With...End With constructs are permitted (also For
and Do loops).

I'm trying to recall (without success) which regular it was that has
posted fairly extensively on using With...End With instead of creating
(and annihilating) new objects. With...End With allows you to operate
directly on the object that Access has already provided; using new
objects requires the additional, and frequently unnecessary processes of
creating, populating, and annihilating those objects.

The compiler also allows you to nest With...End With constructs.
------

snip

Sequence error here, I think (I'm skimming code, not picking through
it
in detail --- but setting the bookmark is equivalent to navigating to
a
different record using the mouse or keyboard; and all bound controls
will be reset to the values of the record just navigated to -- losing
any changes you justy attempted to make.
------
The purpose of navegating to another record is to implement the
changes
Made and I think I have it working correctly in the code provided
above.

My inexperience is showing here. Come to think of it, Access does commit
pending changes when you navigate away from a record.

I don't know what would be considered 'best practice' in this case:
explicitly committing the changes (Me.Dirty = False) or assuming that
the implicit behavior will a) not change in the future and b) will be
understood by the poor soul who in the future has to make sense of your
code.

Also, if navigating away from the current record is not required the
explicit committment would require less I/O overhead than unnecessary
navigation.
------

snip

--
Clif
Still learning Access 2003