Help debugging some code 1

[upplepop]

I have this code on a command button that checks for a Word document on a networked computer that serves the Database. If the document is found, it brings it up. If it's not found, it opens a template, fills in the bookmarks with datbase info, and saves it on the database server.

The problem I am having is I occasionally get an error stating: "The remote server machine does not exist or is unavailable" after it creates a new document and tries to save it. This does not happen right after they open the database and press the button, but if they press it again in the same session, they get the error. Can you guys figure out what's wrong?



Private Sub cmdReport_Click()
'Checks in recommendations directory if a recommendation already
'exists. If not, it creates a new document based on the recommendation
'template. It saves/opens based on the case number (i.e. 2347.doc)

On Error GoTo Err_cmdReport_Click

Dim appWord As New Word.Application
Dim CaseNumber
Dim strCourtDate As String
Dim strDept As String
Dim strFirst As String
Dim strMiddle As String
Dim strLast As String
Dim strDOB As String
Dim strCaseNum1 As String
Dim strCaseNum2 As String
Dim strCaseNum3 As String
Dim strAttorney As String
Dim strASW As String

txtCase.SetFocus
CaseNumber = txtCase.Text

'If document doesn't exist, generate new report.
If Dir("//ALTSP1/Database/Recommendations/" & CaseNumber & ".doc&quot = vbNullString Then
MsgBox "Report doesn't exist, click OK to generate new report"
appWord.Visible = True
appWord.Documents.Add "//ALTSP1/Database/Recommendation.dot"

'Put the Database information into variables
Me.txtCourt.SetFocus
strCourtDate = txtCourt.Text
Me.txtDept.SetFocus
strDept = txtDept.Text
Me.txtFirst.SetFocus
strFirst = txtFirst.Text
Me.txtMiddle.SetFocus
strMiddle = txtMiddle.Text
Me.txtLast.SetFocus
strLast = txtLast.Text
Me.txtDOB.SetFocus
strDOB = txtDOB.Text
Me.txtCourtCase1.SetFocus
strCaseNum1 = txtCourtCase1.Text
Me.txtCourtCase2.SetFocus
strCaseNum2 = txtCourtCase2.Text
Me.txtCourtCase3.SetFocus
strCaseNum3 = txtCourtCase3.Text
Me.cmbAttorney.SetFocus
strAttorney = cmbAttorney.Text
Me.cmbCaseworker.SetFocus
strASW = cmbCaseworker.Text

'Paste into Template's bookmarks
appWord.Application.Visible = True
ActiveDocument.Bookmarks("CourtDate&quot.Select
appWord.Selection.Text = strCourtDate
ActiveDocument.Bookmarks("Dept&quot.Select
appWord.Selection.Text = strDept
ActiveDocument.Bookmarks("First&quot.Select
appWord.Selection.Text = strFirst
ActiveDocument.Bookmarks("Middle&quot.Select
appWord.Selection.Text = strMiddle
ActiveDocument.Bookmarks("Last&quot.Select
appWord.Selection.Text = strLast
ActiveDocument.Bookmarks("DOB&quot.Select
appWord.Selection.Text = strDOB
ActiveDocument.Bookmarks("Case1&quot.Select
appWord.Selection.Text = strCaseNum1
ActiveDocument.Bookmarks("Case2&quot.Select
appWord.Selection.Text = strCaseNum2
ActiveDocument.Bookmarks("Case3&quot.Select
appWord.Selection.Text = strCaseNum3
ActiveDocument.Bookmarks("Attorney&quot.Select
appWord.Selection.Text = strAttorney
ActiveDocument.Bookmarks("ASW&quot.Select
appWord.Selection.Text = strASW


appWord.Application.Visible = True
appWord.ActiveDocument.SaveAs "//ALTSP1/Database/Recommendations/" & CaseNumber + ".doc"
Me.txtReport.SetFocus
txtReport.Text = Date
Else
MsgBox "Report exists, click OK to open file"
appWord.Visible = True
appWord.Documents.Open "//ALTSP1/Database/Recommendations/" & CaseNumber & ".doc"
End If

On Error Resume Next
docObject.UserControl = True

Exit_cmdReport_Click:
Exit Sub

Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click

End Sub

 

[VBslammer]

This problem occurs (Error 462) because you have used implicit references to your automation object, which causes them to remain in memory until you restart your program. To eliminate the problem, use explicit references to all of the automation objects.

For example, you reference the application properly using:

appWord.Documents.Add "//etc..."

But then you reference the 'ActiveDocument' without using:

appWord.ActiveDocument.BookMark("Case1").Select

My suggestion is that you use a With-End With block to simplify your statments:

With appWord
  .ActiveDocument.Bookmarks("CourtDate").Select
  .Selection.Text = strCourtDate
  .ActiveDocument.Bookmarks("Dept").Select
  .Selection.Text = strDept
  .ActiveDocument.Bookmarks("First").Select
  .Selection.Text = strFirst
  .ActiveDocument.Bookmarks("Middle").Select
  .Selection.Text = strMiddle
  .ActiveDocument.Bookmarks("Last").Select
  .Selection.Text = strLast
  .ActiveDocument.Bookmarks("DOB").Select
  .Selection.Text = strDOB
  .ActiveDocument.Bookmarks("Case1").Select
  .Selection.Text = strCaseNum1
  .ActiveDocument.Bookmarks("Case2").Select
  .Selection.Text = strCaseNum2
  .ActiveDocument.Bookmarks("Case3").Select
  .Selection.Text = strCaseNum3
  .ActiveDocument.Bookmarks("Attorney").Select
  .Selection.Text = strAttorney
  .ActiveDocument.Bookmarks("ASW").Select
  .Selection.Text = strASW
End With

Once you do that, your users can re-click the report button all they want.


VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

By the way, here's the Knowledge Base article that discusses this issue:

Microsoft Knowledge Base Article - 189618 VBSlammer

Unemployed in Houston, Texas

 

[upplepop]

thanks for your help VBSlammer! It worked great.