Just Plain Need Help

[Daryl]

I have two related tables with a one-to-many relationship. In Table A I have a field called IsValid. In Table B I have two fields: Eff_dt and Exp_dt. In other words, if a part is being used in any of severable products, then IsValid is true else it should be false.

Here's what I think I need to do. Check each related record in Table B as follows:
a. If Eff_dt is not null and Exp_dt is null then the IsValid field is true .... stop checking any other records.
b. If Exp_dt is not null then IsValid is false check next record (go to step a).
c. If Eff_dt and Exp_dt is null then IsValid is false ... check next record (go to step a).

This looping (possibly with arrays to check each record) makes my head spin. I think I only need to concentrate on step a. but I could be wrong.

Any ideas (code would be helpful) how I can do this? What event property should the code go in?

 

[Fornatian]

Why don't you use an Update Query with criteria?

 

[Daryl]

Thanks for the suggestion Fornatian. Unfortunately that won't work entirely. Consider that I choose to update IsValid to false using and update query. This means there are no products using Part A. Later, a correction is made to Product C deleting an erroneous Exp_dt entry. Now the IsValid is for Part A is true (its now being used in a product) but the update query would not change it back. I would need to create another update query to change IsValid to true. But that may have the same problem but in reverse. I'm thinking that some If..then code would be a better approach.

 

[Pat Hartman]

It is generally incorrect not to mention dangerous to store calculated values and "IsValid" falls into that category. It is similar in concept to storing the sum of the order details in the order table. If you change something in the order details, that invalidates the stored sum in the order table. Your case is the same, if the effective date/expiration date change for one of your detail parts, that could invalidate the value of IsValid.

If what you want is to show an IsValid flag on the main form, you can use a DCount() to count the related many-side rows where Eff_t is not null and Exp_dt is null. If the count is > 0, IsValid is true otherwise it is false. Of course, you would need to requery this textbox whenever the AfterUpdate event of the subform runs to pick up any changes in status.