Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-26-2012, 10:13 AM   #1
arkainus
Newly Registered User
 
Join Date: Apr 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
arkainus is on a distinguished road
Thumbs down Calling Access Report from Word - Run-time Error '462'

Hi everyone,

I'm new to these forums and to vba in general. I have run into an issue that I just can't seem to figure out. The first time I run this macro, it runs fine without issue. The next time I run it, I get an error of:

Run-Time error '462':

The Remote server machine does not exist or is unavailable

If I close word and re-open it, I can get the macro to kick in, but again only the first time I attempt to run the macro does it successfully work 100%. The rest of time it will intermittently spit that same error out to me.

Code:
 
Sub GenMCL()
'
' GenMCL Macro
' Generates Master Client List Report and Exports that to a PDF to a location of the users choice
'
    'On Error GoTo ProcError
    Dim objAccess As Object
    'Dim objDB As Object
    Dim sPathUser As String
    Dim currentDate As String
    Dim YesOrNoAnswerToMessageBox As String
    Dim QuestionToMessageBox As String
GetFilePath:
    currentDate = Format(Date, "mmddyyyy")
    sPathUser = BrowseFolder(Caption:="Select A Folder To Output The Report To")
 
    If Len(sPathUser) = 0 Then
    QuestionToMessageBox = "Cancel button detected. Did you mean to quit this program?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Quit Program")
        If YesOrNoAnswerToMessageBox = vbNo Then
            GoTo GetFilePath
        Else
            MsgBox "Ok, Goodbye."
            Exit Sub
        End If
    End If
 
    sPathUser = sPathUser & "\[" & currentDate & "] - Master Client List" & ".pdf"
 
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "G:\\Public\Access Data\MyDB.accdb"
 
   DoCmd.OutputTo acOutputReport, "Master Client List", acFormatPDF, sPathUser, True
    objAccess.Quit
    Set objAccess = Nothing
    'MsgBox "Report Generated and Saved.", vbInformation
    Exit Sub
 
ProcError:
    MsgBox Err.Description, vbExclamation, "Generate Master Client List Report"
    MsgBox Err.Number
    Resume ProcExit
ProcExit:
    Exit Sub
 
End Sub
If I hit debug on the code, the line that is red above is the line that is highlighted as the offending code by the debugger. There is a report called exactly "Master Client List" in MyDB.accdb, if I take it through the debugger I see sPathUser definitely has a valid value. The reason for the line
Code:
objAccess.OpenCurrentDatabase "G:\\Public\Access Data\MyDB.accdb"
is because I saw a posting where another person suggested that the issue may be because G is a mapped drive and putting the extra slash in there would help. Unfortunately it made no appreciable difference in my case.

I am really stuck here. My assumption is that since it works the first time, something isn't getting closed properly, but that is simply a guess. I'm throwing this out here in the hopes that someone much smarter than me can help figure this out. Any help here would be greatly appreciated.

Thank you,

Chris

arkainus is offline   Reply With Quote
Old 04-26-2012, 10:17 AM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Calling Access Report from Word - Run-time Error '462'

http://blogs.technet.com/b/heyscript...ss-report.aspx
spikepl is offline   Reply With Quote
The Following User Says Thank You to spikepl For This Useful Post:
arkainus (04-26-2012)
Old 04-26-2012, 10:50 AM   #3
arkainus
Newly Registered User
 
Join Date: Apr 2012
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
arkainus is on a distinguished road
Re: Calling Access Report from Word - Run-time Error '462'

[QUOTE=spikepl;1149459]<link removed because my post count doesn't allow it to be included>QUOTE]

Thank you for your response. I went ahead and read the link. That doesn't seem to be exactly what I'm trying to accomplish, but I do notice that in his script he he has set objCommand = objAccess.DoCmd, something I don't do in my code. I jump straight into docmd.output right after assigning the proper database to be the current database. Is it better if I explicitly go that route? That one also has a line of: objAccess.CloseCurrentDatabase, where as I use objAccess.quit and then set objAccess to Nothing. I am going to lunch right now so I haven't modified my code yet, but is there a fundamental difference to objAccess.quit vs. objAccess.CloseCurrentDatabase? If so, should I just do objAccess.CloseCurrentDatabase rather than objAccess.Quit or should I do both, calling closecurrentdatabase first, then objAccess.Quit? Being new to VBA I am just hopefully looking for some clarification. Thank you again for your response, and for anyone else who may be able to help.

arkainus is offline   Reply With Quote
Reply

Tags
access , docmd , word

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question Word Automation - Run Time Error '13' Type Mismatch jrdewilde General 3 06-18-2010 04:16 AM
Run-Time error '3075' Access Report WhatNThe General 7 02-01-2010 09:08 AM
Error when calling a stored procedure from the MS Access code accesser2003 General 3 07-16-2008 08:27 AM
Calling an Access function from inside of WORD monad General 1 10-05-2005 08:24 PM
ACCESS 2000: Error Calling Sub using eval spartac Modules & VBA 1 05-23-2004 10:35 PM




All times are GMT -8. The time now is 02:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World