Validation Rule or code

[DRMOB]

I have a status combobox that has a few choices (Pending, Approved,
Completed, Withdrawn). On initial entry the status is Pending. I also
have a subform that totals up the cost approved of items requested. Only if
the cost approved is >0, do I want the user to be able to change the
cboStatus to Approved or Completed; the status could be changed to withdrawn
no matter what. Do I put this as a validation rule or code it elsewhere?
Thanks in advance. You are always helpful.

 

[Marshall Barton]

I have a status combobox that has a few choices (Pending, Approved,
Completed, Withdrawn). On initial entry the status is Pending. I also
have a subform that totals up the cost approved of items requested. Only if
the cost approved is >0, do I want the user to be able to change the
cboStatus to Approved or Completed; the status could be changed to withdrawn
no matter what. Do I put this as a validation rule or code it elsewhere?

A validation rule is not the right tool for this because it
spans multiple tables and forms.

I think it would be better to not even provide those choices
until the condition has been met. This can be done by using
two different queries for the combo box and modifying the
combo box's RowSource as needed.

You didn't say how the row source is currently set up, but
it should at least be a table that looks something like

tblStatus:
ID Integer (primary key)
Descr Text
Allow0 Yes/No (True for Pending and Withdrawn)

Then a query like can be used to to provide the appropriate
choices.

SELECT ID, Descr
FROM tblStatus
WHERE (Forms.mainform.subform.Form.txtTotalApproved > 0) OR
Allow0 = (Forms.mainform.subform.Form.txtTotalApproved = 0)
ORDER BY ID

Then all you have to do is requery the combo box in its
Enter event procedure:
Me.cboStatus.Requery

Not sure what you might want to do if the costs are edited
so that the total goes from >0 to 0