How can run a Public Function from a button on a Form?

[David Ball]

Hi Forum,

I have some code (see below) that merges fields from an Access database form into a Word template. It is in a VBA module and works well.
I want to be able to run it from a button on the form. I have set up a button on the form and want to be able to run this code from the button's On-Click event. I have tried to just cut and paste the code from the module but it doesn't work.

How can I get this code to run from the button on my form?

Thanks very much.

D B.

Public Function MergetoWord()
'This method creates a new document in MS Word
'using Automation
On Error Resume Next
Dim rsCert As Recordset, iTemp As Integer
Dim WordObj As Word.Application
Set rsCert = DBEngine(0).Databases(0).OpenRecordset("tblComplCert", dbOpenTable)
rsCert.Index = "PrimaryKey"
rsCert.Seek "=", Forms!CCert![Plant]
If rsCert.NoMatch Then
MsgBox "Invalid record dude", vbOKOnly
Exit Function
End If
DoCmd.Hourglass True
Set WordObj = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If
WordObj.Visible = True
WordObj.Documents.Add ("C:\Access Automation\Test.doc")
WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Plant"
WordObj.Selection.TypeText rsCert![Plant]

WordObj.Selection.GoTo what:=wdGoToBookmark, Name:="Area"
WordObj.Selection.TypeText rsCert![Area]



Set WordObj = Nothing
DoCmd.Hourglass False
Exit Function
TemplateError:
Set WordObj = Nothing
Exit Function


End Function

 

[pbaldy]

It would look like:

Private Sub ButtonName_Click()
  MergetoWord
End Sub

 

[David Ball]

So easy! Thanks very much.

Just tried and it works great.


Cheers

D B.

 

[pbaldy]

Happy to help!

 

[Galaxiom]

Since you have On Error Resume Next the Error handler will never be used.
Even if it was, your function would leave the hourglass on after any error.

It is a good idea to use a single exit point for a sub or function with a Resume directed from the Error handler to that Exit. Much like the procedures made by the Button Wizard.

This way you only need the single line setting the WordObject to Nothing.
In that Exit also include the DoCmd Hourglass False

It is also a very common bad practice to Exit a function from the middle of the code.
Properly designed If Then Else/ElseIf structures can work through the code in an orderly way to the single Exit Function.

Also note that this site provides a code box which allows the code to be formatted with indentation making it much easier to read. See the # button on the advanced editor.