Results 1 to 7 of 7
  1. #1
    AmanKaur123 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    47

    Runtime error 462, The remote server machine doesn't exist or in unavailable

    Hi All

    when I run the code first time then it works fine but next time there is always a problem and I get runtime error 462. SO when I close the form and start back again then it works fine for the first time again. There is a problem in the Red line in the below code:


    Code:
    Private Sub RowFormat(r As Word.Range, IsHeader As Boolean)
        Dim I As Integer
        If IsHeader Then
          '  r.Shading.BackgroundPatternColor = -738132071
          r.Shading.BackgroundPatternColor = RGB(127, 196, 220)
        Else
            Dim c As Word.Cell
            For Each c In r.Cells
                'AddBorders c.Range
                
        For I = -4 To -1
            c.Range.Borders(I).LineStyle = Options.DefaultBorderLineStyle
        Next
            Next
        End If
    End Sub
    ANy help will be much appreciated.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Where is this code running? Access?, Word?

    if Access, I don't see where the Word app is defined.
    Word.cell must have Word app defined and created.

  3. #3
    AmanKaur123 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    47
    This is the complete code:
    Code:
    Private Sub cmdsummit_Click()
         
        Dim CurSec() As String, PrevSec As String
        Dim strSavePath As String
        Dim strSaveName As String
        Dim db As DAO.Database
        Dim rsMailmerge As Recordset
        strSavePath = DLookup("Variable", "tblVariable", "VariableID=16")
        strSaveName = "QA Form " & Format(Now(), "yyyymmmdd_hhmmss") & " " & Me.cboSupervisor.Column(1) & ".doc"
        
        With CurrentDb.OpenRecordset("select refid,standarddoc,score from qryQAMatrix where QAID=" & txtQAID & "")
         
           ' If .BOF And .EOF Then Exit Sub
            Dim wa As New Word.Application
            Dim wd As Word.Document
            Dim bk As Word.Bookmark
            
            With wa
                .Visible = True
                .Activate
                .ScreenUpdating = False
            End With
            
          
            Set wd = wa.Documents.Open("L:\Templates\Health Check.dot")
            
            strTextFile = cboForms.Column(1)
            
            createKFIMailMergefile strPath, strTextFile & ".txt"
            
            If Me.cboForms <> 7 Then
            Set bk = wd.Bookmarks("InsertTable")
            
            Dim wt As Word.Table, wr As Word.Row
            'Set wt = wd.Tables.Add(wd.Parent.Selection.Range, 1, 3)
            Set wt = wd.Tables.Add(bk.Range, 1, 3)
            
            
            wt.Columns(1).Width = 60
            wt.Columns(2).Width = 400
            wt.Columns(3).Width = 70
            
            RowFormat wt.Range, False
            
            Do Until .EOF
                CurSec = Split(.Fields(0), ".")
                Set wr = wt.Rows.Add
              '  wr.Cells(1).Range.Text = CurSec(1) & "." & CurSec(2)
                wr.Cells(1).Range.Text = .Fields(0)
                wr.Cells(2).Range.Text = .Fields(1)
                If .Fields(2).Value = 1 Then
                wr.Cells(3).Range.Text = "Yes"
                ElseIf .Fields(2).Value = 2 Then
                wr.Cells(3).Range.Text = "No"
                Else
                wr.Cells(3).Range.Text = "N/A"
                End If
                
                If Not CurSec(0) = PrevSec Then
                    'new section, create a header
                    
                    PrevSec = CurSec(0)
                  
                    Set wr = wt.Rows.Add(wr)
                    If cboForms.Value = 1 Then
                    wr.Cells(1).Range.Text = SecName(Me.cboArea.Value)
                    Else
                    wr.Cells(1).Range.Text = SecName(CurSec(0))
                    End If
                    'wr.Cells(1).Range.Text = CurSec
                    wr.Cells(3).Range.Text = "Response"
                    wd.Range(wr.Cells(1).Range.Start, wr.Cells(2).Range.End).Cells.Merge
                    RowFormat wr.Range, True
                End If
                
                .MoveNext
            Loop
            wt.Rows(1).Delete
            End If
        End With
    wd.MailMerge.MainDocumentType = 0
    wd.MailMerge.Destination = wdSendToNewDocument
    wd.MailMerge.OpenDataSource (strPath & strTextFile & ".txt")
    wd.MailMerge.Execute
    'Go through all created doc and remove all mail merge errors
    For I = 1 To wd.Application.Documents.Count
        If InStr(1, wa.Application.Documents(I).Name, "Error") <> 0 Then
            wa.Application.Documents.Item(I).Close False
            I = I - 1
        End If
        If I = wa.Application.Documents.Count Then Exit For
    Next I
    'Save merged document as new file
    wa.ActiveDocument.AttachedTemplate.Saved = True
    wd.Application.Documents.Item(1).SaveAs strSavePath & strSaveName, , , , False, , True
    'Go through all created doc and close them
        For I = 1 To wa.Application.Documents.Count
            wa.Application.Documents.Item(wa.Application.Documents.Count).Close False
        Next I
    'delete the text file
    Kill strPath & strTextFile & ".txt"
    'delete the subs
    exithere:
    wa.Quit
    Set wd = Nothing
    Set wa = Nothing
    Set qry = Nothing
    Set db = Nothing
    MsgBox ("Export Completed")
    Application.FollowHyperlink strSavePath & strSaveName
    Exit Sub
        
        With wd.Parent
            .ScreenUpdating = True
        End With
        
    End Sub

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't think you can have a negative border.
    For I = -4 to -1
    Borders(I)

  5. #5
    AmanKaur123 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    47
    Ranman, The code works fine in the first attempt. The error comes when I run the second time.

    Can you please amend the code to make it work?

    Thanks

  6. #6
    AmanKaur123 is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    47
    Any help on this anyone?

    Thanks

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I suspect it is because you are using

    With CurrentDb.OpenRecordset("select refid,standarddoc,score from qryQAMatrix where QAID=" & txtQAID & "")


    try

    Code:
    dim rst as dao.recordset
    set rst=currentdb.openrecordset("select refid,standarddoc,score from qryQAMatrix where QAID=" & txtQAID & "")
    
    with rst.....
    ...
    ...
    set rst=nothing
    the reason I think this is because every time you use currentdb it creates a new object variable (which is why things like .recordsaffected will always return 0 after a .execute)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import spec doesn't exist error
    By gg80 in forum Import/Export Data
    Replies: 1
    Last Post: 04-03-2016, 10:11 PM
  2. Replies: 2
    Last Post: 03-13-2015, 09:11 AM
  3. Replies: 2
    Last Post: 10-15-2014, 04:23 AM
  4. Replies: 2
    Last Post: 03-30-2012, 02:37 AM
  5. Replies: 3
    Last Post: 02-23-2012, 07:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums