Having tried various permutations of Before Update and well for that
matter, After Update, OnExit, OnEnter, etc. and also Locked controls,
I'm still unable to obtain the intended results. There are actually two
parts of what I'm trying to accomplish, but are interrelated. When one
part performs as it should, then upon implementation of the other,
conflicts arise and vice versa.
A form (frmEvents) is used to logged various events and one of the
controls is (EventDate) that is used when setting up the data. There
are two elements of the Event Date control that require code to prevent
inadvertent problems. One element is to disallow setting up a future
event with an EventDate that has the same date as a previous one. So on
the form's BeforeUpdate, I used the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!EventDate = DLookup("[EventDate]", "tblEvent", "EventDate = #
" & Me![EventDate] & "#") Then
MsgBox "Sorry, the event date that you have typed has already been
used. Please enter another date."
Me!EventDate.SetFocus
Cancel = True
RunCommand acCmdUndo
End If
End Sub
Well, this works, sort of, but it of course undoes all of the record
data entered (and sometimes it does not even recognize a previous date
used - strange). However, because if it is used on the control's
BeforeUpdate, then it conflicts with the second element noted below.
The second element is to prevent a user from changing an EventDate if
it is earlier than the current Date (because all data for the event has
been entered, recorded, finalized, etc.) and changing the date would
cause potential problems. I have approached this two different ways.
One using Before Update and the other using Locked record. The Locked
record code seems to work best, but it conflicts with part one's code
above. The examples are:
Private Sub EventDate_Enter()
If Me!EventDate > Date Then
If IsNull(Me!EventDate) Then
Exit Sub
Else
Me!EventDate.Locked = True
End If
End If
End Sub
Plus:
Private Sub EventDate_KeyDown(KeyCode As Integer, Shift As Integer)
If Me!EventDate < Date Then
MsgBox "Be advised that since the event has already occurred the
Event Date cannot be changed."
End If
End Sub
The other code used:
Private Sub EventDate_BeforeUpdate(Cancel As Integer)
If Me!EventDate < Date Then
MsgBox "The Event Date cannot be changed at this point because the
event event has already occurred."
Cancel = True
RunCommand acCmdUndo
End If
End Sub
Other perplexing problem is that I'm using a Long Date for the control,
but acCmdUndo does not always restore the day (i.e. Tuesday) before the
date and then there is the ensuing error message that the value entered
isn't valid for the field. At least for part one, acCmdUndo is
necessary because the user may not know what the original date was
before typing in a different one.
So need help, any assistance or suggestions will be much appreciated.
Thanks, Rolan
