Results 1 to 12 of 12
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Red face AfterUpdate event help

    Need some quick assistance with an AfterUpdate event.



    I would like to have the event add the entered number on a subform to a number on the main form.

    I have attached an example photo.

    When someone adds a new rental extension amount of days I'd like the afterupdate event to add that number to the existing "Days Needed" field on the main form.

    Can this be done?

    TIA.

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    254

    10 Guage -

    You could try...

    dim x as Variant
    dim y as Variant
    x = [Forms]![MainFormName]![SubformName].Form![ExtensionDaysControlName]
    y = [Forms]![MainFormName]![DaysNeededControlName]

    [Forms]![MainFormName]![DaysNeededControlName] = (x + y)

    Hope this helps,

    Jim

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No photo attached, and it sounds like a design issue if you're storing days in two different places, but:

    Forms!FormName.DaysNeeded = Me.NumberOfDays

    with all the names changed as appropriate. If you're trying to add on:

    Forms!FormName.DaysNeeded = Forms!FormName.DaysNeeded + Me.NumberOfDays
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Thanks to both of you.

    I used pbaldy's method and it worked perfectly.

    pbaldy, there are two different "types" of dates needed I guess you could say.

    One field is the running total of original amount of days needed plus any extension days.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. What are you going to do if the user makes a mistake and deletes the record you just updated the main form with? Or changes 2 days to 3? The perils of storing a value like that. You have to account for every possible way a user can add/edit/delete data.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by pbaldy View Post
    Happy to help. What are you going to do if the user makes a mistake and deletes the record you just updated the main form with? Or changes 2 days to 3? The perils of storing a value like that. You have to account for every possible way a user can add/edit/delete data.
    Yes that is the stipulation for sure....

    What if I added a field for "total days needed" (currently just days needed) and made "days needed" the initial amount of days needed. Then I could make a formula for "total days needed" (unbound) that would be the "days needed" + the DSum of the extension days? (would probably need a refresher on how to get the DSum to to only calculate the days given for a specfic RentalID).

    Something like this:

    =[DaysNeeded]+DSum("ExtensionDays","Extensions","RentalID =" & [RentalID])

  7. #7
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    =[DaysNeeded]+DSum("ExtensionDays","Extensions","RentalID =" & [RentalID])

    Seems to work, but the text box does not autopopulate with that number, how can I get it to populate on form load?

    Edit: Nevermind it looks like it's loading.....


    My next issue is if I don't have any rental extensions my "total days" box does not populate with a number (which means my expected return date doesn't display).

    Will I need an Iif statement?

    I tried the following but I am getting an invalid syntax message:

    =Iif(DSum("ExtensionDays","Extensions","RentalID =" & [RentalID]) = null, "[DaysNeeded]", "[DaysNeeded]+DSum("ExtensionDays","Extensions","RentalID =" & [RentalID])")

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can wrap either side in an Nz() function to get around the Null value.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by pbaldy View Post
    You can wrap either side in an Nz() function to get around the Null value.
    I'm sorry I don't follow, wrap either side of what?

    =Iif(Nz(DSum("ExtensionDays","Extensions","RentalI D =" & [RentalID]), "[DaysNeeded]"), "DSum("ExtensionDays","Extensions","RentalID =" & [RentalID])+[DaysNeeded]")

    Like that?

    I am still getting a syntax error.

  10. #10
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Ok looks like the syntax error was with the quotes around the last part of the Iif statement.

    I ended up with this, and it seems to be working! Thanks for the Nz reference pbaldy.

    =IIf(Nz(DSum("ExtensionDays","Extensions","RentalI D =" & [RentalID]),0)=0,[DaysNeeded],DSum("ExtensionDays","Extensions","RentalID =" & [RentalID])+[DaysNeeded])

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted out. Does this work?

    =Nz([DaysNeeded], 0) + Nz(DSum("ExtensionDays","Extensions","RentalID =" & [RentalID]), 0)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Thanks Paul, that does work as well.

    Leave it to me to find the most bloated way possible to get something done, haha.

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

Similar Threads

  1. AfterUpdate event code error?
    By agripa86 in forum Programming
    Replies: 3
    Last Post: 08-12-2011, 09:12 AM
  2. how to created afterupdate event
    By Brigitt in forum Forms
    Replies: 2
    Last Post: 02-15-2011, 03:23 AM
  3. Replies: 0
    Last Post: 02-01-2011, 04:49 AM
  4. User focus control in AfterUpdate event
    By nosliwmada@gmailcom in forum Programming
    Replies: 3
    Last Post: 12-20-2010, 12:51 PM
  5. AfterUpdate event won't refresh subform!
    By Remster in forum Forms
    Replies: 16
    Last Post: 11-26-2010, 10:06 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