How would I write VBA code that automatically fills out one form field based on inputs entered into other various fields (in real time) but have the various field inputs update said field based on a priority? (clearer example inside!)

Best Microsoft Access Programmer Portland OR

Hello!
I have a question about how to set up some VBA code on an Access form. So I want to lock a field on a form for control reasons. This field is to display the Status of the record. There are 9 other fields that are all intended to have a number in them, if applicable. Let’s say these fields are titles as follows on the form:

Q1HIGH, Q1MEDIUM, Q1LOW
Q2HIGH, Q2MEDIUM, Q2LOW
Q3HIGH, Q3MEDIUM, Q3LOW

Now, all of these fields are to have a number entered into the fields depending on how the record is worked by the end user. I want the Status field that I want to lock from manual editing to automatically update based on which of the fields have a # greater than zero in them.

If there are only ‘low’ #’s present on the form (Q1LOW, Q2LOW or Q3LOW), I would want the Status field to reflect “Low Risk”.

If there were any ‘medium’ #’s filled out on the form, I would want the Status field to change to “Medium Risk”. So if there was a mix of low and medium #’s, the presence of #’s in the medium fields would trump the low-risk status.

If there were any ‘high’ #’s filled out on the form, I would want the Status field to change to “High Risk”, regardless of whether there are also ‘medium’ or ‘low’ # fields filled out.

Does this make sense? It would be great if adding or deleting of inputs in these fields would update the Status field in real time.

My head feels like it’s going to pop trying to wrap my head around this. Can anyone give me a jump start on how this would look in VBA code? Or would/could I do this with conditional formatting?

submitted by /u/MrManager689
[link] [comments]

Microsoft Acess Developer Portland OR