I do NOT advise any method that requires creating a new table every month. Any process that requires routinely modifying design with code is ill-conceived.
As already confirmed, code can pull value from calculated control. The real trick is figuring out what event to put code in.
Here is example from my db that runs code in Report Close event. It has reference to report control to pull a value - although it is not a calculated value, the principal is the same:
Code:
Private Sub Report_Close()
'save calculated results to ApprovedFurnaceCalibrations table
'and open FurnaceCalibrationFactors report
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT Furnace, MixNumber, RRound([Avg12]-[ASPHOPT],2) AS CF " & _
"FROM 610 RIGHT JOIN FurnaceCalibrationReportData ON [610].LABNUM = FurnaceCalibrationReportData.MixNumber " & _
"WHERE Not Avg12 Is Null;", cn, adOpenStatic, adLockPessimistic
If Not IsNull(DLookup("MixDesignNum", "ApprovedFurnaceCalibrations")) Then
If MsgBox("Correction Factors already recorded. Update all values?", vbYesNo, "CF") = vbYes Then
cn.Execute "DELETE FROM ApprovedFurnaceCalibrations WHERE MixDesignNum='" & Me.MixNumber & "'"
End If
End If
If IsNull(DLookup("MixDesignNum", "ApprovedFurnaceCalibrations", "FurnaceID='" & rs!Furnace & "' AND MixDesignNum='" & Me.MixNumber & "'")) Then
While Not rs.EOF
cn.Execute "INSERT INTO ApprovedFurnaceCalibrations(FurnaceID, MixDesignNum, CF) VALUES ('" & rs!Furnace & "', '" & Me.MixNumber & "', " & rs!cf & ")"
rs.MoveNext
Wend
End If
Exit_proc:
rs.Close
DoCmd.OpenReport "FurnaceCalibrationFactors", acViewPreview
CurrentDb.Execute "DELETE * FROM FurnaceCalibrationReportData"
End Sub