How do I run a module from a button?

[PJ Crittenden]

You folks helped me by creating a module to populate something in a table...it works great. Now I've created a button, but I can only get it to open the module, and I need it to run the module. How do I do that?



Here's the code for the module:


Option Explicit
Sub PopulateHeader()
Dim rs As DAO.Recordset, strHeader As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Raw_Data WHERE Header Is Null ORDER BY ID;")
While Not rs.EOF
If rs!Field6 = "T0" And strHeader <> Left(rs!Field1, 19) Then
strHeader = Left(rs!Field1, 19)
Else
rs.Edit
rs!Header = strHeader
rs.Update
End If
rs.MoveNext
Wend
End Sub

[ItsMe]

...it works great..

How do you know it works great? How are you calling the procedure now?

From within the same module, VBA looks like this
Call PopulateHeader

[PJ Crittenden]

I open the module and click on the little green Run subform button.

[ItsMe]

I open the module and click on the little green Run subform button.

You can automate the process by using VBA
Call PopulateHeader

However, if PopulateHeader is not within the same module (that your Call PopulateHeader line of code is in) it will not work. In other words, you need PopulateHeader to be a public function. There are other ways, but I am not going to get into that here.

To create a Public Function, you need your code block to be in a Standard Module and you need the Function Declaration Line to be
Public Function PopulateHeader()
instead of
Sub PopulateHeader()

And the Signature Line to be
End Function
instead of
End Sub

Just remember that all of your code that is the Public Function needs to be in a Standard Module in order to be available from any other module.

[PJ Crittenden]

Got it to work. Thank you!

[June7]

If omit the Public keyword, Access will default to public.

If you have a button on a form to run this code, instead of a call to code in standard (general) module, the code can be in the button Click event procedure in the module behind the form, as explained in your other thread.

Unless you want to call the procedure from multiple locations, suggest you put it in the button Click event - just less complication.

[ItsMe]

...If omit the Public keyword, Access will default to public...

I was not sure why the OP had a Sub Procedure and did not want to get too deep into Public Interface vs. Private interface.

Everything can go into a click event, for sure... Just as easy to call the sub from a click event within the same Module.