Validating an entry with a specification table 1

[InfoNow]

How do I compare text fields to a table of specifications for a certain product? I have 6 fields on my form, time1, time2, time3, time4, product, and status. when Product A is selected, the user then input 4 test results (time1 - time4), the status field should tell the user if the tests passed or failed, based on the specifications of that product from the product specification table.
btw..there are over 150 products, each with its own specs.

Thanks

 

[PHV]

Without the schema of the product specification table and the validation rules it's hard to say ...
You may consider the DLookUp function with a well formed Criteria argument.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya InfoNow . . . . .

In agreement with [blue]PHV[/blue] what the criteria for validating against your Spec Table?

See Ya! . . . . . .

 

[KenReay]

Hi

How about:

A (5) column combo box where the use enters the productId, if you set linit to list true on this combo, this ensures valid product code is entered, also via comboboxname.Column(1), comboboxname.Column(2)...etc you have access to the specification columns, and can perform appropriate checks in the save operation of the form, that way no need for any additional DLookups etc, but at PHV says without more detailed info on your design it is not possible to give a more detailed answer

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[InfoNow]

Thank you for the fast reply....
So far, I have 3 tables. The tblMain table contains Part#, mfgLot#, phaselot#, phase. There are other fields, but these make up the primary key. The tblTests table contains Part#, mfglot#, phaselot#, phase, machine#, operator, TestDate, time1, time2, time3, time4, timestatus..etc. the relationship is one-to-many between these tables. The tblSpecs table contains Phase, RangeMin, and RangeMax. This is a one-to-one relationship to the tblTests table. One product can have many tests because if it fails, the user must rework the product and test again. The specs are pretty straight forward. There's a low end and a high end. Anything within this range, the product passes. And it fails, if it is out of range.

I created a main form with the fields from the tblMain and a subform with the fields from the tblTests. The subform is in a datasheet view so I see multi tests for a particular product.

This is what I have in the On-Enter Event of the TimeStatus field. It doesn't work, but I was trying anything that I can think. I just hard code the specs in there, cause I have no idea how to link it to the spec table. I don't think I want to do this for 150 phases.
__________________________________________
Private Sub TimeStatus_Enter()
If (Phase = "07") And (Me![Time1] < 2 Or Me![Time1] > 5) And (Me![Time2] < 2 Or Me![Time2] > 5) And (Me![Time3] < 2 Or Me![Time3] > 5) And (Me![Time4] < 2 Or Me![Time4] > 5) Then
MsgBox "At lease one test value is out of spec. Test has failed.", vbExclamation, "Test Failed!"
Me!TimeStatus = "failed"

ElseIf (Phase = "08") And (Me![Time1] < 3 Or Me![Time1] > 6) And (Me![Time2] < 3 Or Me![Time2] > 6) And (Me![Time3] < 3 Or Me![Time3] > 6) And (Me![Time4] < 3 Or Me![Time4] > 6) Then
MsgBox "At lease one test value is out of spec. Test has failed.", vbExclamation, "Test Failed!"
Me!TimeStatus = "failed"

Else
Me!TimeStatus = "passed"
End If
End Sub
________________________________________


Thanks in advance for the help

 

[PHV]

I'd code like this:
If (Phase = "07") And (Me![Time1] < 2 Or Me![Time1] > 5 Or Me![Time2] < 2 Or Me![Time2] > 5 Or Me![Time3] < 2 Or Me![Time3] > 5 Or Me![Time4] < 2 Or Me![Time4] > 5) Then


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[InfoNow]

Thanks PH...

That works great...I was so close....

How can I grab the specs from the table instead of hard coding them into the event procedure? It would be nice because sometimes the specs change and user can change it themself instead of calling me.

 

[PHV]

Quick and dirty:

Private Sub TimeStatus_Enter()
Dim intMin As Integer, intMax As Integer
intMin = DLookUp("RangeMin", "tblSpecs", "Phase='" & Phase & "'")
intMax = DLookUp("RangeMax", "tblSpecs", "Phase='" & Phase & "'")
If Me![Time1] < intMin Or Me![Time1] > intMax _
Or Me![Time2] < intMin Or Me![Time2] > intMax _
Or Me![Time3] < intMin Or Me![Time3] > intMax _
Or Me![Time4] < intMin Or Me![Time4] > intMax Then
   Me!TimeStatus = "failed"
Else
   Me!TimeStatus = "passed"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[InfoNow]

Wow PH!!!You are awesome....This works grrrreat!!!
I wish I can buy you a cold beer!!
Thank you so much for your help.