MS Access - autofilling a form field from a previous record

0 Votes

MS Access - autofilling a form field from a previous record

I am creating a risk register set up as follows;

Table 1: Basic risk data
Table 2: Risk assessment scores
Table 3: Actions and risk controls

There are many risk assessments (Table 2) attaching to each risk (Table 1). Each risk assessment record is an updated version of the previous one.

I would like to do three things;

1. When a user adds a new risk assessment, copy forward the scores from the old record to the new record to give users a head start on completing the form.

2. I would also like to copy forward the "current score" from the old risk assessment to the "previous score" field of the new risk assessment. (This is required so I can easily compare the previous and current scores, and hence gauge whether the risk is stable, increasing or decreasing. If I don't pull the two scores into the same record then I would need to compare them across two separate records which would have to be filtered, and this sounds more difficult).

I guess I would also need a way of identifying the most recent risk assessment relating to that particular risk in order to pull the data through from it.

3. I need to automatically set the status to "current" in the new risk assessment and, at the same time, set the status in all prior risk assessments for that particular risk to "archive".

Ideally I'm looking for a solution based on queries and expressions or macros as I have no VB knowledge at all.

I hope I'm not asking the impossible, but thanks a lot for your advice.