If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add "Record Created On" Field to a Query
Can I add a field to a query that shows the date a record was created on? I
want to run this query every day so I need to know which records where created on which date? How do I add this field to a query table, or do I have to create a seperate table and then join then to bring the record date over? wishing I understood everything I know about Access grin Darrell |
#2
|
|||
|
|||
Add "Record Created On" Field to a Query
Nevermind I figured it out.
Darrell "Darrell" wrote: Can I add a field to a query that shows the date a record was created on? I want to run this query every day so I need to know which records where created on which date? How do I add this field to a query table, or do I have to create a seperate table and then join then to bring the record date over? wishing I understood everything I know about Access grin Darrell |
#3
|
|||
|
|||
Add "Record Created On" Field to a Query
One of the better solutions is to add two date/time fields to your tables.
One[Created] defaulted to Now(), the second one [LastUpdate] controlled by the BeforeUpdate or OnDirty event of the bound data entry form. "Darrell" wrote in message ... Can I add a field to a query that shows the date a record was created on? I want to run this query every day so I need to know which records where created on which date? How do I add this field to a query table, or do I have to create a seperate table and then join then to bring the record date over? wishing I understood everything I know about Access grin Darrell |
#4
|
|||
|
|||
Add "Record Created On" Field to a Query
Updated or dirtied doesn't necessarily mean the data will have been changed
of course as a user might change a value, then change it back to the original value before the record is saved. To record the date of an actual change to the data put the following module in the database: ''''module starts'''' ' module basChangedRecord ' determines if data in a record edited ' in a form has actually been changed Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() End Sub Public Sub StoreNewVals(rst As DAO.Recordset) ' store values of edited row in array aNewVals = rst.GetRows() End Sub Public Function RecordHasChanged() As Boolean Dim n As Integer, intlast As Integer Dim var As Variant Dim aOld(), aNew() intlast = UBound(aOldVals) - 1 ' loop through array of original values ' and store in new array For Each var In aOldVals() ReDim Preserve aOld(n) aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array For Each var In aNewVals() ReDim Preserve aNew(n) aNew(n) = var ' if any value has changed then return True If Nz(aNew(n), 0) Nz(aOld(n), 0) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function ''''module ends'''' And call the functions in a form's module as follows, taken from a form based on an Addresses table with an AddressID primary key: Private Sub Form_Current() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If End Sub Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.DateUpdated) VBA.Date Then StoreNewVals rst If RecordHasChanged() Then With rst .MoveFirst .Edit .Fields("DateUpdated") = VBA.Date .Update End With Me.Refresh End If End If End Sub Ken Sheridan Stafford, England "Ed Robichaud" wrote: One of the better solutions is to add two date/time fields to your tables. One[Created] defaulted to Now(), the second one [LastUpdate] controlled by the BeforeUpdate or OnDirty event of the bound data entry form. "Darrell" wrote in message ... Can I add a field to a query that shows the date a record was created on? I want to run this query every day so I need to know which records where created on which date? How do I add this field to a query table, or do I have to create a seperate table and then join then to bring the record date over? wishing I understood everything I know about Access grin Darrell |
#5
|
|||
|
|||
Add "Record Created On" Field to a Query
Hi Ken,
Interesting and useful! I have used something similar in some of my systems, only I was using it to see it someone else had changed the data and therefore to prevent the current person from overwriting the other person's changes. (This was not using Access.) A couple of thoughts: 1) The "If Nz(aNew(n), 0) Nz(aOld(n), 0) Then" condition will falsely report no changes if the values switch between 0 and Null. Better to do something like this: If (Isull(aNew(n)) And Not IsNull(aOld(n))) Or (Not Isull(aNew(n)) And IsNull(aOld(n))) Or aNew(n) aOld(n) Then 2) For performance reasons, place the ReDim outside of the For loop: ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() instead of: For Each var In aOldVals() ReDim Preserve aOld(n) or, just use a direct assignment: aOld = aOldVals Sincerely, Clifford Bass "Ken Sheridan" wrote: Updated or dirtied doesn't necessarily mean the data will have been changed of course as a user might change a value, then change it back to the original value before the record is saved. To record the date of an actual change to the data put the following module in the database: ''''module starts'''' ' module basChangedRecord ' determines if data in a record edited ' in a form has actually been changed Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() End Sub Public Sub StoreNewVals(rst As DAO.Recordset) ' store values of edited row in array aNewVals = rst.GetRows() End Sub Public Function RecordHasChanged() As Boolean Dim n As Integer, intlast As Integer Dim var As Variant Dim aOld(), aNew() intlast = UBound(aOldVals) - 1 ' loop through array of original values ' and store in new array For Each var In aOldVals() ReDim Preserve aOld(n) aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array For Each var In aNewVals() ReDim Preserve aNew(n) aNew(n) = var ' if any value has changed then return True If Nz(aNew(n), 0) Nz(aOld(n), 0) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function ''''module ends'''' And call the functions in a form's module as follows, taken from a form based on an Addresses table with an AddressID primary key: Private Sub Form_Current() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If End Sub Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.DateUpdated) VBA.Date Then StoreNewVals rst If RecordHasChanged() Then With rst .MoveFirst .Edit .Fields("DateUpdated") = VBA.Date .Update End With Me.Refresh End If End If End Sub Ken Sheridan Stafford, England |
#6
|
|||
|
|||
Add "Record Created On" Field to a Query
Cllifford:
Thanks for the very helpful comments. I'll take those on board. Ken Sheridan Stafford, England "Clifford Bass" wrote: Hi Ken, Interesting and useful! I have used something similar in some of my systems, only I was using it to see it someone else had changed the data and therefore to prevent the current person from overwriting the other person's changes. (This was not using Access.) A couple of thoughts: 1) The "If Nz(aNew(n), 0) Nz(aOld(n), 0) Then" condition will falsely report no changes if the values switch between 0 and Null. Better to do something like this: If (Isull(aNew(n)) And Not IsNull(aOld(n))) Or (Not Isull(aNew(n)) And IsNull(aOld(n))) Or aNew(n) aOld(n) Then 2) For performance reasons, place the ReDim outside of the For loop: ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() instead of: For Each var In aOldVals() ReDim Preserve aOld(n) or, just use a direct assignment: aOld = aOldVals Sincerely, Clifford Bass "Ken Sheridan" wrote: Updated or dirtied doesn't necessarily mean the data will have been changed of course as a user might change a value, then change it back to the original value before the record is saved. To record the date of an actual change to the data put the following module in the database: ''''module starts'''' ' module basChangedRecord ' determines if data in a record edited ' in a form has actually been changed Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() End Sub Public Sub StoreNewVals(rst As DAO.Recordset) ' store values of edited row in array aNewVals = rst.GetRows() End Sub Public Function RecordHasChanged() As Boolean Dim n As Integer, intlast As Integer Dim var As Variant Dim aOld(), aNew() intlast = UBound(aOldVals) - 1 ' loop through array of original values ' and store in new array For Each var In aOldVals() ReDim Preserve aOld(n) aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array For Each var In aNewVals() ReDim Preserve aNew(n) aNew(n) = var ' if any value has changed then return True If Nz(aNew(n), 0) Nz(aOld(n), 0) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function ''''module ends'''' And call the functions in a form's module as follows, taken from a form based on an Addresses table with an AddressID primary key: Private Sub Form_Current() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If End Sub Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.DateUpdated) VBA.Date Then StoreNewVals rst If RecordHasChanged() Then With rst .MoveFirst .Edit .Fields("DateUpdated") = VBA.Date .Update End With Me.Refresh End If End If End Sub Ken Sheridan Stafford, England |
#7
|
|||
|
|||
Add "Record Created On" Field to a Query
You are very much welcome!
Clifford Bass "Ken Sheridan" wrote: Cllifford: Thanks for the very helpful comments. I'll take those on board. Ken Sheridan Stafford, England |
Thread Tools | |
Display Modes | |
|
|