Results 1 to 5 of 5
  1. #1
    nhylan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    12

    Assign converted macro to navigation panel button

    Hi All,
    This is my first time trying to do anything with VBA so please be gentle :-). I've translated a macro which exports a file to a specific save location so I can use concatenate to save it with today's date in the same. Now I can't seem to assign this module to a button on my navigation panel. Would you mind kindly taking a look?

    Private Sub Command58_Click()
    Function Run_Macro()
    On Error GoTo Run_Macro_Err

    DoCmd.OutputTo acOutputQuery, "Query Union", "ExcelWorkbook(*.xlsx)", "S:\stevez\blahblah\Trade Processing Team\foreign exchange\FX Files\bbh_date.xlsx", True, "", , acExportQualityPrint
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "AppendNonAgent", acViewNormal, acEdit
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "AppendAgent", acViewNormal, acEdit




    Run_Macro_Exit:
    Exit Function

    Run_Macro_Err:
    MsgBox Error$
    Resume Run_Macro_Exit

    End Function
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,909
    If you want to leave it as a stand alone function (presuming it's still in a standard module):

    Code:
    Private Sub Command58_Click()
      Run_Macro
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    nhylan is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    If you want to leave it as a stand alone function (presuming it's still in a standard module):

    Code:
    Private Sub Command58_Click()
      Run_Macro
    End Sub
    Pbadly,
    thanks for replying.
    is Run_Macro supposed to be the name of the module? It's not working so I'm wondering if it needs to be entered as "converted macro- Run Macro" which is the actual name of the module.

    Thanks,
    Nate

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,467
    A function is not placed within a sub. Functions and subs are two kinds of procedures. The following structure eliminates the function.

    Private Sub Command58_Click()
    DoCmd.OutputTo acOutputQuery, "Query Union", "ExcelWorkbook(*.xlsx)", "S:\stevez\blahblah\Trade Processing Team\foreign exchange\FX Files\bbh_date.xlsx", True, "", , acExportQualityPrint
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "AppendNonAgent", acViewNormal, acEdit
    DoCmd.OpenQuery "AppendAgent", acViewNormal, acEdit
    DoCmd.SetWarnings True
    End Sub

    If you want to keep the error handler code then replace Run_Macro with Command58_Click
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,909
    Quote Originally Posted by nhylan View Post
    Pbadly,
    thanks for replying.
    is Run_Macro supposed to be the name of the module? It's not working so I'm wondering if it needs to be entered as "converted macro- Run Macro" which is the actual name of the module.

    Thanks,
    Nate
    No, it would be the name of the function. You can post the db here if you have trouble with it.
    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. converted coding issues.
    By d4jones in forum Database Design
    Replies: 2
    Last Post: 07-11-2012, 09:48 AM
  2. Assign F5 function to a button
    By eduardo in forum Forms
    Replies: 4
    Last Post: 06-24-2012, 08:21 AM
  3. Hiding Navigation Pane kills macro
    By SemiAuto40 in forum Programming
    Replies: 3
    Last Post: 12-09-2011, 02:05 PM
  4. Replies: 0
    Last Post: 01-12-2011, 12:43 PM
  5. Replies: 6
    Last Post: 02-09-2010, 07:53 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