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?