Results 1 to 8 of 8
  1. #1
    trout is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    5

    Embedded macro on form

    Hi all,



    I have an embedded macro on one of my forms that autopopulates the Age after the date of birth is entered. When the form is in Edit mode, this macro works just fine. However, it does not work when the form is in Add mode. Is there a setting I need to change to make this work? I have tried using the Macro After Update on the DOB field and OnClick on the Age field.

    Thanks in advance!

  2. #2
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) Are you saying that you have a field "Age" on the table? And you realize that every month, about 8.33% of your records become wrong? Do you have a scheduled update query to fix the age periodically?

    2) Bind the date-of-birth field to txtDOB control on the form, bind the Age to txtAge on the form. In the AfterUpdate on the txtDOB control, calculate and set the value of the txtAge field. Macro should work just fine.

    3) I don't generally use macros, and I don't ever use embedded macros, since VB behind the form is more visible, and macros make debugging very mysterious. (I don't do web development, so YMMV.)

  3. #3
    trout is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    5
    Hi, thanks for replying. There is an age field in the table, because we are interested in the age at first visit, so it doesn't matter that it will be out of date. Anyway, I just tried doing this with VBA code from your last suggestion (using Code Builder from the AfterUpdate option), and I still have the same problem where it works in Edit Mode, but not Add mode.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Why are you messing with add vs edit mode at all and as Dal pointed out you're trying to store a calculated value there's no reason to do that. If your initial contact date is stored in your database you can calculate their age at the time of contact without storing it. You really, really want to stay away from storing any calculated value even if it's a one time deal.

  5. #5
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    rpeare - trout just said it's not Age, it's AgeAtFirstVisit, a field which will not change.

    trout - move it to BeforeUpdate and see what happens. I'm not sure what the Add Mode functionality is doing. Also, for your tests are you entering this as the first value in the record, and is that what the user will actually do?

  6. #6
    trout is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2013
    Posts
    5
    Dal Jeanis, I was just entering it as the first value and when I started the form from the beginning, it worked fine. Thank you!

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes, I know he said age at first visit, it's still a calculated value, even if it's one time, but he appears to have a result he's happy with so the question is moot.

  8. #8
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, that kind of makes sense. The very first field entered kicks off the new record creation event (I've forgot the event name), so it might not kick off the After Update Event.

    Please mark thread solved. top of page, under thread tools.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-30-2013, 09:59 AM
  2. Embedded Macro not there
    By notacluewhatimdoing in forum Forms
    Replies: 4
    Last Post: 03-23-2013, 08:04 AM
  3. Replies: 3
    Last Post: 02-15-2013, 03:36 PM
  4. Embedded Macro
    By sireesha in forum Forms
    Replies: 1
    Last Post: 12-08-2012, 02:28 PM
  5. Replies: 0
    Last Post: 07-13-2010, 07:45 AM

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