Change DAte

[JohnR]

is there any way to automatically change a date field to the current date
whenever a change is made in the record ??

Thanks
John

 

[Damien McBain]

is there any way to automatically change a date field to the current
date whenever a change is made in the record ??

Thanks
John

If you're editing the record using a form, you can use an afterupdate even
on one of the other fields like:

txtChangeDate = Date()

If you're editing it directly in the table, I think you'll have to change it
manually. But then again if you're editing it in the table you might as well
use excel.

 

[JohnR]

Is that the exact wording to insert into the "on change" event code ???I
believe it goes into a VBE script [Definitely not my strong suit]
John

 

[Damien McBain]

Is that the exact wording to insert into the "on change" event code
???I believe it goes into a VBE script [Definitely not my strong
suit]
John

OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the propery called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)

 

[JohnR]

Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!
John

Is that the exact wording to insert into the "on change" event code
???I believe it goes into a VBE script [Definitely not my strong
suit]
John

OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the propery called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in
txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)

 

[Tom Wickerath]

Hi John,

...without writing this macro 35 times or so.

Damien's method uses VBA code. It is not a macro.

Yes, move the code to the Form's Before_Update event procedure.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message
Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!
John

_____________________________________

Is that the exact wording to insert into the "on change" event code
???I believe it goes into a VBE script [Definitely not my strong
suit]
John

OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the propery called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in
txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)

 

[JohnR]

That doesn't appear to be doing it. I right click on the form bar in design
mode, select properties and go to the event tab. I enter the code, save it
& enter the form. clicking and/or changing anything in the form does not
change the date field. Am I doing something wrong ????
John

Hi John,

...without writing this macro 35 times or so.

Damien's method uses VBA code. It is not a macro.

Yes, move the code to the Form's Before_Update event procedure.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to
do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!
John

_____________________________________

Is that the exact wording to insert into the "on change" event code
???I believe it goes into a VBE script [Definitely not my strong
suit]
John

OK, on the form where you're editing the data, choose the control (ie
text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the propery called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private
sub
with no code in it.
Paste in the line of code I provided below, making allowance for
different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in
txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)

JohnR wrote:
is there any way to automatically change a date field to the current
date whenever a change is made in the record ??

Thanks
John

If you're editing the record using a form, you can use an
afterupdate even on one of the other fields like:

txtChangeDate = Date()

If you're editing it directly in the table, I think you'll have to
change it
manually. But then again if you're editing it in the table you might
as well
use excel.

 

[Tom Wickerath]

Hi John,

Open the form in design view. Click on View > Properties to bring up the properties dialog (if it
is not already displayed). Make sure that the word "Form" is displayed in the blue title bar of
the properties dialog. Select the Event tab. On the 4th entry from the top, you should see:

Before Update...........[Event Procedure]

Click on the ellipses (the button with the three dots) for this event procedure. You should see a
new procedure in your code that looks like this:


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub
________________

Type in the line of code indicated below. Use the appropriate name for the field that you are
storing this date into. In my example, the field is named "DateLastUpdated".

Private Sub Form_BeforeUpdate(Cancel As Integer)

DateLastUpdated = Date

End Sub


This procedure should work, as long as you make the record dirty (ie. you start to edit the
record in any way, so that a pencil symbol is displayed in the record selector). The form's
before_update event procedure will not fire if you haven't made any changes to the data in an
existing record. You'll need to use the navigation buttons at the bottom of the form to change to
a different record. Changing to a different record will automatically commit your change.
(Alternatively, you can use the command button wizard to add a Save Record command button). When
you return to this record, you should see the updated date value.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message
That doesn't appear to be doing it. I right click on the form bar in design
mode, select properties and go to the event tab. I enter the code, save it
& enter the form. clicking and/or changing anything in the form does not
change the date field. Am I doing something wrong ????

John
_____________________________________


Hi John,

...without writing this macro 35 times or so.

Damien's method uses VBA code. It is not a macro.

Yes, move the code to the Form's Before_Update event procedure.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message
Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!

John

_____________________________________



OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the property called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)
_____________________________________


"JohnR" <onejm at comcast.net> wrote in message
Is that the exact wording to insert into the "on change" event code ???I
believe it goes into a VBE script [Definitely not my strong suit]
John
_____________________________________


If you're editing the record using a form, you can use an afterupdate even
on one of the other fields like:

txtChangeDate = Date()

If you're editing it directly in the table, I think you'll have to change it
manually. But then again if you're editing it in the table you might as well
use excel.
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message
is there any way to automatically change a date field to the current date
whenever a change is made in the record ??

Thanks
John

 

[JohnR]

Terrific Tom. Both methods work. Don't know what I was doing wrong, but
problem is solved. I also added "Save A Record" button to toolbar.
Thanks Again
John

Hi John,

Open the form in design view. Click on View > Properties to bring up the
properties dialog (if it
is not already displayed). Make sure that the word "Form" is displayed in
the blue title bar of
the properties dialog. Select the Event tab. On the 4th entry from the
top, you should see:

Before Update...........[Event Procedure]

Click on the ellipses (the button with the three dots) for this event
procedure. You should see a
new procedure in your code that looks like this:


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub
________________

