Dynamic display in Access Form - possible?

[blaze77]

Hi,

I'm somewhat of a power user in excel and a newbie to Access though the possibilities are exciting me :-)

I am trying to create a tool in Access to replace an existing tool in Excel. My question is whether I will be able to replicate some key features that I was able to do in Excel.

The Excel tool was for operators in my company to put in records of Quality Control tests in production. Since we have a lot of complex tests, I was able to make it much easier for them with some excel features:
1. In Excel, my form dynamically displays the 'Acceptance Range' and 'Instructions' next to each field. (These are specified in TableX depending on the type of product being made) Can a form in Access also dynamically display information from a table based on some pre-selected criteria?
2. when they enter a test result it immediatelly turns red or green depending whether the result is acceptable or not (by checking whether it falls within the 'Acceptance Range' of TableX). If I create a form in access, is there anyway of creating some visual feedback after each field (test result) is filled even before the form is submitted?

The operators in my company keep the current Excel file open all day long and depend on it to flag them if product is getting into the danger zone and needs to be quarantined. But the problem is that with an excel tool, one cannot create reports across different files that the leadership can review. I am hoping that by migrating to Access, I will be able to create the reports I need. But I don't think operators will accept it if it doesnt give them instant feedback like they're getting now.

I am really hoping somebody will say this is possible in some way. Any help on how to do this is GREATLY appreciated. :-)

Mathew

[ADezii]

Form what you are saying, it would appear that this could easily and more efficiently be accomplished within Access. For starters, I'll take a guess as to which Fields comprise TableX, just tell me which ones are correct/incorrect, then fill in the missing blanks.

[Product] -{TEXT 50}
[Instructions] - (Probably {MEMO})
[HI] - (Hi value of Acceptable Range) - {NUMERIC - specific Type Unknown}
[LOW] - (Low value of Acceptable Range) - {NUMERIC - specific Type Unknown}
[Test Results] - {NUMERIC - specific Type Unknown}

[blaze77]

Form what you are saying, it would appear that this could easily and more efficiently be accomplished within Access. For starters, I'll take a guess as to which Fields comprise TableX, just tell me which ones are correct/incorrect, then fill in the missing blanks.

[Product] -{TEXT 50}
[Instructions] - (Probably {MEMO})
[HI] - (Hi value of Acceptable Range) - {NUMERIC - specific Type Unknown}
[LOW] - (Low value of Acceptable Range) - {NUMERIC - specific Type Unknown}
[Test Results] - {NUMERIC - specific Type Unknown}

---------------------------------------------------

Thats almost exact.
-TableX does not contain [Test Results]. That is filled into the form and goes into another table.
-it does contain an additional field; [Test Type]. We have some 30-40 different tests for each product and the acceptance range and instructions are different for each.

thanks for the quick response ADezii :-)

Mathew

[ADezii]

---------------------------------------------------

Thats almost exact.
-TableX does not contain [Test Results]. That is filled into the form and goes into another table.
-it does contain an additional field; [Test Type]. We have some 30-40 different tests for each product and the acceptance range and instructions are different for each.

thanks for the quick response ADezii :-)

Mathew

Just checking in Matthew, will report back later and see if we can get you started.

[blaze77]

Just checking in Matthew, will report back later and see if we can get you started.

thanks ADezii, Let me know if there's anymore information I need to add on.

Mathew

[ADezii]

thanks ADezii, Let me know if there's anymore information I need to add on.

Mathew

There is 1 point that I must get straightened out before we proceed. You state that 'Acceptance Range' and 'Instructions' depend on the specific Product. Shouldn't the HI and LOW Acceptance Values for a Range be dictated by the Tests involved and not the Product? In your scenario, unless I misunderstand it, if Product A has 20 Tests that can be associated with it, each of the 20 Tests will have the same HI and LOW Acceptance Values. Is this correct?

I would also like to see some Raw Data such as: 3 Product Names, associated Instructions for each of those Products, depending on what your answer in the previous Post is, HI and LOW Acceptance Values defining the Range for the Product or Test, 6 Tests which would be appropriate for each of the 3 Products along with the Test Type information.

Since multiple Tests can be performed on a single Product, I am envisioning a Product Table with a 1 to MANY Relationship with a Test Table, but this Acceptance Range is still clouding the issue for me.

[blaze77]

@ADezii

Sorry, I was not completely accurate earlier. Let me try to do a better job :-)

Acceptance Range - This depends on both 'Product' & 'Test Type'. Initially the Hi/Low is set by Test Type. This automatically sets the default values for all Products. Later depending on customer feedback, it is adjusted individually for each Product.

Instructions - These are dependent on Test Type only

There are other variables as well, but the ones we've talked about are the most important.

I've attached an excel file with a simplified version of the raw data in TableX in Excel. It has three products and a variety of rests.

Please let me know if I need to add on anything more. I appreciate the time you're putting in here.

Mathew

[ADezii]

Blaze77, I have the Excel File and I'll try to have a look tomorrow and see what is going on, and if I can, I'll try to arrive at some sort of solution for you. Kindly be patient.

