I'm currently working on a database for product warranty returns, with a test results input form which will colour a field using conditional formatting based on values a user inputs in reference to max and min values from a table. The basic setup of the parts relative to this question is:
* Table "RETURNS_DATA" which generates a returns number as primary key, users input customer/product details, including selecting a product code from table "ITEMS".
* Table "ITEMS" with product codes and test min/max values
* Table "TESTS" with test results, returns number as primary key linked to "RETURNS_DATA"
* Query "TEST_VALUES" which gets return number from "RETURNS_DATA" and relevant product code and test values from "ITEMS" for that return number.
* Form "FRONTPAGE" with combo box to select primary key returns number from "RETURNS DATA".
* Form "SUBFORM_TESTS" as subform within "FRONTPAGE" to input test results into table "TESTS".
* Form "SUBFORM_TEST_VALUE_DISPLAY" as a further subform within "SUBFORM_TESTS" to display min/max test values from query "TEST_VALUES" relevant to the product code associated with the returns number.
This all works as expected and records all the relevant details referenced to the returns number primary key in "RETURNS_DATA". When using "SUBFORM_TESTS" to input test results, the form gets the correct product code and test min/max values for the product code associated with that returns number.
The issues has arisen when I have tried to use conditional formatting to colour the text boxes the user will input their results into in "SUBFORM_TESTS", with the intention for the box to turn red when out of range and green within. I have tried setting this up for the first field (Inlet Pressure) as:
Field Value Is Between/Not Between [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMIN] and [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMAX] (green and red formatting respectively)
and as value greater than/less than min max with red and green formatting.
Both of these seem to work superficially, however, what I'm finding is this:
TESTINMIN: 750
TESTINMAX: 1000
A value of 800 will display green.
A value of 500 will display red.
A value of 1200 will display red.
All good so far, right? The problem is:
A value of 80 will display green (incorrect)
A value of 50 will display red (correct)
A value of 8 will display green (incorrect).
Am I doing something wrong here? The for the test values in form "ITEMS" are set as Data Field: Number, Double and I have tried various combinations of data fields and settings. I learned to use Access myself last year to build this database for my business and it works well, but there may be something fundamental I'm naïve to here causing the issue.
Any help much appreciated!
* Table "RETURNS_DATA" which generates a returns number as primary key, users input customer/product details, including selecting a product code from table "ITEMS".
* Table "ITEMS" with product codes and test min/max values
* Table "TESTS" with test results, returns number as primary key linked to "RETURNS_DATA"
* Query "TEST_VALUES" which gets return number from "RETURNS_DATA" and relevant product code and test values from "ITEMS" for that return number.
* Form "FRONTPAGE" with combo box to select primary key returns number from "RETURNS DATA".
* Form "SUBFORM_TESTS" as subform within "FRONTPAGE" to input test results into table "TESTS".
* Form "SUBFORM_TEST_VALUE_DISPLAY" as a further subform within "SUBFORM_TESTS" to display min/max test values from query "TEST_VALUES" relevant to the product code associated with the returns number.
This all works as expected and records all the relevant details referenced to the returns number primary key in "RETURNS_DATA". When using "SUBFORM_TESTS" to input test results, the form gets the correct product code and test min/max values for the product code associated with that returns number.
The issues has arisen when I have tried to use conditional formatting to colour the text boxes the user will input their results into in "SUBFORM_TESTS", with the intention for the box to turn red when out of range and green within. I have tried setting this up for the first field (Inlet Pressure) as:
Field Value Is Between/Not Between [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMIN] and [SUBFORM_TEST_VALUE_DISPLAY].Form![TESTINMAX] (green and red formatting respectively)
and as value greater than/less than min max with red and green formatting.
Both of these seem to work superficially, however, what I'm finding is this:
TESTINMIN: 750
TESTINMAX: 1000
A value of 800 will display green.
A value of 500 will display red.
A value of 1200 will display red.
All good so far, right? The problem is:
A value of 80 will display green (incorrect)
A value of 50 will display red (correct)
A value of 8 will display green (incorrect).
Am I doing something wrong here? The for the test values in form "ITEMS" are set as Data Field: Number, Double and I have tried various combinations of data fields and settings. I learned to use Access myself last year to build this database for my business and it works well, but there may be something fundamental I'm naïve to here causing the issue.
Any help much appreciated!