Validate control before status is updated

[Ruth]

I need help tweaking setup for a CancelEvent validation and message
box. I'm working on a legacy Access 2000 database.

On the Job Entry form, there is a control [CompressorType] that
indicates whether equipment is a rental (1), from the maintenance co.
(2) or the service company (3). In order to prevent leaving rental
equipment on site, I need to add a validation that checks first to see
if a rental compressor was used. If it was ([CompressorType]=1), then
it needs to validate that something (no way to validate specific
entries) was entered in the [CompRetConf] field (which stores the
Return Confirmation number). If a user tries to change [JobStatus] to
"Completed" or "Canceled"and [CompressorType]=1 and [CompRetConf] is
null, the update should be canceled, revert to original value before
the edit was attempted and have a message box display that tells the
user that they have to enter a return confirmation number before they
can change the status to "Completed" or "Canceled."

I tried this using a macro as I'm not too great at writing multiple if
statements in VBA.

My condition is:
[Forms]![FrmJobEntry]![JobStatus]="Completed" And [Forms]!
[FrmJobEntry]![CompressType]=1 And [Forms]![FrmJobEntry]![CompRetConf]
Is Null
Action is:
CancelEvent.
Next line is MsgBox with text "You must enter a Rental Return
Confirmation number before you can mark a job "Complete."

This "sort of" works.

I also added conditions that stopped the macro if [JobStatus] was
anything other than Canceled or Completed.

#1 thing wrong: Trying to change [JobStatus] to "Completed" when the
conditions are met invokes the message box but doesn't revert to the
previous value. There are 4 other types of status for jobs, so how
would I get it to go back to whichever it was before until the user
makes the appropriate changes to the record?

#2 thing wrong: The message box pops up whenever I try to change the
status to ANYTHING-- not just "Completed."

Help!

Thanks,
Ruth

 

[Steve Schapel]

Ruth,

What's missing in your schema is an RunCommand/Undo action.

Also, in a macro, if you want the same condition to apply to multiple
actions, as is the case here, you need to enter an ellipsis ... in the
Condition column for each action that the condition applies to.

By the way, since this macro is already running on the FrmJobEntry form,
you don't need to specify. You could simplfy the Condition to:
[JobStatus]="Completed" And [CompressType]=1 And [CompRetConf] Is Null

I assume you have this macro assigned on the Before Update event of the
form, or some such.

 

[Ruth]

Thanks for your reply, Steve! Unfortunately I tried that already and I
get an error message telling me that the command is not available. I'm
figuring that it can't execute the undo event since it would be part
of the "Before Update" event. Since the record hasn't been changed, it
can't be undone.

I'll try to think my way through this, but if you or anyone else have
other suggestions, I'd appreciate them!

Ruth

 

[Steve Schapel]

Ruth,

No, that is not correct. The RunCommand/Undo action reverts the data to
the old value. The form's Before Update event is the appropriate place
to have it. Try putting it in the macro before the CancelEvent action.