Thanks Cliff, you've been a big help.
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
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
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
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
=======================================================
OH YES, this did start with just a simple question, didn't it. I have
some
thing that almost works, but I am certain that it is written poorly.
While
I am ashamed to show it, I need to if I am going to get this finished,
so
here goes..
I've noted my problem with comments on the problem lines with <<
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.
I'm going to flag comments below with **************
------
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
------
Option Compare Database
Option Explicit
------------------------
**************
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 !!
------
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError
Me.RiskDescipt.SetFocus
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
**************
Whenever you create an object, I gather that it is good programming
practice to be sure that you release the reference when you are finished
with it, by use of an explicit Set (object) = Nothing before you exit
the proc: Set rst = Nothing. [The topic of memory leaks comes to mind.]
Question for more experienced developers: in this case, where this
recordset object will (resumably) be used multiple times during each
instance of the form being opened for use would it be better (more
efficient?) to move the Dim rst As Recordset to the module level, the
Set rst = Me.RecordsetClone to the form load event, and the Set rst =
Nothing to the form unload event?
Dim strSearchName As String
' Lb = LowerBoundry will always be 0
If Me.ID = 1 Then
Me.RiskDescipt.SetFocus
Exit Sub
End If
' Records can not be added, and the Lb of 1 and 11 can not be changed
If IsNull(Me.ID) Or Me.ID = 1 Or Me.ID = 11 Then
strSearchName = 1
Else
strSearchName = Str(Me!ID) - 1
End If
**************
strSearchName = Str(Me.ID - 1)
Some observations: When comparing string data types (If, FindFirst, etc)
the comparison is done byte by byte, but numeric comparisons are done in
a single operation. In general it is better to use a numeric data type
for "lookup" fields (keys, indexes, etc.) so I'd suggest making ID an
integer instead of string.
Formatting of strings can be a huge issue in matching: leading zeroes
and/or spaces, for instance.
Sometime search these archives for discussion on when to use DOT (Me.ID)
(vs) when to use BANG (Me!ID). There is quite informative discussion
out there, and good advice on the various benefits of using one over the
other when either syntax will work. They are not identical. One
difference is that the DOT syntax is recognized by Intellisense (control
names can be selected from a dropdown list - helpful with longer names)
and the compiler validates the control name; whereas a mis-spelled
control name following a BANG will not be detected by the compiler.
You might set the bookmark first, and SetFocus second.
------
Found the right way to use .bookmark (I THINK)
rst.MovePrevious
Me.Bookmark = rst.Bookmark
------
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & strSearchName
Me.RiskDescipt.SetFocus
Me.Bookmark = rst.Bookmark
***************
Consider using:
With Me.RecordsetClone
.FindFirst "ID = " & strSearchName
Me.Bookmark = .Bookmark
End With
Me.RiskDescipt.SetFocus
------
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
------
And doing away with the whole issue of creating and annihilating the rst
recordset object. (So throughout your code.)
***************
Hmm ... this setfocus overrides RiskDescript??
------
Maybe LEFTOVER FROM some testing, will look at this, thanks.
------
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
------------------------
Private Sub Ub_GotFocus()
On Error GoTo ProcError
' Prevent changes to Ub of record IDs 10 and 11
If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then
Me.RiskDescipt.SetFocus
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
------------------------
Private Sub Ub_AfterUpdate()
Me.CreateDate = Now()
End Sub
------------------------
Private Sub Ub_LostFocus()
On Error GoTo ProcError
If IsNull(Me.ID) Or Me.ID = 10 Or Me.ID = 11 Then GoTo ExitProc
Dim rst As Recordset
Dim fld As Field
Dim strSearchName As String
Dim bmk As Object << I'm doing something wrong here, not
yet
sure what.
***************
Try Dim bmk As Variant
Me.ctl_prvUb = Me.Ub
Me.ctl_prvID = Me.ID
Set rst = Me.RecordsetClone
strSearchName = Str(Me!ID) + 1
**************
strSearchName = Str(Me.ID + 1)
Set bmk = rst.Bookmark << wishing to capture the current bookmark
rst.FindFirst "ID = " & strSearchName
Me.RiskDescipt.SetFocus
If rst.NoMatch Then GoTo ExitProc
Me.Bookmark = rst.Bookmark
If Me.ID = 1 Then
Me.ctl_prvUb = Me.Ub
Else
If Me.ID = (Me.ctl_prvID + 1) Then
If Me.Lb <> Me.ctl_prvUb Then
Me.Lb = Me.ctl_prvUb
Me.CreateDate = Now()
Me.Bookmark = bmk << so the changes made are
recorded
************
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.
------
End If
End If
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdEndDate_Click..."
Resume ExitProc
End Sub
***************
I didn't try to repeat myself when I saw a repeat of an issue I'd
already commented on.
HTH!
