JustJerry
08-05-2008, 03:31 PM
Hello everyone,
I have the following code that works flawlessly when running Access 2003 and Word 2003:
Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
Dim intAnswerMe As Integer
'Fill in Work Order Description if blank to avoid Null error in Word
If IsNull(Me.WODesc.Value) Or _
Me.WODesc = 0 Then
intAnswerMe = MsgBox("Please enter in a Work Order Description", vbOKOnly + vbInformation, "Description Needed")
Me.WODesc.SetFocus
Exit Sub
End If
Dim oApp As Object 'Variable for Word
Dim sFilename As String 'Variable for Auto-Save file name
Dim strTemplateName As String 'Variable for Word Template to be used
Dim objWORDdoc As Object
'Save Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Create path to Quotation Template
strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot (file://\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot)"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\ (file://\\Sharppc\ServerFiles\ProjectData\Quotations\)" & Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc"
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
If Dir(sFilename) = "" Then 'Test to see if created filename already exists
'and if not, open Template to fill in date
Set oApp = CreateObject("word.basic") 'otherwise just open that filename already
With oApp
.filenew Template:=strTemplateName
'Set bookmarks in QuotationTemp to equal values of new Quoation Number created
.EditBookmark Name:="quotedate", GoTo:=True
.Insert (Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
.EditBookmark Name:="quotenum", GoTo:=True
.Insert (CStr(Me.WONum)) 'insert work order number
.EditBookmark Name:="companyname", GoTo:=True
.Insert (CStr(Me.CompanyName)) 'insert company name
'This code will make sure that if the street address has two lines, both are inserted
If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) Or _
DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") = 0 Then
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 address
.Insert ((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
Else
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 & 2 address
.Insert (CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & vbCrLf & (CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))
End If
.EditBookmark Name:="citystate", GoTo:=True
.Insert ((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & ", " & (CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & " " & (CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
.EditBookmark Name:="custname", GoTo:=True
.Insert (CStr(Me.Contact))
.EditBookmark Name:="subject", GoTo:=True
.Insert (CStr(Me.WODesc))
.EditBookmark Name:="firstname", GoTo:=True
.Insert (CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & ",")
.filesaveas Name:=sFilename 'save Quotation with auto save name
End With
Else 'If filename already exists, just open the file at this point
oApp.Documents.Open sFilename
oApp.ActiveDocument.Save
End If
Exit_RunWordTemplate_Click:
Exit Sub
Err_RunWordTemplate_Click:
MsgBox Err.Description
Resume Exit_RunWordTemplate_Click
My problem comes from two users that have Access 2003 and Word 2007. Apparently there is an issue that arrises when this code is ran and the file is saved under Word 2007, the file can only be looked at using 2007 - even though it has the .doc extention and not the .docx extention. When the Word 2003 users try to access the file, they get an error asking to select the encoding to make the document readable.
I 'think' the issue comes from this line of the code:
.filesaveas Name:=sFilename 'save Quotation with auto save name
I know in Word 2007 you have to select the SaveAs 97-2003 version for the .doc filename.
My questions are these:
1) Is there some code that I would need to run to check the version of word running and have one save line for the 2003 users, and one save for the 2007 users?
2) Is there some option I am missing under the .filesaveas Name:=sFileName that would solve this issue?
Also, when the 2003 user runs this code, either user can read the file without issue.
Thank you for any help
I have the following code that works flawlessly when running Access 2003 and Word 2003:
Private Sub RunWordTemplate_Click()
On Error GoTo Err_RunWordTemplate_Click
Dim intAnswerMe As Integer
'Fill in Work Order Description if blank to avoid Null error in Word
If IsNull(Me.WODesc.Value) Or _
Me.WODesc = 0 Then
intAnswerMe = MsgBox("Please enter in a Work Order Description", vbOKOnly + vbInformation, "Description Needed")
Me.WODesc.SetFocus
Exit Sub
End If
Dim oApp As Object 'Variable for Word
Dim sFilename As String 'Variable for Auto-Save file name
Dim strTemplateName As String 'Variable for Word Template to be used
Dim objWORDdoc As Object
'Save Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Create path to Quotation Template
strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot (file://\\Sharppc\ServerFiles\ProjectData\Quotations\QuotationTemp.dot)"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\ (file://\\Sharppc\ServerFiles\ProjectData\Quotations\)" & Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc"
Set oApp = CreateObject("Word.Application")
oApp.Visible = True
If Dir(sFilename) = "" Then 'Test to see if created filename already exists
'and if not, open Template to fill in date
Set oApp = CreateObject("word.basic") 'otherwise just open that filename already
With oApp
.filenew Template:=strTemplateName
'Set bookmarks in QuotationTemp to equal values of new Quoation Number created
.EditBookmark Name:="quotedate", GoTo:=True
.Insert (Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
.EditBookmark Name:="quotenum", GoTo:=True
.Insert (CStr(Me.WONum)) 'insert work order number
.EditBookmark Name:="companyname", GoTo:=True
.Insert (CStr(Me.CompanyName)) 'insert company name
'This code will make sure that if the street address has two lines, both are inserted
If IsNull(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) Or _
DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'") = 0 Then
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 address
.Insert ((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
Else
.EditBookmark Name:="address", GoTo:=True 'insert Line 1 & 2 address
.Insert (CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & vbCrLf & (CStr(DLookup("[CustAdd2]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))
End If
.EditBookmark Name:="citystate", GoTo:=True
.Insert ((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & ", " & (CStr(DLookup("[StateID]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & " " & (CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))))
.EditBookmark Name:="custname", GoTo:=True
.Insert (CStr(Me.Contact))
.EditBookmark Name:="subject", GoTo:=True
.Insert (CStr(Me.WODesc))
.EditBookmark Name:="firstname", GoTo:=True
.Insert (CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")) & ",")
.filesaveas Name:=sFilename 'save Quotation with auto save name
End With
Else 'If filename already exists, just open the file at this point
oApp.Documents.Open sFilename
oApp.ActiveDocument.Save
End If
Exit_RunWordTemplate_Click:
Exit Sub
Err_RunWordTemplate_Click:
MsgBox Err.Description
Resume Exit_RunWordTemplate_Click
My problem comes from two users that have Access 2003 and Word 2007. Apparently there is an issue that arrises when this code is ran and the file is saved under Word 2007, the file can only be looked at using 2007 - even though it has the .doc extention and not the .docx extention. When the Word 2003 users try to access the file, they get an error asking to select the encoding to make the document readable.
I 'think' the issue comes from this line of the code:
.filesaveas Name:=sFilename 'save Quotation with auto save name
I know in Word 2007 you have to select the SaveAs 97-2003 version for the .doc filename.
My questions are these:
1) Is there some code that I would need to run to check the version of word running and have one save line for the 2003 users, and one save for the 2007 users?
2) Is there some option I am missing under the .filesaveas Name:=sFileName that would solve this issue?
Also, when the 2003 user runs this code, either user can read the file without issue.
Thank you for any help