Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AfterUpdate - Help - Does Not Work (Access97 SR-2)

    The following Q & A was extracted from Woody's Access Watch # 3.14:

    Q:JT wants to know if there is a simple way to put the current date in a field whenever a record is changed.

    A:Put code on the Form After Update event to write Date() or Now() to this field. This will update the field whenever the record is changed.

    ------------------------------------------------------------------
    I am using Access97 SR-2

    I could not get this to work. I tried using both Date() and =Date()
    to no avail. The following is a description of the field in question and what I did.

    Field: Editdate
    Field Properties: Format: mm/dd/yyyy Default Value: =date()
    Required: No Indexed: No

    (Note: I tried changing the required from "no" to "yes" but this also
    had no effect).

    On the only form for this single table database, I selected design and
    selected the field editdate, selected build event, and on the class module screen, for Editdate and AfterUpdate I added the Date() so that the section read as follows:


    Private Sub EDITDATE_AfterUpdate()
    Date()
    End Sub

    As I mentioned, I tried =Date() and also tried editdate = date()
    Nothing worked to change the date in the editdate field after a change was made to a record. After re-opening the record the field either had the previous date or no date (This database was originally imported from a
    database converted to DBF III and then imported to Access97. Originally, I did not have an adddate field or an editdate field. Therefore, many older records do not have any date in these fields. Newer records automatically add the current date to both fields. However, when I update a record, I have to manually update the editdate field. I was hoping that the AfterUpdate in the event builder would automatically do this for me).

    I would appreciate it if anyone could tell me how to get the AfterUpdate to work. Thanks.

    Allan

    itisi5@netzero.net

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Help - Does Not Work (Access97 SR-2)

    Take a look at:
    <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=24070&page=& view=&sb=&o=&vc=1#Post24070>Previous thread</A>

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Help - Does Not Work (Access97 SR-2)

    Thanks for your reply.
    I tried the following from the previous thread:

    Attach the following to the Before Update Event of the form:

    If Me.Dirty Then
    Me![RefDate] = Date
    End If

    The Dirty property determines whether the current record has been modified since it was last saved.

    I substituted editdate (my field name) for RefDate in the above. The editdate field still does not change to the current system date.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Help - Does Not Work (Access97 SR-2)

    shouldn't it be
    Me![RefDate] = Date()

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Help - Does Not Work (Access97 SR-2)

    I just went back to the application where I used this. I edited a field on the form and when I moved off that record, the RefDate value changed to the system date. Did yours remain unchanged in both the form and the table, or just the form?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Help - Does Not Work (Access97 SR-2)

    You put it in the before update event code of the form, correct?

    I know that's what you said in your previous message, but your very first message seemed to indicate that you were trying to do things in the event code of the editdate field, and I just wanted to make sure you caught the difference.

  7. #7
    New Lounger
    Join Date
    Dec 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Thank to all - it now works -but

    Thanks to JerryC, ThomasW and Douglas Martin for your replies. In answer to all of your inquiries:

    1. Date() become just Date when the build event is selected to modify the form class module

    2. When I could not get the date to change, it did not change in either the form or the table

    3. Douglas Martin led me to the correct solution. I was changing the editdate field on the form class module rather than the FORM itself. Changing editdate to form corrected the problem. Using form_before update using the if me.dirty coding worked perfectly. I tried using the form_afterupdate adding the code editdate=date. While using the form, rather than the field, caused the editdate field to update with the system date, error messages were encountered: the first, upon trying to close the form stated: "you can't save this record at this time. ...Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost." In fact the changes were not lost. When attempting to again open the form (open not design), instead of the form opening, the form class module opened. Attempting to close the module caused a pop-up to state: "this action will reset the current code in break mode. Do you want to stop the running code?....."

    Anyway, as I stated, using the form_beforeupdate works fine. The only thing is that I normally update records not from the form but from a sorted query that I specifically use for adding and editing records.
    It seems that only the form provides a build event menu item which allows for modifying the form class module. I quess I'll have to figure out if this can be done from within the modules tab (rather than the query tab) or perhaps by writing a macro which will update the editdate field with the system date when a record is updated in the query. Any ideas?

    Again, thanks.

    Allan

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AfterUpdate - Thank to all - it now works -but

    I'm not clear on what you mean about changing a field in the form's class module. Class modules don't have fields, only recordsets and tables/queries have fields, and forms and reports have controls which may be bound to those fields.

    I'm also not sure what you mean about using a sorted query to add/edit records. That's the main purpose of a form, so why use a query as well ... unless, of course, you're using an unbound form and either an append or update query?
    Charlotte

  9. #9
    New Lounger
    Join Date
    Dec 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Thank to all - it now works -but

    Hi Charlotte:

    Thanks for your reply.

    Before I address your comments and explain how I use two of my Access97 databases and what I am attempting to accomplish, I would like to give you some background information. I am completely unfamiliar with VB/VBA programming. The syntax is foreign to me. In the early 1960s, at NYU, as a student,I did some Fortran IV programming on the university

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AfterUpdate - Thank to all - it now works -but

    <hr>That is, the code was entered in the section for Form rather than the section for the field name<hr>
    OK, no wonder I was confused. What the dropdown on the left represents is objects in this form, including the form itself. The dropdown on the right represents the events associated with a particular object and any user-defined routines that are not associated with an event.

    <hr>How can I get the editdate field to automatically update to the system date when the record is changed by posting the change to my query rather than to a form?<hr>
    You can't, at least not if you are actually keying data into the query. That's one of the several very good arguments for using forms and *not* using queries or tables for data entry.

    On the other hand, if you're running an update or append query, you can simply use the Date() function to update your date field on the records being updated. The exact mechanics would depend on the SQL for the update query.
    Charlotte

  11. #11
    New Lounger
    Join Date
    Dec 2002
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterUpdate - Thank to all - it now works -but

    HI:

    Sorry I did'nt respond sooner. I am able to accomplish to automatic updaing showing the system date, but as you said, not in a query. I have a form using the query as the record source. When the form is in datasheet view I am able to do the type of posting (copying records and merely updaing one field, etc.) or editing selected records and having the editdate field automatically updated with the system date (current date).

    Originally, I did not know that a form could be in datasheet view so as to show all the records as does a table or query, that is, in a columnar format.

    Regards,

    Allan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •