Help with this peice of code please

[RitchieJHicks]

I currently have the following code in my DB, so that if the user selects any
option other than "Closed", they are forced to enter a diary date.

How can I set the code so that they are not forced to enter a date when the
status is "Closed" OR "Rejected" please?

Private Sub Form_AfterUpdate()
If Me![File Status] <> "Closed" And IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
DoCmd.GoToControl "DiaryDate"
Cancel = -1
End If
End Sub

 

[Steve Sanford]

Hi Ritchie,

The code inside the IF() function will run only if all conditions are true.
The conditions you have are:

Me![File Status] <> "Closed"
Me![File Status] <> "Closed"
IsNull(Me![DiaryDate])


So the code would look like:

Private Sub Form_AfterUpdate()
If Me![File Status] <> "Closed" AND Me![File Status] <> "Closed" And
IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
DoCmd.GoToControl "DiaryDate"
Cancel = TRUE
End If
End Sub

NOTE: From "IF" to "THEN" should be on one line and the only quotes should
be around both of the "Closed"s.


I think you should move this code to the Form_BeforeUpdate() event. You
cannot cancel the "Form_AfterUpdate()" event...the record is already
savedwhen the after update event fires.

HTH

 

[Douglas J. Steele]

I'd recommend putting that code into the form's BeforeUpdate event. Putting
it in the AfterUpdate event doesn't really do much good: the record's
already been saved at that point, regardless of whether or not it should
have been.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me![File Status] <> "Closed" Or Me![File Status] <> "Rejected" _
And IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
Me![DiaryDate].SetFocus
Cancel = -1
End If
End Sub

Now, the fact that your code works at all indicates that you haven't told
Access to require that all variables be declared, since your code does not
have a declaration for the variable Cancel. It'd strongly recommend that you
remedy that situation: you can spend literally hours trying to track down
why your code isn't working if you make a simple typo on a variable name! Go
into the VB Editor and select Tools | Options from the menu bar. On the
Editor tab, make sure the checkbox "Require Variable Declaration" is
selected.

 

[Douglas J. Steele]

Oops: just noticed a slight typo (a missing closing parenthesis):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me![File Status] <> "Closed" Or Me![File Status] <> "Rejected") _
And IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
Me![DiaryDate].SetFocus
Cancel = -1
End If
End Sub

Sorry about that!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'd recommend putting that code into the form's BeforeUpdate event.
Putting it in the AfterUpdate event doesn't really do much good: the
record's already been saved at that point, regardless of whether or not it
should have been.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (Me![File Status] <> "Closed" Or Me![File Status] <> "Rejected" _
And IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
Me![DiaryDate].SetFocus
Cancel = -1
End If
End Sub

Now, the fact that your code works at all indicates that you haven't told
Access to require that all variables be declared, since your code does not
have a declaration for the variable Cancel. It'd strongly recommend that
you remedy that situation: you can spend literally hours trying to track
down why your code isn't working if you make a simple typo on a variable
name! Go into the VB Editor and select Tools | Options from the menu bar.
On the Editor tab, make sure the checkbox "Require Variable Declaration"
is selected.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I currently have the following code in my DB, so that if the user selects
any
option other than "Closed", they are forced to enter a diary date.

How can I set the code so that they are not forced to enter a date when
the
status is "Closed" OR "Rejected" please?

Private Sub Form_AfterUpdate()
If Me![File Status] <> "Closed" And IsNull(Me![DiaryDate]) Then
MsgBox "You must enter a diary date before you exit this record"
DoCmd.GoToControl "DiaryDate"
Cancel = -1
End If
End Sub