Problem with Populating a Word Document from Access 2003

G

gm.miller

I have this database that has worked great up until now. When
created, all users in the office were using Access 2003 and Word
2003. Now, two computers are using Access 2003 and Word 2007.

The problem has something to do with the 2007 users when the word
document is saved. Only these users can further open up the document
to view/edit. The 2003 users, when trying to open up the document get
nothing but gargle and a pop up box to select the encoding method to
view the document. However, when the 2003 users run this database and
create a new document, it is useable by all users still.

I know that in Word 2007, you can select SaveAs 97-2003 document when
saving, so I am thinking that I have to change the code accordingly
somehow.

Here is my code that works under the 2003 Office system:

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"
'Create default SaveName for New Quotation being written
sFilename = "\\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

End Sub
***********************************************

I have tried replacing this line:

..filesaveas Name:=sFilename 'save Quotation with auto save name

with this code:

If oApp.Version = 12 Then
.filesaveas Name:=sFilename, _
.FileFormat:=0
Else
.filesaveas Name:=sFilename 'save Quotation with auto save name
End If

But, this does not work either.

It was suggest that this line of code:

Set oApp = CreateObject("Word.Basic")

might be casuing me an issue, but I am pretty much clueless as to how
to fix this. I am barely over the newbie range and still learning on
the fly, and if someone can point out how to fix this code to work so
that it will check to see which version of Word is running and save
the file in a 97-2003 format accordingly, I would be MUCH
appreciative. I have been looking online and can't seem to pinpoint
anything down that works.

THank you, Jerry
 
P

Peter Jamieson

You /probably/ need to specify the file format in the .SaveAs as
wdFormatDocument (available in both Word 2003 and Word 2007 object models).
This has value 0 and should result in a save as 97-2003 format in Word 2007.

--
Peter Jamieson
http://tips.pjmsn.me.uk

I have this database that has worked great up until now. When
created, all users in the office were using Access 2003 and Word
2003. Now, two computers are using Access 2003 and Word 2007.

The problem has something to do with the 2007 users when the word
document is saved. Only these users can further open up the document
to view/edit. The 2003 users, when trying to open up the document get
nothing but gargle and a pop up box to select the encoding method to
view the document. However, when the 2003 users run this database and
create a new document, it is useable by all users still.

I know that in Word 2007, you can select SaveAs 97-2003 document when
saving, so I am thinking that I have to change the code accordingly
somehow.

Here is my code that works under the 2003 Office system:

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"
'Create default SaveName for New Quotation being written
sFilename = "\\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

End Sub
***********************************************

I have tried replacing this line:

.filesaveas Name:=sFilename 'save Quotation with auto save name

with this code:

If oApp.Version = 12 Then
.filesaveas Name:=sFilename, _
.FileFormat:=0
Else
.filesaveas Name:=sFilename 'save Quotation with auto save name
End If

But, this does not work either.

It was suggest that this line of code:

Set oApp = CreateObject("Word.Basic")

might be casuing me an issue, but I am pretty much clueless as to how
to fix this. I am barely over the newbie range and still learning on
the fly, and if someone can point out how to fix this code to work so
that it will check to see which version of Word is running and save
the file in a 97-2003 format accordingly, I would be MUCH
appreciative. I have been looking online and can't seem to pinpoint
anything down that works.

THank you, Jerry
Click to expand...
 
G

gm.miller

Are you talking about something like this?

If oApp.Version = 12 Then
.filesaveas Name:=sFilename, _
FileFormat:=wdFormatDocument
Else
.filesaveas Name:=sFilename 'save Quotation with auto save name
End If

I put this in but I get the error that 'wdFormatDocuemtn' is not a
defined variable
 
P

Peter Jamieson

If oApp.Version = 12 Then
.filesaveas Name:=sFilename, _
FileFormat:=wdFormatDocument
Else
.filesaveas Name:=sFilename 'save Quotation with auto save name
End If
Click to expand...

