Conditional display in Access?

Would your form field be invisible and become visible when it meets certain criteria? If that’s your question, I have a solution.

Take, for example, a form used to enter prices of certain items. This form would default to contain the following fields:

NAME IN THE TABLE
- Article
- Cost
- Selling price
- Reason

NAME IN THE FORM
- txtArticle
- txtCout
- txtPrix
- txtRaison

But if the user enters a selling price lower than the Cost of the item, another field appears and that is the field REASON. This would be used to add a reason for selling an item cheaper than its purchase cost (for example: Promotion to destroy the competition).

First, this field must be created in the table. Then, set the control (the txtBox REASON) to invisible by default. To do this, in the properties of txtRaison, set VISIBLE to FALSE.

Then set a condition for its display. The condition is: IF the price < cost THEN SHOW REASON. In VBA this is:

IF [me.txtPrix] < [me.txtCout] THEN txtRaison.visible = TRUE

Now that we have our “formula,” Access must process it at the appropriate moment. This moment is after updating the price textbox and only if a Cost is also present.

Therefore insert our formula in the AFTERUPDATE event of txtPrix. To do this, go to the properties of txtPrix, the EVENT tab, and put the code in AFTERUPDATE.

What it will look like is:

SUB txtPrix_AfterUpdate

IF IS NOT NULL(me.txtCout) THEN
IF [me.txtPrix] < [me.txtCout] THEN txtRaison.visible = TRUE
END IF

END SUB

What happens is: after updating the PRICE field, Access checks whether the COST field is not empty. If it isn’t, it checks whether the PRICE is less than the COST and if so, it makes the REASON field visible.

There you go.

PS: You should also include this procedure in ONCURRENT so that the REASON box appears when returning to a previous record.