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
|
|||
|
|||
Updated datestamp doesn't work
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 |
#2
|
|||
|
|||
Updated datestamp doesn't work
As I read the code you have written, you are saying
Just before the update of the "Date-Updated" field you want the system to put date into the Date-Updated field. In order for the action to be performed you have to be changing the date you want updated. Put this/that code in the afterupdate event of whatever fields you want to trigger the updating of the Date_Updated. or To capture the date for any change to any field put this code in the BeforeUpdate event of the form (next to the place where you see the BeforeInsert and the AfterInsert. Me.DateUpdated = Date Ron |
#3
|
|||
|
|||
Updated datestamp doesn't work
Hi Graham,
If all your looking for is to enter a date in the field, use a command button so the user can click the button to automatically enter the current date of the amendment. Each time something is changed on the record, they can just click the button to refresh with current date. Unless I am not understanding what your looking for? "Graham" wrote: 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 |
#4
|
|||
|
|||
Updated datestamp doesn't work
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 |
#5
|
|||
|
|||
Updated datestamp doesn't work
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 |
#6
|
|||
|
|||
Updated datestamp doesn't work
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 |
#7
|
|||
|
|||
Updated datestamp doesn't work
Ken,
Nice routine. Question - does this handle changing null to zero or changing a zero to null? I think you attempted to handle nulls using the comparison If Nz(aNew(n), 0) Nz(aOld(n), 0) Then It seems to me this would fail if the Old value was null and the new value was zero OR the other way round. An alternative might be If (IsNull(aNew(n)) = True and IsNull(aOld(n)) = False) _ OR (IsNull(aNew(n)) = False and IsNull(aOld(n)) = True) _ OR (Nz(aNew(n), 0) Nz(aOld(n), 0)) Then AND with that test you probably don't even need the calls to the NZ function. '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === 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: |
#8
|
|||
|
|||
Updated datestamp doesn't work
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 |
#9
|
|||
|
|||
Updated datestamp doesn't work
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 |
#10
|
|||
|
|||
Updated datestamp doesn't work
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 |
|
Thread Tools | |
Display Modes | |
|
|