Calculate date in MS Access then write value to a field in a table

Question

Following on from this question, how would I go about writing the resulting date value to the field (DEADLINE (25 WD)) which is located in a form?

(The field is located in the form which is linked to a table.)

Answer 1

The real solution is to not store this "calculation", but use it when and where required. Calculations belong to Queries not in Tables. The reason why it is best to use in Queries than storing in tables using Form method is because, when the DateOpened is changed the Deadline is automatically updated. BUT this happens only if you are using the Form to edit the information. If you are editing the date directly in the table or using a Query to Update the record, the Deadline will not be consistent.

However, if you use a query like

SELECT 
    DateOpened, 
    addWorkDays(25, DateOpened) As Deadline 
FROM 
    yourTable;

This will work based on the DateOpened, as when the Query is run. Even if it gets changed in the Query (while it is open), the Deadline will be updated accordingly. You loose this flexibility when you use a Form to store calculations back to the table.

That being said, as your need is to calculate this based on another field; I would suggest that you make use of the AfterUpdate event of the DateOpened control.

Private sub DateOpened_AfterUpdate()
    If Len(Me.DateOpened & vbNullString) <> 0 Then 
        Me.DeadlineDate = addWorkDays(25, DateOpened)
    Else
        Me.DeadlineDate = ""
    End If
End Sub

Since the control is bound to the table, there will be no need to run an update code. This will automatically have the deadline filled in.