Type in the line of code indicated below. Use the appropriate name for the
field that you are
storing this date into. In my example, the field is named
"DateLastUpdated".

Private Sub Form_BeforeUpdate(Cancel As Integer)

DateLastUpdated = Date

End Sub


This procedure should work, as long as you make the record dirty (ie. you
start to edit the
record in any way, so that a pencil symbol is displayed in the record
selector). The form's
before_update event procedure will not fire if you haven't made any
changes to the data in an
existing record. You'll need to use the navigation buttons at the bottom
of the form to change to
a different record. Changing to a different record will automatically
commit your change.
(Alternatively, you can use the command button wizard to add a Save Record
command button). When
you return to this record, you should see the updated date value.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

That doesn't appear to be doing it. I right click on the form bar in
design
mode, select properties and go to the event tab. I enter the code, save
it
& enter the form. clicking and/or changing anything in the form does not
change the date field. Am I doing something wrong ????

John
_____________________________________


Hi John,

...without writing this macro 35 times or so.

Damien's method uses VBA code. It is not a macro.

Yes, move the code to the Form's Before_Update event procedure.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to
do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!

John

_____________________________________



OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the property called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in
txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)
_____________________________________


"JohnR" <onejm at comcast.net> wrote in message

Is that the exact wording to insert into the "on change" event code ???I
believe it goes into a VBE script [Definitely not my strong suit]
John
_____________________________________


If you're editing the record using a form, you can use an afterupdate even
on one of the other fields like:

txtChangeDate = Date()

If you're editing it directly in the table, I think you'll have to change
it
manually. But then again if you're editing it in the table you might as
well
use excel.
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

is there any way to automatically change a date field to the current date
whenever a change is made in the record ??

Thanks
John

 

[Tom Wickerath]

You're welcome!

________________________________________

"JohnR" <onejm at comcast.net> wrote in message
Terrific Tom. Both methods work. Don't know what I was doing wrong, but
problem is solved. I also added "Save A Record" button to toolbar.
Thanks Again
John


________________________________________

Hi John,

Open the form in design view. Click on View > Properties to bring up the
properties dialog (if it
is not already displayed). Make sure that the word "Form" is displayed in
the blue title bar of
the properties dialog. Select the Event tab. On the 4th entry from the
top, you should see:

Before Update...........[Event Procedure]

Click on the ellipses (the button with the three dots) for this event
procedure. You should see a
new procedure in your code that looks like this:


Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub
________________

Type in the line of code indicated below. Use the appropriate name for the
field that you are
storing this date into. In my example, the field is named
"DateLastUpdated".

Private Sub Form_BeforeUpdate(Cancel As Integer)

DateLastUpdated = Date

End Sub


This procedure should work, as long as you make the record dirty (ie. you
start to edit the
record in any way, so that a pencil symbol is displayed in the record
selector). The form's
before_update event procedure will not fire if you haven't made any
changes to the data in an
existing record. You'll need to use the navigation buttons at the bottom
of the form to change to
a different record. Changing to a different record will automatically
commit your change.
(Alternatively, you can use the command button wizard to add a Save Record
command button). When
you return to this record, you should see the updated date value.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

That doesn't appear to be doing it. I right click on the form bar in
design
mode, select properties and go to the event tab. I enter the code, save
it
& enter the form. clicking and/or changing anything in the form does not
change the date field. Am I doing something wrong ????

John
_____________________________________


Hi John,

...without writing this macro 35 times or so.

Damien's method uses VBA code. It is not a macro.

Yes, move the code to the Form's Before_Update event procedure.

Tom
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

Ok, terrific, it works now. I was also using the wrong function in the
date field. I was using Date(), instead of Now(). Is there any way to
do
this so a change in "ANY" field will trigger this date change, without
writing this macro 35 times or so. That's the number of fields I have.
If not, many thanks anyway!

John

_____________________________________



OK, on the form where you're editing the data, choose the control (ie text
box) which will trigger the date change when you edit it's contents. I'm
going to call it txtData and the one with the change date in it
txtChangeDate.
Open the property sheet for txtData, find the property called After Update
and click on the build button next to it (looks like "..."). Choose code
builder. This opens the VBA editor and places your cursor in a private sub
with no code in it.
Paste in the line of code I provided below, making allowance for different
field names in your form. Close the VBA editor and save the form.
Now run the form and test what happens when you change the data in
txtData.

HTH

Damo
(lucky I'm archiving right now, lots of time to type reply posts!)
_____________________________________


"JohnR" <onejm at comcast.net> wrote in message

Is that the exact wording to insert into the "on change" event code ???I
believe it goes into a VBE script [Definitely not my strong suit]
John
_____________________________________


If you're editing the record using a form, you can use an afterupdate even
on one of the other fields like:

txtChangeDate = Date()

If you're editing it directly in the table, I think you'll have to change
it
manually. But then again if you're editing it in the table you might as
well
use excel.
_____________________________________

"JohnR" <onejm at comcast.net> wrote in message

is there any way to automatically change a date field to the current date
whenever a change is made in the record ??

Thanks
John