More or less, but I don't think you even need to test for Version 12 as I
think the value wdFormatDocument does the right thing in both 2003 and 2007.
I assume (without checking) that leaving out the parameter probably means
that at least Word 2007 and possibly both versions will use whatever
document format is set to be the default save format, which may vary from
user to user.
I put this in but I get the error that 'wdFormatDocuemtn' is not a
defined variable
Click to expand...

It won't be unless you reference the Word object model in the VBA Editor,
but since you're probably trying to avoid that so you can work with both
versionso f Word, you could either just use the value 0 or if you prefer to
use names, set your own Const and use that
 
G

gm.miller

More or less, but I don't think you even need to test for Version 12 as I
think the value wdFormatDocument does the right thing in both 2003 and 2007.
I assume (without checking) that leaving out the parameter probably means
that at least Word 2007 and possibly both versions will use whatever
document format is set to be the default save format, which may vary from
user to user.


It won't be unless you reference the Word object model in the VBA Editor,
but since you're probably trying to avoid that so you can work with both
versionso f Word, you could either just use the value 0  or if you prefer to
use names, set your own Const and use that

--
Peter Jamiesonhttp://tips.pjmsn.me.uk








- Show quoted text -
Click to expand...

I got it to work thanks to some help from other users on two different
forums. In case anyone want to know, here is the code:

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 Word.Application '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 Word.Document

'Save Record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

'Create path to Quotation Template
strTemplateName = "\\Sharppc\ServerFiles\ProjectData\Quotations
\QuotationTemp.dot"
'Create default SaveName for New Quotation being written
sFilename = "\\Sharppc\ServerFiles\ProjectData\Quotations\" &
Me.WONum.Value & " - " & Me.CompanyName.Value & ".doc"

Set oApp = New 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 objWORDdoc = oApp.Documents.Open(strTemplateName)

objWORDdoc.Bookmarks("quotedate").Range.Text =
(Format(Me.WODate, "mmmm dd, yyyy")) 'insert date of quote
objWORDdoc.Bookmarks("quotenum").Range.Text = (CStr(Me.WONum))
'insert work order number
objWORDdoc.Bookmarks("companyname").Range.Text =
(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
objWORDdoc.Bookmarks("address").Range.Text =
((CStr(DLookup("[CustAdd1]", "ContactsTbl", "[Contact]='" &
Me.Contact.Value & "'"))))
Else
objWORDdoc.Bookmarks("address").Range.Text = (CStr(DLookup
_
("[CustAdd1]", "ContactsTbl", "[Contact]='" &
Me.Contact.Value & "'"))) & vbCrLf & (CStr(DLookup("[CustAdd2]",
"ContactsTbl", "[Contact]='" & Me.Contact.Value & "'")))
End If

objWORDdoc.Bookmarks("citystate").Range.Text =
((CStr(DLookup("[CustCity]", "ContactsTbl", "[Contact]='" &
Me.Contact.Value & "'"))) & ", " & (CStr(DLookup("[StateID]",
"ContactsTbl", "[Contact]='" & Me.Contact.Value & "'"))) & " " &
(CStr(DLookup("[CustZip]", "ContactsTbl", "[Contact]='" &
Me.Contact.Value & "'"))))
objWORDdoc.Bookmarks("custname").Range.Text =
(CStr(Me.Contact))
objWORDdoc.Bookmarks("subject").Range.Text = (CStr(Me.WODesc))
objWORDdoc.Bookmarks("firstname").Range.Text =
(CStr(DLookup("[fName]", "ContactsTbl", "[Contact]='" &
Me.Contact.Value & "'")))

objWORDdoc.SaveAs sFilename 'save Quotation with auto save
name

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
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access - Write to Word Table 0
Problem with VBA running a Word mail merge 3
Problem with format when printing to Word from Access 2
Pass control name from Access to Word and save text in the control 4
Print section of a word document from an access button click event. 2
MailMerge from Access to Word 2003 0
Printing a Word Document from Access 2003 1
Pass control name from Access to Word 0

Top