Results 1 to 10 of 10

Convert Private Sub to Public or Function

  1. #1
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46

    Convert Private Sub to Public or Function

    I have a form that I collect information of from callers. On that form are several controls that are used in calculating the total delivery costs for a the delivery the caller is requesting.

    At the bottom of the on the existing record is a TotalCharges field. I have set the double click event of this control (TotalCharges) to create a total charge based on several fields. Everything works fine. I would just like to be able to create a module or function that can be called from any textbox's afterupdate event to calculate the total charges and update the TotalCharges textbox.

    Is it possible to use the private sub code in a module for public function and be able to call it form any field on the form to update the record (total the delivery charges)?

    It is possible to set the focus to TotalCharges and call the double click event to get the update but that means I have to use the mouse to move back to the next field i want to add information to.

    I hope this isn't to vague.
    Thanks, Randy

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    Sure. If you're saying you want to call it from various places on that form, but only that form, then you'd make it a public function within that form module. If you want to call it from any form or report, then you'd want it in a standard module. The advantage to using the form's module is that you can still use "Me." to refer to controls on the form. If you use a standard module, you'll either have to use the full form reference to refer to form controls, or set the function up to accept input parameters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    Thank you for your response.

    If I understand this I can simply change the TotalCharges_DblClick() event to a Public Function and call it from anywhere on the form.

    Thank you,
    Randy

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    Sure; looks like this type of thing:

    Code:
    Private Function CalcTotals()
      Me.D = Me.A + Me.B
    End Function
    
    Private Sub A_AfterUpdate()
      CalcTotals
    End Sub
    
    Private Sub B_AfterUpdate()
      CalcTotals
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,891
    Quote Originally Posted by Randy View Post
    Thank you for your response.

    If I understand this I can simply change the TotalCharges_DblClick() event to a Public Function and call it from anywhere on the form.

    Thank you,
    Randy
    To augment Paul's reply, you do not even need to change the Sub to Public to use it elsewhere on the same form.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    Thank you again... I will try this tomorrow at the office and post the results here.

    Thanks,
    Randy

  7. #7
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    Thank you very much for your help. It worked perfectly except for some of my bad code that i had to alter.

    Thanks again,
    Randy

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    Ho problem Randy, glad you got it working.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,891
    Quote Originally Posted by pbaldy View Post
    Ho problem Randy, glad you got it working.
    That would be Ho as in "Ho...Ho...Ho"! Merry Christmas everyone.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    You thought that was a typo? There's no problem, if it wasn't any, and then there's ho problem, if it was a little bit of one. Then you have...oh never mind, I screwed up.

    Merry Christmas to all!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 2
    Last Post: 12-25-2015, 05:44 PM
  2. Public access to database on internet
    By Katrine in forum Access
    Replies: 3
    Last Post: 12-08-2010, 03:54 PM
  3. VBA public procedure in a form
    By gg80 in forum Programming
    Replies: 3
    Last Post: 09-12-2010, 04:55 AM
  4. Public Terminal Emulation
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-31-2010, 02:09 AM
  5. public instead of dim not working
    By DKY in forum Access
    Replies: 1
    Last Post: 10-14-2008, 11:42 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
  •  
Tech Forums: Microsoft Office Forums