Using Access XP -> Word XP
I'm exporting data from Access to three Word templates using automation. The code I've developed consists of three nested loops to extract the data and import it into bookmarks in each of the word templates, and then combine the separate documents into one document. The code works exactly as required for the first iteration of the outer loop. On the second iteration, I get the error message
Runtime error 462: The remote server machine does not exist or is unavailable
If I select one electorate (the outer loop), everything runs perfectly and I get the result I expected. If I select more than one ( and it could be any number up to 150), the first electorate reports correctly, and the second one (any second one) produces the error.
Can anyone explain why this error is occurring, and what it actually means? Any assistance will be greatfully received.
Sorry about the length, but since I don't know why the error is occurring, I posted the whole routine.
Lightning
I'm exporting data from Access to three Word templates using automation. The code I've developed consists of three nested loops to extract the data and import it into bookmarks in each of the word templates, and then combine the separate documents into one document. The code works exactly as required for the first iteration of the outer loop. On the second iteration, I get the error message
Runtime error 462: The remote server machine does not exist or is unavailable
If I select one electorate (the outer loop), everything runs perfectly and I get the result I expected. If I select more than one ( and it could be any number up to 150), the first electorate reports correctly, and the second one (any second one) produces the error.
Can anyone explain why this error is occurring, and what it actually means? Any assistance will be greatfully received.
Code:
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("hidReportElectorates", dbOpenDynaset)
Rs.MoveFirst
With Rs
Do Until Rs.EOF
strElectorate = !Electorate
'Select the Start and End years for this Electorate's funding,
' and the TOTAL funding provided for all programs
strSelect = "SELECT Project_Electorate.Electorate, " _
& "Min(Payment.Financial_Year) AS StartYear, " _
& "Max(Payment.Financial_Year) AS EndYear, " _
& "Sum(Payment.Payment_Amount) AS TotalFunding"
strFrom = " FROM ((Programme INNER JOIN Project ON " _
& "Programme.Programme_ID = Project.Programme_ID) " _
& "INNER JOIN Payment ON Project.Project_ID = Payment.Project_ID) " _
& "INNER JOIN Project_Electorate ON Project.Project_ID = Project_Electorate.Project_ID"
strGroup = " GROUP BY Project_Electorate.Electorate"
strHave = " HAVING (((Project_Electorate.Electorate) = " & MyQuotes(strElectorate) & "))"
strOrder = " ORDER BY Project_Electorate.Electorate"
strSQL = strSelect & strFrom & strGroup & strHave & strOrder
Set Rs1 = Db.OpenRecordset(strSQL, dbOpenDynaset)
With Rs1
strStartYear = .Fields("StartYear")
strEndYear = .Fields("EndYear")
dblFunding = .Fields("TotalFunding")
End With
Rs1.Close
Set Rs1 = Nothing
strSQL = ""
strSelect = ""
strFrom = ""
strGroup = ""
strHave = ""
strOrder = ""
'Open Word, set data to Bookmarks, and then display the document
Set objWord = New Word.Application
objWord.Visible = True
Set objElectorate = objWord.Documents.Add(Template:="Electorate", NewTemplate:=False)
With objElectorate.Bookmarks
.Item("Electorate").Range.Text = strElectorate
.Item("ReportDate").Range.Text = Format(Date, "mmmm yyyy")
.Item("TotalFunding").Range.Text = Format(dblFunding, "##,##0")
End With
dblFunding = 0
objWord.ActiveDocument.SaveAs FileName:="C:\Bryan\Database\PROFET Minister's Report\Reports\" & strElectorate & " Programmes Report at " & Day(Date) & "-" & Month(Date) & "-" & Year(Date) & ".doc"
strSelect = "SELECT Project_Electorate.Electorate, " _
& "Programme.Programme_Name, Sum(Payment.Payment_Amount) " _
& "AS ProgramFunds"
strFrom = " FROM ((Programme INNER JOIN Project ON " _
& "Programme.Programme_ID = Project.Programme_ID) " _
& "INNER JOIN Payment ON Project.Project_ID = " _
& "Payment.Project_ID) INNER JOIN Project_Electorate ON " _
& "Project.Project_ID = Project_Electorate.Project_ID"
strGroup = " GROUP BY Project_Electorate.Electorate, " _
& "Programme.Programme_Name, " _
& "Programme.Programme_Description"
strHave = " HAVING (((Project_Electorate.Electorate) = " & MyQuotes(strElectorate) & "))"
strOrder = " ORDER BY Project_Electorate.Electorate"
strSQL = strSelect & strFrom & strGroup & strHave & strOrder
Set Rs1 = Db.OpenRecordset(strSQL, dbOpenDynaset)
intCount = 0
With Rs1
Do Until .EOF
strProgram = .Fields("Programme_Name")
dblFunding = .Fields("ProgramFunds")
' strDescription = .Fields("Programme_Description")
Set objPrograms = objWord.Documents.Add(Template:="Programs", NewTemplate:=False)
With objPrograms.Bookmarks
.Item("ProgramName").Range.Text = strProgram
.Item("TotalFunds").Range.Text = dblFunding
' .Item("ProgramDetails").Range.Text = strDescription
End With
strSelect2 = "SELECT Project_Electorate.Electorate, " _
& "Programme.Programme_Name, Project.Project_Description, " _
& "Payment.Financial_Year, Sum(Payment.Payment_Amount) " _
& "AS SumOfPayment_Amount"
strFrom2 = " FROM ((Programme INNER JOIN Project ON " _
& "Programme.Programme_ID = Project.Programme_ID) " _
& "INNER JOIN Payment ON Project.Project_ID = " _
& "Payment.Project_ID) INNER JOIN Project_Electorate ON " _
& "Project.Project_ID = Project_Electorate.Project_ID"
strGroup2 = " GROUP BY Project_Electorate.Electorate, " _
& "Programme.Programme_Name, Project.Project_Description, " _
& "Payment.Financial_Year, Programme.Programme_Description"
strHave2 = " HAVING (((Project_Electorate.Electorate)=" & MyQuotes(strElectorate) & ") AND ((Programme.Programme_Name)=" & MyQuotes(strProgram) & "))"
strOrder2 = " ORDER BY Project_Electorate.Electorate, Payment.Financial_Year"
strSQL2 = strSelect2 & strFrom2 & strGroup2 & strHave2 & strOrder2
Set Rs2 = Db.OpenRecordset(strSQL2, dbOpenDynaset)
With Rs2
intX = 0
Do Until .EOF
strProject = .Fields("Project_Description")
dblProjectFunds = .Fields("SumOfPayment_Amount")
Set objProjects = objWord.Documents.Add(Template:="Projects", NewTemplate:=False)
With objProjects.Bookmarks
.Item("ProjectName").Range.Text = strProject
' .Item("TotalFunds").Range.Text = dblProjectFunds
' .Item("ProjectDetails").Range.Text = strProjDescr
End With
'Save the completed Projects file into the Programs file
intX = intX + 1
objWord.ActiveDocument.SaveAs FileName:="C:\Bryan\Database\PROFET Minister's Report\Reports\temp\" & strElectorate & " Projects" & intX & ".doc"
strProjFileName = "C:\Bryan\Database\PROFET Minister's Report\Reports\temp\" & strElectorate & " Projects" & intX & ".doc"
objProjects.Close SaveChanges:=wdSaveChanges
objPrograms.Activate
objPrograms.Select
[COLOR=red yellow]Error occurs on this line-->[/color] Selection.EndKey Unit:=wdStory
Selection.InsertFile strProjFileName
.MoveNext
Loop
End With
Rs2.Close
Set Rs2 = Nothing
'Save the completed Programs file into the Electorate file
intCount = intCount + 1
objPrograms.Activate
objWord.ActiveDocument.SaveAs FileName:="C:\Bryan\Database\PROFET Minister's Report\Reports\temp\" & strElectorate & " Programmes" & intCount & ".doc"
strProgFileName = "C:\Bryan\Database\PROFET Minister's Report\Reports\temp\" & strElectorate & " Programmes" & intCount & ".doc"
objElectorate.Activate
objElectorate.Select
Selection.EndKey Unit:=wdStory
Selection.InsertFile strProgFileName
objPrograms.Close SaveChanges:=wdSaveChanges
.MoveNext
Loop
End With
Rs1.Close
Set Rs1 = Nothing
objElectorate.Close SaveChanges:=wdSaveChanges
objWord.Quit
strKillFiles = "C:\Bryan\Database\PROFET Minister's Report\Reports\temp\*.doc"
Kill strKillFiles
.MoveNext
Loop
End With
Call EmptyTable
MsgBox "All requested Electorate reports have been generated.", vbOKOnly + vbInformation, _
"Reports Completed!"Sorry about the length, but since I don't know why the error is occurring, I posted the whole routine.
Lightning