[ADezii]

@blaze77
Hello Matthew, I had a chance to look at the Excel File and also to re-read this Thread. There is one that that you have to realize, it is very difficult sometimes to try to interpret the request for a Thread at the other end of a Web Page, and this is one of those times.

That being said here is what I've come up with so far. A specific Test can be applied to several Products and a single Product can have multiple Tests performed on it. This is what we call in Access the dreaded 'MANY to MANY' Relationship, and it cannot be directly supported. What will be needed here is an Intermediate Table that will contain only 2 Fields: the Primary Key of the Test Table (TestID) and the Primary Key (ProductID) of the Products Table. 'Together' in this Intermediate Table will these 2 Fields comprise a Primary Key, and now can Link back to their respective Tables. Download the Attachment that I have supplied. The Test Database will open and display the Relationships Window to give you a visual display of what I have just said.

There are a few other Items which I will need clarification on, occasionally referring to the Excel Sheet:

1. The Default Value for a Test is overwritten by the Default Value for the Product on which it is applied. Is this correct? For example, if you were to apply the Slide Test (Face to Back) for the S11Paper Product, your HIGH would be 12 and your LOW would be 20 because these are specified within the Product. Is this correct?
2. Given the same Test/Product Combination (Slide Test for S11Paper), how can the Test have a Default LOW of 14 and HIGH of 25, while these same Defaults for the S11Paper are 12 HIGH and 20 LOW (LOWER Range > HIGH)?
3. Aside from these questions being answered, here is the logic as I see it:
   1. The Operator selects a Test to be performed on a Product.
   2. Check and see if the specific Test is applicable for the Product, if it isn't notify the Operator and get outta Dodge!
   3. If the Test is applicable, check and see if Default HI/LOW Product Values exist for the specific Test/Product combination. Use the Product values first, then revert back to the Test Defaults in the event that either 1 or both are not specified for the Product.
   4. Once the appropriate HIGH/LOW Values have been determined, check the Test Results Value against this Range as it is entered into a Field on a Form? If it is within the Range, Green Light it, if not Red Light.
   5. Forget the Results Table for now, I'm sure that this is enough to digest.
   6. Don't forget to view the Attachment.

P.S. - The System will not allow me to Upload a 12KB Zip File, I'll check in with the Admin and get back to you ASAP.

[ADezii]

@blaze77, I am once again attempting to send you the Attachment referred to in the previous Post. Got it!

[blaze77]

@ADezii
Thanks ADezii,

I've got the file. It seems that the many-to-many relationship between TestID & ProductID is defined in TblTestProduct. That makes sense.

To answer your questions:
1. The default values for a Test never get overwritten unless manually done so. When a new Test is added, all Products use the Test default until they are changed, (but the Test default values stay the same). That way when a new Product is added, it will use the original Test default as well.
2. So for the Slide Test, the default is 14-25 (Low-High and this will not change). For the Product: S11Paper, this has been updated to 12-20 (not 20-12 which was my typo. Sorry :-))
3. slight correction below, otherwise you've accurately described the process:
C: Use Product Values ONLY. If they do not exist, then they have been deleted for that product. Operator will never use the Test default values (Test values are only used as references to setup new products)

With the structure you've created for me, I'm going to go work on building on top of it. I'm looking forward to what direction you point me in on how to create dynamic forms (dynamic hi/low/instructions/ redlight/greenlight)

I don't know which part of the world you're from, but incase you do celebrate Thanksgiving on the 27th, hope you and your family have a wonderful time! :-)

Mathew

[ADezii]

Hello Blaze77, I need to know how many more typos there are since I have no actual way of knowing what the single value missing from a HIGH/LOW pair is. Can you send me a corrected Spreadsheet free of typos, then I can proceed? This is a critical step.

If an Operator wanted to perform a Gloss Test on either 1 of the 3 Products, how could he/she? The Test is applicable to all three Products, but there is a critical Value, either HIGH or LOW missing for each of the Products. Would you simply notify the Operator that the Test cannot be performed, etc.?

Once we get these couple of issues ironed out, we should be able to proceed, but at this point I can do nothing further until I get these resolved.

Have a nice Holiday!

[blaze77]

@ADezii
H ADezii

I have attached the spreadsheet again after reviewing it. This is accurate.

Any missing values that you see are intentional. In the Gloss Test example, there is no High because the higher the gloss, the better it is for the customer. So a test result below the 'Low' will trigger a red-light, but it can go high without any restrictions.

Blaze77

[ADezii]

@blaze77
Got it, will try to look at it as soon s I can and will get back to you.

[ADezii]

@blaze77
Hello again, Blaze77. Download the Attachment and see if this Template is something similar to what you are looking for. It is simply more practical to view the Attachment, rather than post all the code. Good luck and let me know how you make out.

[blaze77]

Hi Adezii,
Just wanted to make sure I came back and said thank you! My final solution changed quite a bit as I tried to incorporate everyone's feedback, but your solution is what set up on the right path. :-)
Blaze77

[ADezii]

@blaze77
Glad it all worked out for you, Blaze77.