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.