Need Help Badly

J

Joe Cilinceon

I'm sending the contents of a form to Microsoft Word as a merge file. The
code for this procedure is from http://support.microsoft.com/?kbid=210271 It
works great except for removing the bookmarks which gives me an error.

The line that hangs is as follows
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = "" ' Chokes on this line
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If

The error I get is Run Time Error #91
Object varialble or With Block variable no set

Any one have a clue as to what I'm missing. I'm new at this and have asked a
couple of times.
 
B

Brendan Reynolds

I'll take a look at the URL you posted. In the meantime, I'd say the most
likely cause of that error on that line is that nothing has been assigned to
the objWord variable.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
B

Brendan Reynolds

Well, I copied and pasted the code from the KB article. I had to comment out
the lines that attempt to copy and paste the photo, because I'm using Access
2003, and the Access 2003 version of Northwind no longer stores the
employee's photos within the database. Once I'd done that, though, the code
ran without any error. So the problem doesn't appear to be a bug in the
original code. It seems it must be in your modifications to the code, and
we'll likely need to see the whole procedure in order to track it down.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Joe Cilinceon

Thanks Brendan The complete sub is below. I'm using version Office XP Pro
(2002) if that makes a difference. I also am not using the photo.

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application

'SetUp Name Field variables
Dim AccountName As String
Dim Address2 As String
Dim Address3 As String
Dim Contact As String

'Start Microsoft Word 97.
Set objWord = CreateObject("Word.Application")

If Me!BusIsTenant = True Then
AccountName = Trim(Me!AccountName)
Else
AccountName = Trim(Me!FirstName) & " " & Trim(Me!MiddleName) & " " &
Trim(Me!LastName)
End If

If IsNull(Me!MailAddress2) Or (Trim(Me!MailAddress2) = "") Then
Address2 = Trim(Me!MailCity) & ", " & Trim(Me!MailState) & " " &
Trim(Me!MailZip)
Address3 = Null
Else
Address2 = Trim(Me!MailAddress2)
Address3 = Trim(Me!MailCity) & ", " & Trim(Me!MailState) & " " &
Trim(Me!MailZip)
End If
If IsNull(Me!AltFName) Then
Contact = ""
Else
Contact = Trim(Me!AltFName) & " " & Trim(Me!AltLName)
End If


With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("C:\Handi\Lease\Lease.doc")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("Ax1").Select
.Selection.Text = CStr(AccountName)
.ActiveDocument.Bookmarks("Ax2").Select
.Selection.Text = CStr(Trim(Me!MailAddress1))
.ActiveDocument.Bookmarks("Ax3 ").Select
.Selection.Text = CStr(Address2)
.ActiveDocument.Bookmarks("Ax4").Select
.Selection.Text = CStr(Address3)
.ActiveDocument.Bookmarks("ax5").Select
.Selection.Text = (CStr(Me!HomePhone))
.ActiveDocument.Bookmarks("ax6").Select
.Selection.Text = (CStr(Me!BusPhone))
.ActiveDocument.Bookmarks("Ax7").Select
.Selection.Text = (CStr(Me!Lic))
.ActiveDocument.Bookmarks("Ax8").Select
.Selection.Text = CStr(Contact)
.ActiveDocument.Bookmarks("Ax9").Select
.Selection.Text = (CStr(Me!SocSec))
.ActiveDocument.Bookmarks("ax10").Select
.Selection.Text = (CStr(Me!GateCode))
.ActiveDocument.Bookmarks("ax11").Select
.Selection.Text = (CStr([fsubTenantLedger].Form![Payment Date]))
.ActiveDocument.Bookmarks("ax12").Select
.Selection.Text = (CStr([fsubTenantLedger].Form![Unit]))
.ActiveDocument.Bookmarks("ax13").Select
.Selection.Text = (CStr(Me.txtSize))
.ActiveDocument.Bookmarks("ax14").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![RentRate],
"Currency")))
.ActiveDocument.Bookmarks("ax15").Select
.Selection.Text = (CStr("$18.00"))
.ActiveDocument.Bookmarks("ax16").Select
.Selection.Text = (CStr("$25.00"))
.ActiveDocument.Bookmarks("Ax17").Select
.Selection.Text = (CStr(Format(Nz([fsubTenantLedger].Form![Rent],
0), "Currency")))
.ActiveDocument.Bookmarks("Ax18").Select
.Selection.Text =
(CStr(Format(Nz([fsubTenantLedger].Form![AdmistrationFee], 0), "Currency")))
.ActiveDocument.Bookmarks("Ax19").Select
.Selection.Text = (CStr(Format(Nz([fsubTenantLedger].Form![Lock],
0), "Currency")))
.ActiveDocument.Bookmarks("Ax20").Select
.Selection.Text = (CStr("$0.00"))
.ActiveDocument.Bookmarks("Ax21").Select
.Selection.Text = (CStr(Format(Nz([fsubTenantLedger].Form![MiscChg],
0), "Currency")))
.ActiveDocument.Bookmarks("Ax22").Select
.Selection.Text =
(CStr(Format(Nz([fsubTenantLedger].Form![CreditApplied], 0), "Currency")))
.ActiveDocument.Bookmarks("Ax23").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![Payment
Amount], "Currency")))
.ActiveDocument.Bookmarks("ax24").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![PaidThru],
"mm/dd/yyyy")))
.ActiveDocument.Bookmarks("ax25").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![RentRate],
"Currency")))
.ActiveDocument.Bookmarks("ax26").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![Payment
Amount], "Currency")))
.ActiveDocument.Bookmarks("ax27").Select
.Selection.Text = (CStr(Format([fsubTenantLedger].Form![PaidThru],
"mm/dd/yyyy")))


End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
Else
MsgBox Err.Number & vbCr & Err.Description
End If

Exit Sub
End Sub
[/QUOTE]
 
B

Brendan Reynolds

Dim Address3 As String
Click to expand...
Address3 = Null
Click to expand...

This may be your problem. You can't assign the Null value to a String, a
Variant is the only type of variable that can accept Null. And this is
happening *before* you assign anything to the objWord variable, so if the
error occurs at this point ...

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Click to expand...
<snip>

.... your error handler attempts to use the objWord variable, to which
nothing has yet been assigned.

You need to either change Address3 to a Variant, or assign an empty String
to it instead of a Null. I'd also add the following check to your error
handler ...

If Not objWord Is Nothing Then
objWord.Selection.Text = ""

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Joe Cilinceon

Thanks Brendan and I did that and it worked. I did find another problem also
that was confusing me in that it wasn't removing the bookmark name
completely. It was in full paragraphs but the top of the form is in a table.
I dumped the table and went with tab stops and now it is perfect. Thanks
again Brenden.
 
Top