Solved

Convert embedde macros to event procedure

Posted on 2016-11-04
8
18 Views
Last Modified: 2016-11-23
I placed a command button on my subform that will perform record deletions as well as some other stuff.   How do I convert the macro to an event procedure since I have to modify the code to do the "other stuff" I referred to?  The deletion code embedded by the wizard in a macro is not sufficient for everything I need the button to do. Thanks.
0
Comment
Question by:dbfromnewjersey
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 125 total points (awarded by participants)
ID: 41874711
open the form in design view
in the right part of the ribbon, click on Convert Forms macro to Visual Basic
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points (awarded by participants)
ID: 41874719
In newer versions of Access -
Open the form in design view.
At the far right of the Design tab of the ribbon is an option -
"Convert Form's Macros to Visual Basic"

Press that.
0
 

Author Comment

by:dbfromnewjersey
ID: 41874756
I did that but it's not affecting the command button.
0
 

Author Comment

by:dbfromnewjersey
ID: 41874782
I've attached a small database with command button in a subform to show what I'm referring to.  When I click on the 'Convery Form's Macros to Visual Basic', it doesn't do anything.
TestDatabase.accdb
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41874840
it work, see this revised db
TestDatabase.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41874842
did you open the subform in design view before clicking on the Convert Macro to visual basic?
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points (awarded by participants)
ID: 41875103
you can run this code to convert all macros behind forms and reports and stand-alone to VBA
Public Sub ConvertAllTheMacros()
'acCmdConvertMacrosToVisualBasic
'160725 strive4peace
'although macros are converted to code, you will have to manually fix places where the program calls those macros

   'set up error handler
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
     , Cnt As DAO.Container _
     , Doc As DAO.Document _
     , obj As Object
   
   Dim sDocument As String _
      , iNumForms As Integer _
      , iNumReports As Integer _
      , iNumMacros As Integer _
      
   
   'set an object variable to the current database
   Set db = CurrentDb
   
'   'close all open forms
'   Call CloseAllForms
'   'close all open reports
'   Call CloseAllReports
         
   iNumForms = 0
   iNumReports = 0
   iNumMacros = 0
   
   'convert macros for Forms
   'set an object variable to the forms container
   Set Cnt = db.Containers("Forms")
   Debug.Print "-------------- Forms --------------"
   For Each Doc In Cnt.Documents
   
      'get the name of the form
      sDocument = Doc.Name
   
      'print name to debug (Immediate) window
      Debug.Print sDocument
      
      'open in design view
      DoCmd.OpenForm sDocument, acDesign
      
      'acknowledge msgbox of what to convert so user is not prompted
      '  Add error handling
      '  include comments
      'False: don't wait to process keystroke -- go to next statement
      SendKeys "{ENTER}", False
      'convert macros to vba
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      'acknowledge "conversion Finished message"
      SendKeys "{ENTER}", False
            
      'close form and save
      DoCmd.Close acForm, sDocument, acSaveYes
      
      iNumForms = iNumForms + 1
         
   Next Doc
   
   'convert macros for Reports
   Set Cnt = db.Containers("Reports")
   Debug.Print "-------------- Reports --------------"
   For Each Doc In Cnt.Documents
   
      'get the name of the Report
      sDocument = Doc.Name
   
      'print name to debug (Immediate) window
      Debug.Print sDocument
      
      'open in design view
      DoCmd.OpenReport sDocument, acDesign
      
      SendKeys "{ENTER}", False
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      SendKeys "{ENTER}", False
            
      'close Report and save
      DoCmd.Close acReport, sDocument, acSaveYes
            
      iNumReports = iNumReports + 1
   Next Doc
   
   'convert macros for stand along Macros
   Debug.Print "-------------- Macros --------------"
   For Each obj In CurrentProject.AllMacros
      iNumMacros = iNumMacros + 1
      
      Debug.Print obj.Name
      DoCmd.SelectObject acMacro, obj.Name, True
      SendKeys "{ENTER}", False
      DoCmd.RunCommand acCmdConvertMacrosToVisualBasic
      SendKeys "{ENTER}", False

   Next obj
   
   MsgBox "Converted " & iNumForms & " macros for forms " _
      & iNumReports & " macros for reports " _
      & iNumMacros & " macros  ", ,"Done"

   
Proc_Exit:
   On Error Resume Next
   'release object variables
   Set obj = Nothing
   Set Doc = Nothing
   Set Cnt = Nothing
   Set db = Nothing
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   ConvertAllTheMacros "

   Resume Proc_Exit
   ' Ctrl-Break the MsgBox... then Debug
   ' then set Resume to be the next statement by right-clicking on it
   '            and choosing Set Next Statement from the shortcut menu
   ' then press F8 to step through code one line at a time to see what is wrong
   Resume
   
End Sub

Open in new window

0
 
LVL 19
ID: 41898878
solutions were offered
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now