Convert embedde macros to event procedure

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.
dbfromnewjerseyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
open the form in design view
in the right part of the ribbon, click on Convert Forms macro to Visual Basic
0
 
PatHartmanConnect With a Mentor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
dbfromnewjerseyAuthor Commented:
I did that but it's not affecting the command button.
0
 
dbfromnewjerseyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
it work, see this revised db
TestDatabase.accdb
0
 
Rey Obrero (Capricorn1)Commented:
did you open the subform in design view before clicking on the Convert Macro to visual basic?
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
solutions were offered
0
All Courses

From novice to tech pro — start learning today.