Results 1 to 11 of 11
  1. #1
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31

    Writing Code for "After Update" Table Events

    I'd like to write VBA code for when a table's "After Update" event is triggered but all I can seem to access is the macro designer. Is there any way to write VBA code for that event? If not, why is that restricted? (If it matters, I'm using an Access 2007 DB.)



    Dan

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you confirm your version? 2010 has data macros that would fire when a table is updated; 2007 does not. In 2007 you would need to use a form's after update event. For VBA, you may still need to do that, as I don't know if you can run VBA from a data macro offhand. My gut feeling is not. I don't have 2010 in front of me to check.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    Quote Originally Posted by pbaldy View Post
    Can you confirm your version? 2010 has data macros that would fire when a table is updated; 2007 does not. In 2007 you would need to use a form's after update event. For VBA, you may still need to do that, as I don't know if you can run VBA from a data macro offhand. My gut feeling is not. I don't have 2010 in front of me to check.
    I am using 2010.

    Dan

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I fired up a VM with 2010, and I don't see offhand a way to run VBA from a data macro. However, a quick Google finds my friend Tom with a workaround:

    http://social.msdn.microsoft.com/For...6-dd5bcad36c6b
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    I have created a public function "AddActivityRecord" declared as follows:

    Code:
    Public Function AddActivityRecord(something As String) As Integer
    AddActivityRecord = 0
    End Function
    I've given it a break point and attempted to call it using SetLocalVar, but I receive the error: "The identifier '[AddActivityRecord]' could not be found."

    Any ideas why this wouldn't be working?

    Dan

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The brackets make me wonder if it thinks it's a field. Make sure the call includes parentheses, and since your function requires a value, a value:

    AddActivityRecord("test")
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    Thanks, Paul. I have tried AddActivityRecord(), AddActivityRecord("Test"), AddActivity "Test", and a few other variations, but I receive the same error message.

    Dan

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I have only used data macros in very limited testing, so I'm not really familiar with them. You're using that as Tom described? Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    Yes, I attempted to use it as described. I would post the DB but it is larger than the 500K file size limit.

    Dan

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you compact/repair and then zip? That will get most files small enough.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    Good call. I just assumed accdb files were zipped like other office xml documents, although in retrospect that doesn't really make sense for a database. Please see attached.

    The goal is to write a record to my "ActivityLog" table whenever changes are made to a table using the "AfterUpdate" event.

    Thank you,

    Dan
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-06-2012, 09:14 AM
  2. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  3. Replies: 0
    Last Post: 01-11-2012, 12:34 PM
  4. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  5. Replies: 9
    Last Post: 05-19-2011, 12:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums