Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10957 > unrolled thread

Auto Update Column on Record Update

Back to article view | Back to comp.databases.ms-access

Contents

  Auto Update Column on Record Update mflynn@scu.edu - 2015-07-23 11:47 -0700
    Re: Auto Update Column on Record Update Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-07-24 06:09 -0700
      Re: Auto Update Column on Record Update Ulrich Möller <knobbi38@arcor.de> - 2015-07-24 22:00 +0200
    Re: Auto Update Column on Record Update "Ron Paii" <None@none.com> - 2015-07-27 07:47 -0500
      Re: Auto Update Column on Record Update mflynn@scu.edu - 2015-07-28 09:15 -0700
        Re: Auto Update Column on Record Update Ulrich Möller <knobbi38@arcor.de> - 2015-07-28 18:53 +0200
        Re: Auto Update Column on Record Update "Ron Paii" <None@none.com> - 2015-07-29 07:59 -0500
          Re: Auto Update Column on Record Update mflynn@scu.edu - 2015-07-30 09:16 -0700

#10957 — Auto Update Column on Record Update

I have several databases where most of the data originates in other databases and is loaded into my databases from an Excel spreadsheet.  I usually add a column to my tables named LoadDate that is a Date/Time type and I set the default value to =Now(). I never explicity load the LoadDate column, I let the default value take care of it.  This is handy for tracking issues and backing out loads that had an error.

Is there an way to add an UpdateDate column that does the same thing when the record is changed/updated?  Is there an event I could write some VBA for?  Are there some table otpions that would accomplish this?

In individual forms I have code that puts the current date and time into a UpdateDate column if the record is changed, I was just wondering if there was a way to achieve this in one place instead of having to write code in every place the record might be updated.

Any help gratefully accepted,

Mike

[toc] | [next] | [standalone]

#10959

On Thursday, July 23, 2015 at 2:48:01 PM UTC-4, mfl...@scu.edu wrote:
> I have several databases where most of the data originates in other databases and is loaded into my databases from an Excel spreadsheet.  I usually add a column to my tables named LoadDate that is a Date/Time type and I set the default value to =Now(). I never explicity load the LoadDate column, I let the default value take care of it.  This is handy for tracking issues and backing out loads that had an error.
> 
> Is there an way to add an UpdateDate column that does the same thing when the record is changed/updated?  Is there an event I could write some VBA for?  Are there some table otpions that would accomplish this?
> 
> In individual forms I have code that puts the current date and time into a UpdateDate column if the record is changed, I was just wondering if there was a way to achieve this in one place instead of having to write code in every place the record might be updated.
> 
> Any help gratefully accepted,
> 
> Mike

I assume you are updating from a form.  In the form's AfterUpdate event you could enter
  Me.UpdateDate = Date()  'or Now()

[toc] | [prev] | [next] | [standalone]

#10961

Am 24.07.2015 um 15:09 schrieb Patrick Finucane:
> On Thursday, July 23, 2015 at 2:48:01 PM UTC-4, mfl...@scu.edu wrote:
>> I have several databases where most of the data originates in other databases and is loaded into my databases from an Excel spreadsheet.  I usually add a column to my tables named LoadDate that is a Date/Time type and I set the default value to =Now(). I never explicity load the LoadDate column, I let the default value take care of it.  This is handy for tracking issues and backing out loads that had an error.
>>
>> Is there an way to add an UpdateDate column that does the same thing when the record is changed/updated?  Is there an event I could write some VBA for?  Are there some table otpions that would accomplish this?
>>
>> In individual forms I have code that puts the current date and time into a UpdateDate column if the record is changed, I was just wondering if there was a way to achieve this in one place instead of having to write code in every place the record might be updated.
>>
>> Any help gratefully accepted,
>>
>> Mike
> I assume you are updating from a form.  In the form's AfterUpdate event you could enter
>    Me.UpdateDate = Date()  'or Now()
You mean BeforeUpdate?

Urich

[toc] | [prev] | [next] | [standalone]

#10965

