Event procedure is not working

[Guest]

I have developed a database in Access to track support issues. I have created a data entry form which has a field called CompletionDate and another field called StatusOf. When the CompletionDate is not empty I want the StatusOf text box to change to "Closed". When the form is closed without entering a CompletionDate I want the StausOf textBox to read "Open".

I have been working on the CompletionDate event and this is what I have but I keep getting an error 2115 "The Macro or function set to the BeforeUpdate or Validation Rule property for this field is preventing Microsoft Access from saving the data in the field."
After I clear the error off of the screen "Closed" is entered into the StatusOf field.

Private Sub CompletionDate_AfterUpdate()
If CompletionDate.Text <> "" Then
StatusOf.SetFocus
StatusOf.Text = "Closed"
End If
End Sub

 

[Rick Brandt]

I have developed a database in Access to track support issues. I have

created a data entry form which has a field called CompletionDate and
another field called StatusOf. When the CompletionDate is not empty I want
the StatusOf text box to change to "Closed". When the form is closed
without entering a CompletionDate I want the StausOf textBox to read
"Open".

I have been working on the CompletionDate event and this is what I have

but I keep getting an error 2115 "The Macro or function set to the
BeforeUpdate or Validation Rule property for this field is preventing
Microsoft Access from saving the data in the field."

After I clear the error off of the screen "Closed" is entered into the StatusOf field.

Private Sub CompletionDate_AfterUpdate()
If CompletionDate.Text <> "" Then
StatusOf.SetFocus
StatusOf.Text = "Closed"
End If
End Sub

VB Background?

Forget the Text property. In Access it represents the
changed-but-not-yet-updated value of the control with focus. That's why a
control has to have focus to be able to use it. The following will work.

Private Sub CompletionDate_AfterUpdate()

If Len(Nz(CompletionDate,""))=0 Then
StatusOf = "Closed"
Else
StatusOf = "Open"
End If

End Sub

Proper Design Rant:
If StatusOf is _always_ dependent on the existence of an entry in
CompletionDate then the field doesn't even need to exist in your table.
You can simply use an expression in queries, forms, and reports to display
"Open" and "Closed" directly from the date field. That would be the proper
way to do it. No field in a table should ever be derived from another
field in the same table.

 

[Guest]

Thanks for the Rant. I decided not to even use the StatusOf field. But I did learn about the Nz which I had not seen before.

Thanks a bunch
glg