How to use a macro to run code

[bharlow]

I have a form with a singe button that on click runs the following code...

Private Sub cmdEmailIndividualCustReports1_Click()
On Error GoTo Err_cmdEmailIndividualCustReports_Click
'Declare Variables and Objects
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strStore As String
Dim strRcpt As String
'Set Variables and Objects
strSQL = "SELECT Store,Recipient From DISTTABLE2"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)

'rst.MoveLast
rst.MoveFirst
'MsgBox rst.RecordCount

'Turns off the screen
DoCmd.Echo False
Do While Not rst.EOF
strStore = rst!Store
strRcpt = rst!Recipient
'If Store is a number change to: "Store=" & strStore
DoCmd.OpenReport "RPT-B2B1", acViewPreview, , "STR=" & strStore
DoCmd.SendObject acSendReport, "RPT-B2B1", acFormatSNP, strRcpt, , , "Bed to Bedroom Analysis", , False
DoCmd.Close acReport, "RPT-B2B1"
rst.MoveNext
Loop
DoCmd.Echo True

'Recordset Cleanup
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit_cmdEmailIndividualCustReports_Click:
'Avoids the endless loop
On Error Resume Next
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_cmdEmailIndividualCustReports_Click:
MsgBox "There was an error executing the command." _
& vbCrLf & vbCrLf & "Error " & Err.Number & ": " _
& vbCrLf & vbCrLf & Error, vbExclamation
Resume Exit_cmdEmailIndividualCustReports_Click
End Sub

What I am trying to figure out is.... How to create a Macro that will basically do the same thing as clicking the button?

 

[Mike375]

The code needs to be in a module as a function and the macro action line RunCode calls the function name (not the module name)

If the objective is to be able to run the code from the data base window than that does it.

As to making a macro to do what you want that may or may not be possible. Macros are limited when it comes to variables and you can be close to 100% certain that for a macro to do it a form or forms will need to be open in which case you might as well stick with what you have since either way it will be a "click" on a button or label on an open form.