<mflynn@scu.edu> wrote in message 
news:b56e5c5b-6da2-4b19-a012-2267f4160690@googlegroups.com...
> I have several databases where most of the data originates in other 
> databases and is loaded into my databases from an Excel spreadsheet.  I 
> usually add a column to my tables named LoadDate that is a Date/Time type 
> and I set the default value to =Now(). I never explicity load the LoadDate 
> column, I let the default value take care of it.  This is handy for 
> tracking issues and backing out loads that had an error.
>
> Is there an way to add an UpdateDate column that does the same thing when 
> the record is changed/updated?  Is there an event I could write some VBA 
> for?  Are there some table otpions that would accomplish this?
>
> In individual forms I have code that puts the current date and time into a 
> UpdateDate column if the record is changed, I was just wondering if there 
> was a way to achieve this in one place instead of having to write code in 
> every place the record might be updated.
>
> Any help gratefully accepted,
>
> Mike
>
>

You could add a data macro to the table in the BE, with the added benefit of 
updating no matter how the record was updated. 

[toc] | [prev] | [next] | [standalone]

#10966

Thanks all of you.  In forms I do have 
code in the AfterUpdate event to set the UpdateDate to Now().
What I was looking for was more Ron's suggestion of something in the backend that runs whenever a record is updated, but I don't know how to do that.

In my setup there's just a front and backend combined, a normal Access .accdb file with the menus, code and data all together.  There's no separate backend, such as SQL Server.

[toc] | [prev] | [next] | [standalone]

#10967

Am 28.07.2015 um 18:15 schrieb mflynn@scu.edu:
> Thanks all of you.  In forms I do have
> code in the AfterUpdate event to set the UpdateDate to Now().
> What I was looking for was more Ron's suggestion of something in the backend that runs whenever a record is updated, but I don't know how to do that.
>
> In my setup there's just a front and backend combined, a normal Access .accdb file with the menus, code and data all together.  There's no separate backend, such as SQL Server.
>
>
>
Hi Mike,

it is better to use the beforeupdate form event instead of the 
afterupdate event. The afterupdate event is raised when the record has 
been already updated in the table so you would cause the application to 
make an update twice only for the timestamp.

To generalize this, you could create a class which catches the form 
beforeupdate event and makes the modifications to the underling form 
recordset. Then you will be able to use this class in all your forms. 
After that code changes are only needed to be done in this separate 
single class module.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10968

<mflynn@scu.edu> wrote in message 
news:44c0029f-66d3-4ad3-997a-0c0ec0f9060d@googlegroups.com...
> Thanks all of you.  In forms I do have
> code in the AfterUpdate event to set the UpdateDate to Now().
> What I was looking for was more Ron's suggestion of something in the 
> backend that runs whenever a record is updated, but I don't know how to do 
> that.
>
> In my setup there's just a front and backend combined, a normal Access 
> .accdb file with the menus, code and data all together.  There's no 
> separate backend, such as SQL Server.
>
>
>

Do some reading on data macros, they are not hard to do.

PS: it is HIGHLY recommended to split your app into a front end and back 
end. 

[toc] | [prev] | [next] | [standalone]

#10970

On Wednesday, July 29, 2015 at 5:59:25 AM UTC-7, Ron Paii wrote:
> <mflynn@scu.edu> wrote in message 
> news:44c0029f-66d3-4ad3-997a-0c0ec0f9060d@googlegroups.com...
> > Thanks all of you.  In forms I do have
> > code in the AfterUpdate event to set the UpdateDate to Now().
> > What I was looking for was more Ron's suggestion of something in the 
> > backend that runs whenever a record is updated, but I don't know how to do 
> > that.
> >
> > In my setup there's just a front and backend combined, a normal Access 
> > .accdb file with the menus, code and data all together.  There's no 
> > separate backend, such as SQL Server.
> >
> >
> >
> 
> Do some reading on data macros, they are not hard to do.
> 
> PS: it is HIGHLY recommended to split your app into a front end and back 
> end.

Thanks.   I'll do that.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web