Conditional formating of two forms in Access 2010

[TimTDP]

I have two forms, frmSuggestedStock & frmActualStock

frmSuggestedStock is bound to tblStock
frmActualStock is bound to qryStockConsumed

I cannot have a single form using a query because the user needs to be able edit records in frmSuggestedStock.

Sample records in frmSuggestedStock include
ProductId,SuggestedStockLevel
1,10
2, 5
3, 9
4, 1

Sample records in frmActualStock include
ProductId, ActualStockLevel
1, 15
2, 3
3, 9

I would like to introduce conditional formatting to the forms to change the back color of fields SuggestedStockLevel & ActualStockLevel

For example
frmActualStock
back color of field ActualStockLevel for ProductId 1 should be green (excess stock)
back color of field ActualStockLevel for ProductId 2 should be red(short stock)
back color of field ActualStockLevel for ProductId 2 should be white(correct stock)

frmSuggestedStock
back color of field ActualStockLevel for ProductId 4 should be red(no stock)

Is this possible in Access 2010?

Thanks

 

[bob fitz]

Have you tried this:

Create a query using tblStock and qryStockConsumed joined by ProductId so that it shows all records from tblStock. Include the ProductId and SuggestedStockLevel from tblStock and SuggestedStockLevel form qryStockConsumed.

Bind a form to this new query. Then use Conditional Formatting of the text box controls.

 

[TimTDP]

Create a query using tblStock and qryStockConsumed.....

It is possible that either tblStock will have record (ProductId) that is not in qryStockConsumed and vice versa. A query will not return all records. I would need the query to return all records, and be editable

 

[bob fitz]

Add this field:

a: nz(DLookUp("SuggestedStockLevel","tblStock","ProductID = " & [ProductID]),0)

to qryStockConsumed

Create a query based on tblStock and add this field:

a: nz(DLookUp("ActualStockLevel","qryStockConsumed","ProductID = " & [ProductID]),0)

to the query

Bind the forms to the new queries and create a new text box on each form, bound to the new field ("A")

You can then set the conditional formatting for the text box "SuggestedStockLevel" and the text box "ActualStockLevel", testing each against the new text box "A".

I have attached a sample db.

 

[TimTDP]

Thank you very much