Update a query with date

[adams77]

Is it possible to run a report that would update a query with the day the report was ran? If so, The next time the report was ran it would not update the field that already had a date.



If this could be done, would you give me some direction.

Thank you in advance.

[June7]

Code behind a form that opens report can update data. Code behind report can update data.

The real trick is figuring out what event to put code into. Maybe the report Close event.

Which table, which field, which records?

[adams77]

by chance do you have any sample code for this?

[June7]

An example from my db:

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
        CurrentDb.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
        CurrentDb.Execute "INSERT INTO ApprovedFurnaceCalibrations(FurnaceID, MixDesignNum, CF) VALUES ('" & rs!Furnace & "', '" & Me.MixNumber & "', " & rs!cf & ")"
        rs.MoveNext
    Wend
End If
ExitProc:
rs.Close
DoCmd.OpenReport "FurnaceCalibrationFactors", acViewPreview
CurrentDb.Execute "DELETE * FROM FurnaceCalibrationReportData"
End Sub