Guidance Needed

[DSProductionz]

Hello all,

I am fairly new to Access database design and looking for some guidance.

I have designed a form that is for testing specification requirements. For example...

I have a form that is asking for the following:

Strength 1
Strength 2
Strength 3
Strength 4
AVG Strength (calculated field based on Strength 1-4)

Which is all working as I want.

What I am trying to do

I want access to look at the AVG Strength field against another table for a specification for a particular Strength. If the AVG Strength passed the required criteria, I would like to display a PASS label on the form. If it fails the criteria check, I would like to display a FAILED label on the form.

So, I guess there are two things I am look for help?

1.) How to check against my specifications table and see if the AVG Strength passes or fails?

2.) How to get a label to display depending on the outcome. (PASS / FAILED)

The form is tided into a main table with lots of other columns which are not relevant at this time, the fields that are relevant would be:

MAIN Table
TestID
Test Code
Strength 1
Strength 2
Strength 3
Strength 4
AVG Strength (calculated field)
SpecID (linked via relationships)

SPECIFICATIONS Table
SpecID (linked via relationships)
TestCode
Strength Specification

So my idea if this....

I enter my 4 strength values, access calculates the average of the 4 strengths tested. Once the average is calculated, I want access to check the specifications table based on the TestCode. If the TestCode entered on the form matches that of be in the Specifications table, look at the strength specification and whether it passes or fails display a corresponding label on the form. I would assume we would be using something like a vLookup or dLookup here? Just not sure where to start here....?

How would I go about doing this? I am very new to the VBA programming side and not sure which route to take? Some guidance would be great. Thank you in advance.

[pbaldy]

Since the tables can be joined on SpecID, create a query that joins them together and returns the desired fields. You can do the math in the query or on a form/report.

[DSProductionz]

Can you be a bit more specific on how to do this? How can I get the labels to display as well?

[pbaldy]

In a new query field:

PassFail: IIf([AVG Strength] >=[Strength Specification], "Pass", "Fail")

Using your actual field names. By the way, spaces and symbols in field names are more trouble than they're worth in the long run.

[DSProductionz]

Thanks for the response once again. First off, let me let you that I do actually name my fields as such... Example: ID, FirstName, LastName, Email, PhoneNo, etc... I never use spaces or underscores.

Anyway, I am a bit confused as to how things will work. I guess the bigger picture is this...

Testing different yarn strengths from different suppliers. There is a table that contains all of the YarnCode and Strength specifications for all the different yarns.

Then the main table is what captures the yarn strength data and does the AVGStrength calculation. Once the average is calculated. The user also puts in the YarnCode which needs to check the other table for that YarnCode and then compare the Specification with the AVGStrength allowing for +/- 5% difference to the Specification.

If the yarn AVGStrength passes, then display a message on the form that says 'PASS' but obviously if it fails then "FAILED' displays.

I am sort of lost as to where I need to do these things? Hoping for more in-depth of an explanation or even point me to resources I can read / watch that explain these things.

I can set up tables and relationships, create basic queries, forms and reports. I am just entering the more complex side of things and need some direction.

So anyone that could help with this would be great. Thanks again.

[pbaldy]

Have you used VBA code? It sounds like you need to do this test at some point during data entry, tied either to a button or the after update event of a textbox. You can use DLookup() to get the specification.

[DSProductionz]

I'm just getting involved in VBA and coding actions etc. Hence this post. Just looking for guidance and direction.

[pbaldy]

If the fields are both on the form, a textbox control source like:

IIf([AVG Strength] >=[Strength Specification], "Pass", "Fail")

If you wanted to do it in a specific event, the VBA would look like:

If Me.AvgStrength >= DLookup(...) Then
  Me.LabelName.Caption = "Pass"
Else
  Me.LabelName.Caption = "Fail"
End If

That further assumes you need to look up the specification.

[Micron]

Dsproductions: Could be done without vba but I think one of the textboxes would have a convoluted expression with nested IIF statements since it's not a simple matter of being >= . That is, I see you have a 'between' situation with a 5% variance. If I may borrow from pbaldy, I think you might need something like

Dim dblAvg As Double, dblSpec As Double
dblSpec = DLookup(...)
dblAvg = Me.AvgStrength

If dblAvg >= (dblSpec * .95) And dblAvg <= (dbSpec * 1.05)
  Me.LabelName.Caption = "Pass"
Else
  Me.LabelName.Caption = "Fail"
End If

By the time you read this, I will likely be unavailable for a few days.