Referencing Across tables and Subforms

[Kozbot]

I need my quality control database to inform the user if they are entering out of specification data and prevent it from being entered unless flagged as out of spec. Each product in my database has different quality tests that must be performed with different specifications for each product.

Here are my relationships
http://i.imgur.com/ch8bnVa.png

I have a form attached to the QCEntries table with QCTestReslults subform.

I need code to do the following: in the afterupdate event of the TestResult's control, the code must check the entered value against the minvalue and maxvalue FOR THAT PRODUCT CODE AND TEST found the in the QCSpecs table. I need a message box to pop up informing the user that the value is out of spec.

I have an "idea" of what I need to do but I'm having trouble getting started and referring to the subform.

Any help is appreciated

 

[Isskint]

I would suggest creating a query usingQC Specs, QCTest and QC2TestResults tables. Put a criteria against QC2TestResults.TestResult = YourForm.ControlName. Then in that controls BEFORE UPDATE event enter code to lookup the min and max values and apply your criteria. A rough eg

varMax = dlookup("MaxValue","qryCheck","TestResult=" & me.ControlName
varMin = dlookup("MinValue","qryCheck","TestResult=" & me.TestResult
if me.TestResult > varMax OR me.TestResult < varMin then
msgbox "opps"
me.undo
exit sub
end if
do something else

 

[Kozbot]

I would suggest creating a query usingQC Specs, QCTest and QC2TestResults tables. Put a criteria against QC2TestResults.TestResult = YourForm.ControlName. Then in that controls BEFORE UPDATE event enter code to lookup the min and max values and apply your criteria. A rough eg

varMax = dlookup("MaxValue","qryCheck","TestResult=" & me.ControlName
varMin = dlookup("MinValue","qryCheck","TestResult=" & me.TestResult
if me.TestResult > varMax OR me.TestResult < varMin then
msgbox "opps"
me.undo
exit sub
end if
do something else

I'm confused about how you are designing the query. The criteria is going under TestResults?

 

[Isskint]

Sorry, I misunderstood your question.

You want to check the value returned for a specific test, against the Min & Max values entered for that test?
So you do not need the query. Just use the dlookup's on the QCSpecs table to get the relevant Min and Max values for the current test. Then carry out the conditional test on these values and the test results.

 

[Kozbot]

Sorry, I misunderstood your question.

You want to check the value returned for a specific test, against the Min & Max values entered for that test?
So you do not need the query. Just use the dlookup's on the QCSpecs table to get the relevant Min and Max values for the current test. Then carry out the conditional test on these values and the test results.

The QCSpecs table is set up like this

Product   TestName    MinValue   MaxValue
AB-100   CarbonBlack    10            15
AB-100    MFI              10            20
AB-200   CarbonBlack    16            20

the code must check the value entered in QCTestResults against the min and max value for BOTH the appropriate product and test name. I know how to do this for one condition but the code must select for BOTH the product and TestName.