formsms-accesslookupconditional-formattingdlookup

MS Access Subform conditional format based on data on a table

My database has a Form view for the Lab to input the results of their tests to each batch we produce. I have a query as Subform data source and I use a date combo box for the lab guys to see the production for the day. My problem is that when they write the value of the test (say batch 5, product A, test result= 10), I need the subform to do a conditional formatting based on a min/max specification located in another table. Say this table says product A allows a range from 11-15. Then when the technician writes 10, he should be prompted to quarantine the product. One easy way is to mark the CELL in BOLD RED.

This is my Subform layout:

Product | Batch #  |  Test result

My Spec sheet is something like this:

Product | Min | Max

I tried using DLookup without success, maybe the syntax is wrong.

Any recommendations on how to tackle this?

One nice idea that came through my mind would be to have a button to automatically notify my quality guy.

Thank you.

Solution

  • Autonumber ID field in Products is set as primary key yet the Product field in Batch is text. Change to number long integer type. This is correctly done in [Product Specification] table.

    Once that field type is fixed, can include [Product Specification] table in LabBatchQuery and the specification fields will be available for direct reference in Conditional Formatting.

    SELECT Batch.*, [Product Specification].*
FROM [Product Specification] RIGHT JOIN Batch ON [Product Specification].[Product Code] = Batch.Product
WHERE (((Batch.[Production Date])=[Forms]![TNA Batch Analysis]![Today Date]));

    Change the RecordSource to simply reference LabBatchQuery instead of the SQL statement. Or instead of having dynamic parameter in the query object (I never do), put it in RecordSource SQL statement. SELECT * FROM LabBatchQuery WHERE [Production Date]=[Forms]![TNA Batch Analysis]![Today Date];

    The Conditional Formatting rules will be like: Value Is Not Between [pH Min] And [pH Max]

    Change the [Today Date] textbox AfterUpdate code to: Me.[LabBatchQuery subform].Requery

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

    Also recommend not setting lookups in tables.

    If Products and [Product Specification] have 1-to-1 relationship, why not combine as 1 table?