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 |
#11
|
|||
|
|||
Updated datestamp doesn't work
Can you post back with the code you are using to call the functions in the
form's Current and AfterUpdate event procedures. On what line is the error occurring? Ken Sheridan Stafford, England "Piperlynne" wrote: Thanks Ken. I updated and compiled/repaired and changed function names throughout. Now I'm getting runtime '3021 No current record. Which I think something in the way I have this setup. . . I'm going to go bang my head against the wall now ;-) "Ken Sheridan" wrote: Firstly here's a revised version, which answers John's point, which I seem to have overlooked at the time (sorry, John), but fortunately someone else brought to it my attention in another thread: ' 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 ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array ReDim Preserve aNew(UBound(aOld)) For Each var In aNewVals() aNew(n) = var ' if any value has changed then return True If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _ Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _ Or aNew(n) aOld(n) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function Delete your current module and replace it with the above. Be sure to save it with a name other than that of one of the functions (as you see I call it basChangedRecord). If you still have problems, and a compact/repair doesn't help, then trying changing the function names to something like StoreMyOldVals, StoreMyNewVals and MyRecordHasChanged for instance (just do a couple of 'find/replace all' operations on the whole current project from the VBA menu bar). Ken Sheridan Stafford, England "Piperlynne" wrote: This is great. . .I'm trying to use it in my db. However. . when I load the form I get an error on StoreOldVals that sub or function not defined (on current event) It's defined in the module. . I checked. . .soo. .. hmmm??? Any ideas??? "Ken Sheridan" wrote: There is one potential fly in the ointment here in that a user can update a record without actually changing any data, i.e. they can change it and then change it back to the original again. So if you want to assign the current date to the DateUpdated column only if the data has in fact changed then you need to check that the data has actually been amended. One way you can do this is by first adding the following module to the database. Just open a new standard module, paste in the code from below in place of the two lines already there, and then save the module: ' 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 Then in the form's Current event procedure put the following code, changing MyTable and MyID to the names of your table and its primary key column: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If In the form's AfterUpdate event procedure put: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID 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 Ken Sheridan Stafford, England "Graham" wrote: Thank you all. I was putting the code in the 'Update' control, by placing it in the Form properties it now works a treat. As soon as I change any of the controls on the Input Form, this date is automatically updated. I'd looked at loads of postings on this subject and not one mentioned that the code should be in the Form properties rather than the Control Properties. Many Thanks again!! "John Spencer" wrote: Try putting the code in the Before Update event of the FORM. Putting the code in the control's before update event is not the correct place -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Graham" wrote in message ... I have a MainTable and an InputForm. I have two columns on the MainTable, DateCreated & DateUpdated. Both are formatted as short date, with an input mask. DateCreated works fine with" =Date()" in the Default Property of the MainTable, and shows up in a control on InputForm, when a new record is added, the current date is added. DateUpdated should show the date, on that record, of any amendments to that record. DateUpdated has no Default Property in the MainTable, and in the MainForm Control Properties I have entered : Private Sub DateUpdated_BeforeUpdate(Cancel As Integer) Me.DateUpdated = Date End Sub It doesn't work! I've entered it in the BeforeUpdate & the AfterUpdate Event. I've added () after Date I've changed the fullstop in Me.DateUpdated to an exclamation mark Me!DateUpdated Nothing appears to work. Can anyone see where I'm going wrong ? Do I need to be amending any other Prooperties ? Many Thanks |
#12
|
|||
|
|||
Updated datestamp doesn't work
The line the error is actually occuring in is in the module
Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreMyOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() ---- error happening here End Sub Here's the current and afterupdate call functions Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.LastRevDt) VBA.Date Then StoreMyNewVals rst If MyRecordHasChanged() Then With rst .MoveFirst .Edit .Fields("LastRevDt") = VBA.Date .Update EndWith Me.refresh End If End If End Sub 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 tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreMyOldVals rst End If End Sub "Ken Sheridan" wrote: Can you post back with the code you are using to call the functions in the form's Current and AfterUpdate event procedures. On what line is the error occurring? Ken Sheridan Stafford, England "Piperlynne" wrote: Thanks Ken. I updated and compiled/repaired and changed function names throughout. Now I'm getting runtime '3021 No current record. Which I think something in the way I have this setup. . . I'm going to go bang my head against the wall now ;-) "Ken Sheridan" wrote: Firstly here's a revised version, which answers John's point, which I seem to have overlooked at the time (sorry, John), but fortunately someone else brought to it my attention in another thread: ' 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 ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array ReDim Preserve aNew(UBound(aOld)) For Each var In aNewVals() aNew(n) = var ' if any value has changed then return True If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _ Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _ Or aNew(n) aOld(n) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function Delete your current module and replace it with the above. Be sure to save it with a name other than that of one of the functions (as you see I call it basChangedRecord). If you still have problems, and a compact/repair doesn't help, then trying changing the function names to something like StoreMyOldVals, StoreMyNewVals and MyRecordHasChanged for instance (just do a couple of 'find/replace all' operations on the whole current project from the VBA menu bar). Ken Sheridan Stafford, England "Piperlynne" wrote: This is great. . .I'm trying to use it in my db. However. . when I load the form I get an error on StoreOldVals that sub or function not defined (on current event) It's defined in the module. . I checked. . .soo. .. hmmm??? Any ideas??? "Ken Sheridan" wrote: There is one potential fly in the ointment here in that a user can update a record without actually changing any data, i.e. they can change it and then change it back to the original again. So if you want to assign the current date to the DateUpdated column only if the data has in fact changed then you need to check that the data has actually been amended. One way you can do this is by first adding the following module to the database. Just open a new standard module, paste in the code from below in place of the two lines already there, and then save the module: ' 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 Then in the form's Current event procedure put the following code, changing MyTable and MyID to the names of your table and its primary key column: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If In the form's AfterUpdate event procedure put: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID 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 Ken Sheridan Stafford, England "Graham" wrote: Thank you all. I was putting the code in the 'Update' control, by placing it in the Form properties it now works a treat. As soon as I change any of the controls on the Input Form, this date is automatically updated. I'd looked at loads of postings on this subject and not one mentioned that the code should be in the Form properties rather than the Control Properties. Many Thanks again!! "John Spencer" wrote: Try putting the code in the Before Update event of the FORM. Putting the code in the control's before update event is not the correct place -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Graham" wrote in message ... I have a MainTable and an InputForm. I have two columns on the MainTable, DateCreated & DateUpdated. Both are formatted as short date, with an input mask. DateCreated works fine with" =Date()" in the Default Property of the MainTable, and shows up in a control on InputForm, when a new record is added, the current date is added. DateUpdated should show the date, on that record, of any amendments to that record. DateUpdated has no Default Property in the MainTable, and in the MainForm Control Properties I have entered : Private Sub DateUpdated_BeforeUpdate(Cancel As Integer) Me.DateUpdated = Date End Sub It doesn't work! I've entered it in the BeforeUpdate & the AfterUpdate Event. I've added () after Date I've changed the fullstop in Me.DateUpdated to an exclamation mark Me!DateUpdated Nothing appears to work. Can anyone see where I'm going wrong ? Do I need to be amending any other Prooperties ? Many Thanks |
#13
|
|||
|
|||
Updated datestamp doesn't work
All I can think of is that the form is bound to an empty table, i.e. you are
inserting the first record. Once a row exists in the table it should work. I assume the SKU column is a number data type. Ken Sheridan Stafford, England "Piperlynne" wrote: The line the error is actually occuring in is in the module Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreMyOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() ---- error happening here End Sub Here's the current and afterupdate call functions Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.LastRevDt) VBA.Date Then StoreMyNewVals rst If MyRecordHasChanged() Then With rst .MoveFirst .Edit .Fields("LastRevDt") = VBA.Date .Update EndWith Me.refresh End If End If End Sub 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 tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreMyOldVals rst End If End Sub "Ken Sheridan" wrote: Can you post back with the code you are using to call the functions in the form's Current and AfterUpdate event procedures. On what line is the error occurring? Ken Sheridan Stafford, England "Piperlynne" wrote: Thanks Ken. I updated and compiled/repaired and changed function names throughout. Now I'm getting runtime '3021 No current record. Which I think something in the way I have this setup. . . I'm going to go bang my head against the wall now ;-) "Ken Sheridan" wrote: Firstly here's a revised version, which answers John's point, which I seem to have overlooked at the time (sorry, John), but fortunately someone else brought to it my attention in another thread: ' 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 ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array ReDim Preserve aNew(UBound(aOld)) For Each var In aNewVals() aNew(n) = var ' if any value has changed then return True If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _ Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _ Or aNew(n) aOld(n) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function Delete your current module and replace it with the above. Be sure to save it with a name other than that of one of the functions (as you see I call it basChangedRecord). If you still have problems, and a compact/repair doesn't help, then trying changing the function names to something like StoreMyOldVals, StoreMyNewVals and MyRecordHasChanged for instance (just do a couple of 'find/replace all' operations on the whole current project from the VBA menu bar). Ken Sheridan Stafford, England "Piperlynne" wrote: This is great. . .I'm trying to use it in my db. However. . when I load the form I get an error on StoreOldVals that sub or function not defined (on current event) It's defined in the module. . I checked. . .soo. .. hmmm??? Any ideas??? "Ken Sheridan" wrote: There is one potential fly in the ointment here in that a user can update a record without actually changing any data, i.e. they can change it and then change it back to the original again. So if you want to assign the current date to the DateUpdated column only if the data has in fact changed then you need to check that the data has actually been amended. One way you can do this is by first adding the following module to the database. Just open a new standard module, paste in the code from below in place of the two lines already there, and then save the module: ' 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 Then in the form's Current event procedure put the following code, changing MyTable and MyID to the names of your table and its primary key column: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If In the form's AfterUpdate event procedure put: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID 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 Ken Sheridan Stafford, England "Graham" wrote: Thank you all. I was putting the code in the 'Update' control, by placing it in the Form properties it now works a treat. As soon as I change any of the controls on the Input Form, this date is automatically updated. I'd looked at loads of postings on this subject and not one mentioned that the code should be in the Form properties rather than the Control Properties. Many Thanks again!! "John Spencer" wrote: Try putting the code in the Before Update event of the FORM. Putting the code in the control's before update event is not the correct place -- |
#14
|
|||
|
|||
Updated datestamp doesn't work
I moved the current to before update and can now open the table without the
error occuring. However when I save a new record I get the same error. There are already 340 records in the table and they do show up on the form. I double checked the properties and the form is connected to the table. I'm going to spend the day playing with it a bit and see if I can't get it to work. "Ken Sheridan" wrote: All I can think of is that the form is bound to an empty table, i.e. you are inserting the first record. Once a row exists in the table it should work. I assume the SKU column is a number data type. Ken Sheridan Stafford, England "Piperlynne" wrote: The line the error is actually occuring in is in the module Option Compare Database Option Explicit ' arrays for storing values from recordsets Dim aOldVals(), aNewVals() Public Sub StoreMyOldVals(rst As DAO.Recordset) ' store values of current row in array aOldVals = rst.GetRows() ---- error happening here End Sub Here's the current and afterupdate call functions Private Sub Form_AfterUpdate() Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) If Nz(Me.LastRevDt) VBA.Date Then StoreMyNewVals rst If MyRecordHasChanged() Then With rst .MoveFirst .Edit .Fields("LastRevDt") = VBA.Date .Update EndWith Me.refresh End If End If End Sub 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 tblItem WHERE SKU = " & Me.SKU Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreMyOldVals rst End If End Sub "Ken Sheridan" wrote: Can you post back with the code you are using to call the functions in the form's Current and AfterUpdate event procedures. On what line is the error occurring? Ken Sheridan Stafford, England "Piperlynne" wrote: Thanks Ken. I updated and compiled/repaired and changed function names throughout. Now I'm getting runtime '3021 No current record. Which I think something in the way I have this setup. . . I'm going to go bang my head against the wall now ;-) "Ken Sheridan" wrote: Firstly here's a revised version, which answers John's point, which I seem to have overlooked at the time (sorry, John), but fortunately someone else brought to it my attention in another thread: ' 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 ReDim Preserve aOld(UBound(aOldVals)) For Each var In aOldVals() aOld(n) = var n = n + 1 Next var n = 0 ' loop through array of edited values ' and store in new array ReDim Preserve aNew(UBound(aOld)) For Each var In aNewVals() aNew(n) = var ' if any value has changed then return True If (IsNull(aNew(n)) And Not IsNull(aOld(n))) _ Or (Not IsNull(aNew(n)) And IsNull(aOld(n))) _ Or aNew(n) aOld(n) Then RecordHasChanged = True Exit For End If n = n + 1 Next var End Function Delete your current module and replace it with the above. Be sure to save it with a name other than that of one of the functions (as you see I call it basChangedRecord). If you still have problems, and a compact/repair doesn't help, then trying changing the function names to something like StoreMyOldVals, StoreMyNewVals and MyRecordHasChanged for instance (just do a couple of 'find/replace all' operations on the whole current project from the VBA menu bar). Ken Sheridan Stafford, England "Piperlynne" wrote: This is great. . .I'm trying to use it in my db. However. . when I load the form I get an error on StoreOldVals that sub or function not defined (on current event) It's defined in the module. . I checked. . .soo. .. hmmm??? Any ideas??? "Ken Sheridan" wrote: There is one potential fly in the ointment here in that a user can update a record without actually changing any data, i.e. they can change it and then change it back to the original again. So if you want to assign the current date to the DateUpdated column only if the data has in fact changed then you need to check that the data has actually been amended. One way you can do this is by first adding the following module to the database. Just open a new standard module, paste in the code from below in place of the two lines already there, and then save the module: ' 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 Then in the form's Current event procedure put the following code, changing MyTable and MyID to the names of your table and its primary key column: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String If Not Me.NewRecord Then strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) StoreOldVals rst End If In the form's AfterUpdate event procedure put: Dim dbs As DAO.Database, rst As DAO.Recordset Dim strSQL As String strSQL = "SELECT * FROM MyTable WHERE MyID = " & Me.MyID 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 Ken Sheridan Stafford, England "Graham" wrote: Thank you all. I was putting the code in the 'Update' control, by placing it in the Form properties it now works a treat. As soon as I change any of the controls on the Input Form, this date is automatically updated. |
|
Thread Tools | |
Display Modes | |
|
|