Thanks for the pointers - much appreciated!
At this stage the data is being maintained and updated in a single file geodatabase, but it is envisioned for it to eventually migrate to a DBMS environment, which I imagine is definitely something to keep in mind when setting up fields.
I will have a look at both options (the VBA code and Python script idea) and see which suit the situation the best (perhaps even a combination of the two?). From what I can remember offhand its possible to call a Python script from within VBA code, though I could be wrong.
My VBA and Python skills are at a somewhat beginner-intermediate level at this stage, so I will give it a go and see what comes out of it. I'll post back with any code/problems, in case other people are having similar troubles/questions 🙂
Cheers,
Maya.