code to update a field

[Scott Randolph]

I'm new to writing Access code and I'm using Access97 and want to build a
simple field update tool.

I have a table named Quotes and in that table is two fields: (1) Status and
(2) DateClosed.

The [Status] field simply states whether a quote is open "A" or closed "X".
When the [Status] is changed to an "X" I would like update the [DateClosed]
field with a date and time stamp.

Sounds simple enough but, how would a newbie like me write the code?

Next question: What's a good source book on writing Access code?

TIA,

Scott

 

[Marshall Barton]

I have a table named Quotes and in that table is two fields: (1) Status and
(2) DateClosed.

The [Status] field simply states whether a quote is open "A" or closed "X".
When the [Status] is changed to an "X" I would like update the [DateClosed]
field with a date and time stamp.

Use can use the Status control's AfterUpdate event procedure
to set the DateClosed control.

If Me.Status = "X" Then
Me.txtDateClosed = Date()
Else
Me.txtDateClosed = Null
End If

 

[Scott Randolph]

Thanks! Worked like a charm. However, to make it work I had to add
[DateClosed] to the query and then to the form. How would I write the same
code without having the [DateClosed] field on the form?

I have a table named Quotes and in that table is two fields: (1) Status and
(2) DateClosed.

The [Status] field simply states whether a quote is open "A" or closed "X".
When the [Status] is changed to an "X" I would like update the [DateClosed]
field with a date and time stamp.

Use can use the Status control's AfterUpdate event procedure
to set the DateClosed control.

If Me.Status = "X" Then
Me.txtDateClosed = Date()
Else
Me.txtDateClosed = Null
End If

 

[Marshall Barton]

Thanks! Worked like a charm. However, to make it work I had to add
[DateClosed] to the query and then to the form. How would I write the same
code without having the [DateClosed] field on the form?

I had assumed you already had that kind of set up. While
it's certainly possible to do it other ways, it's just not
worth it. Using a bound text box on the form is very easy
and straightforward. You can make the DateClosed text box
invisible if you don't want to see it.
--
Marsh
MVP [MS Access]

I have a table named Quotes and in that table is two fields: (1) Status and
(2) DateClosed.

The [Status] field simply states whether a quote is open "A" or closed "X".
When the [Status] is changed to an "X" I would like update the [DateClosed]
field with a date and time stamp.

"Marshall Barton" wrote
Use can use the Status control's AfterUpdate event procedure
to set the DateClosed control.

If Me.Status = "X" Then
Me.txtDateClosed = Date()
Else
Me.txtDateClosed = Null
End If

 

[Roezmv]

Hello everyone,

First time posting to this board, and first thing I'd like to do is
thank all the great people who are lending a hand.

Second, I have a situation similar to the one that started this thread.
I have a Date_Last_Modified field that is meant to be reset to the
current date any time any other field in the form is updated.

The first solution that came to mind was to add a "_AfterUpdate"
procedure to each and every field in my form, this procedure would
simple be:

"
Me.Date_Last_Modified = Date
"

And in fact this does work. Of course, the problem is that it will
require me to add such code to each and every field! That solution is
messy and creates maintainability issues. If someone ads a field down
the road, they'll need to remember to manually add this special code to
it. If the code needs to be changed, it will need to be changed in
many places.

Ideally I'd love if there was a way to define one
UpdateDateLastModified procedure in one place and simply have the form
call that procedure whenever a field was updated (instead of having to
manually create specific code for each field).

I have tried to simply create a "Form_AfterUpdate" procedure that
contains the above mentioned code, but sadly it does not seem to
function . Here's what happens... I enter into a random field, type
new data, and exit. The Date_Last_Modified field does not update, and
further, when I try to change records or switch into design mode, I
just get a beep. When I try to close the form I am told "You Can't
save this record at this time, Microsoft Access may have encountered an
error while trying to save a record. If you close this object now,
data changes may be lost..."

Thank you kindly for your help.

 

[Marshall Barton]

Hello everyone,

First time posting to this board, and first thing I'd like to do is
thank all the great people who are lending a hand.

Second, I have a situation similar to the one that started this thread.
I have a Date_Last_Modified field that is meant to be reset to the
current date any time any other field in the form is updated.

The first solution that came to mind was to add a "_AfterUpdate"
procedure to each and every field in my form, this procedure would
simple be:

"
Me.Date_Last_Modified = Date
"

And in fact this does work. Of course, the problem is that it will
require me to add such code to each and every field! That solution is
messy and creates maintainability issues. If someone ads a field down
the road, they'll need to remember to manually add this special code to
it. If the code needs to be changed, it will need to be changed in
many places.

Ideally I'd love if there was a way to define one
UpdateDateLastModified procedure in one place and simply have the form
call that procedure whenever a field was updated (instead of having to
manually create specific code for each field).

I believe that you should be using the form's BeforeUpdate
event (or the form's Dirty event).