I have an Access .accdb form with a query that is itself being fed by 2 different tables. (SEE ATTCHED FILES)
I have a field in the query named "IRR_HighestLevel" which resides in "Database 1" tbl (Its a ratings field that could have one of the following 4 ratings: Critical, High, Moderate or Low. Critical being the highest.)
In table 2 ("tblTPRMdb"), I have 10 fields called "EngID1" through "EngID10" that are also added to the query as well.
"Database 1" tbl also has these same name Engagement ID fields in it and it is what links both tables in the query.
Now each record visible in the form can have up to 10 different EngIDs associated with it....Each with its own "IRR_DataSecurity" rating (1 of the 4 ratings mentioned above)
For example: In the attached image, the desired rating to be returned to the "IRR_HighestLevel" field for the record in this case should be 'Critical' as it is the highest rating ...
I'm wondering if there is some VBA coding Event I could apply to one of the fields or the Access form itself (On Current event property??) with perhaps an IF statement that would automatically return/pull the highest rating from the "IRR_DataSecurity" rating fields and have it auto-populate in the "IRR_HighestLevel" field??
Hope I painted a clear picture.
Any help would be greatly appreciated.
I have a field in the query named "IRR_HighestLevel" which resides in "Database 1" tbl (Its a ratings field that could have one of the following 4 ratings: Critical, High, Moderate or Low. Critical being the highest.)
In table 2 ("tblTPRMdb"), I have 10 fields called "EngID1" through "EngID10" that are also added to the query as well.
"Database 1" tbl also has these same name Engagement ID fields in it and it is what links both tables in the query.
Now each record visible in the form can have up to 10 different EngIDs associated with it....Each with its own "IRR_DataSecurity" rating (1 of the 4 ratings mentioned above)
For example: In the attached image, the desired rating to be returned to the "IRR_HighestLevel" field for the record in this case should be 'Critical' as it is the highest rating ...
I'm wondering if there is some VBA coding Event I could apply to one of the fields or the Access form itself (On Current event property??) with perhaps an IF statement that would automatically return/pull the highest rating from the "IRR_DataSecurity" rating fields and have it auto-populate in the "IRR_HighestLevel" field??
Hope I painted a clear picture.
Any help would be greatly appreciated.
Attachments
Last edited: