Another Access 2010 problem

[NISMOJim]

Here we go again...
I have a form that the user fills in with a student's information, then opens a Word document to display a Certificate of Completion. This worked fine in Access 2007. Here is the code...

Private Sub cmdCert_Click()
'Opens Certificate.docx with the correct labels visible
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCertificate"

'DoCmd.OpenForm stDocName, acViewNormal
Const DOC_PATH As String = "T:\PG Operations\Training Programs\Certificates\"
Const DOC_NAME As String = "CofT.docx"
Dim appWord As Word.Application
Dim doc As Word.Document

On Error Resume Next
Set appWord = GetObject(, "Word.application")
If Err = 429 Then
Set appWord = New Word.Application
Err = 0
End If

With appWord
Set doc = .Documents(DOC_NAME)
If Err = 0 Then


doc.Close False
End If
On Error GoTo ErrorHandler

Set doc = .Documents.Open(DOC_PATH & DOC_NAME, , True)

With doc
.FormFields("EName").Result = Forms!frmCertificate![EName]
.FormFields("Comp").Result = Forms!frmCertificate![Comp]
.FormFields("Inst").Result = Forms!frmCertificate![Inst]
If Me.DTrng <> "" Then
.FormFields("Trng").Result = Forms!frmCertificate![DTrng]
ElseIf Me.STrng <> "" Then
.FormFields("Trng").Result = Forms!frmCertificate![STrng]
ElseIf Me.ETrng <> "" Then
.FormFields("Trng").Result = Forms!frmCertificate![ETrng]
ElseIf Me.ERTrng <> "" Then
.FormFields("Trng").Result = Forms!frmCertificate![ERTrng]
End If
End With
.Visible = True
.Activate
End With

Set rst = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub

ErrorHandler:
MsgBox Err & Err.Description

End Sub

It's hanging up at the "Dim appWord As Word.Application" line with the error message saying "Compile Error: User-defined type not defined. Is there maybe a Reference that I need to check under Tools to get this working again, or does it just need to be written different for Access 2010?

[ItsMe]

I think it is like ActiveX word or something???? Can't recall but I believe you need a reference. I posted some late binding code on here in the last few days. Post up if you don't get it working quickly

[June7]

Yes, probably have to change the library reference for newer version of Word: Microsoft Word 14.0 Object Library.

However, if you have the older library reference selected, should be getting a MISSING library error.

While you're at it, do you have code for Excel and Outlook also?

Why use Word for the certificate as opposed to an Access report?

[NISMOJim]

Adding MS Word 14.0 fixed the certificate problem. I also went through & added MS Outlook to the references, but I'm still getting an error on 2 other buttons that are supposed to bring up an e-mail. Here's the first...

Private Sub Command48_Click()
'Opens Outlook & fills in E-mail message to expiring local permit holders
On Error GoTo ErrorHandler
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim sBody As String
Dim db As Database
Dim rst As Recordset

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

ExpirationDate = Date
ExpirationDate = ExpirationDate + 29
Expire = Format(ExpirationDate, "mmmm yyyy")

With MailOutLook
.BodyFormat = olFormatRichText
.To = ""
.Subject = "Permit Expiration Notice"
Set db = CurrentDb
Set rst = db.OpenRecordset("qryExpire")
rst.MoveFirst
Do While rst.EOF <> True
sBody = sBody & rst.Fields(1) & ", " & rst.Fields(2) & "*" & "*" & "*" & "*" & "*" & nbsp & rst.Fields(3) & "<BR>"
rst.MoveNext
Loop
rst.Close
Set db = Nothing

.HTMLBody = " The following employee's driving permits have expired, or will expire on the first of " & Expire & ". Please contact Operations to schedule a renewal date." & "<BR>" & "<BR>" & sBody
.Display
End With

Exit Sub

ErrorHandler:
If Err = 3021 Then
MsgBox "There are no records to show."
Else: MsgBox Err.Description
End If

End Sub

This also worked in 2007, but now I get an error that just says "Type Mismatch" with an "OK" button that just closes the error box.

[ItsMe]

Maybe it does not like the text in the body because it does not have the HTML tags

.HTMLBody = " The following...

Should probably start something like

.HTMLBody = "<HTML><BODY> The following...

and close with

...</HTML></BODY>"

[June7]

No, those tags are not required. Something else is causing issue. I tried your code and get an error on nbsp that it is not declared (I have Option Explicit in my modules). What is nbsp for?

However, am working with 2007 right now. I can try 2010 tonight.

[ItsMe]

nbsp is to indicate white space in HTML

[June7]

VBA doesn't see it as HTML.

Is nbsp a variable or custom constant? Where is it declared and set?

Which line throws the error? Comment out the On Error GoTo and step debug, set breakpoint - refer to link at bottom of my post for debugging guidelines.

[NISMOJim]

I probably got the nbsp part from someone helping me with the code on this forum a while back, but I don't know what it is. It did work fine up until the change to 2010 though. Now I'm getting a Run-Time Error '13': Type Mismatch on the line Set rst = db.OpenRecordset ("qryExpire"). I checked the query & it works, showing the correct information.

[ItsMe]

Try

Dim db As dao.Database
Dim rst As dao.Recordset

[NISMOJim]

That did the trick! So many nuances I'll never get them all down. Thank you for the help, you guys rock!