Results 1 to 7 of 7
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Automate Access from Word (Access 97)

    I have a situation where automation is used to create Word documents from within an Access application.

    I now want the user to be able to decide, once in Word, that they want to add some extra info to the document, which must come from the open Access database.

    This needs a form to be opened, some data passed to the form , a combo box requeried , then control left with the Access form. If some info is actually selected it will be added to the end of the document.

    I can actually get all this to work, but I don't like the way I have done it, and am looking for something better. At the moment I used AppActivate("my application") followed by a series of SendKeys statements. The first SendKeys activates an autokeys item in Access that opens the form.

    I expect it is possible to do this with code that starts off something like this

    Dim objAccess As Access.Application
    Set objAccess = GetObject(, "Access.Application")
    Dim strfilepath As String
    strfilepath = "e:johnaccessjusttesting.mdb"
    objAccess.OpenCurrentDatabase filepath:=strfilepath
    objAccess.Visible = True

    Now I don't need to open a database, as the one I want to use is already open. If I try to reopen it using this code, it produces an error.

    (Even if the db is not open, the objAccess.visible line throws an error: "You entered an expression that has an invalid reference to the Property visible.)

    Is it possible to point an Access.Application variable to a currently open db.
    Regards
    John



  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automate Access from Word (Access 97)

    If using GetObject to do this, you can try something like this from Word:

    Public Sub TestOpenAccessDB()
    On Error GoTo Err_Handler

    Dim strMsg As String
    Dim strPath As String
    Dim strFile As String
    Dim app As Access.Application

    strPath = "C:Program FilesMicrosoft OfficeOffice"
    strFile = "Northwind.mdb"
    Set app = GetObject(, "Access.Application")

    If app.CurrentProject.Path = strPath & "Samples" And _
    app.CurrentProject.Name = strFile Then
    app.Run "Northwind.OpenFormFromWord", "Customers"
    Else
    MsgBox "Wrong database open, try later.", vbExclamation, "ERROR"
    End If

    Exit_Sub:
    Set app = Nothing
    Exit Sub

    Err_Handler:
    Select Case Err.Number
    Case 429 ' Can't create object (db not open):
    Shell Chr$(34) & strPath & "MsAccess.exe" & Chr$(34) & Chr$(32) & _
    Chr$(34) & strPath & "Samples" & strFile & Chr$(34), vbNormalFocus
    Resume
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Sub

    Once database is open (error handler will open if not open already) the Word sub runs this sub in the Access file, using Application Run method, which accepts up to 30 optional arguments for the procedure being run (see VBA Help for details):

    Public Sub OpenFormFromWord(ByRef strForm As String)
    DoCmd.OpenForm strForm, acNormal, , , , , "Word"
    DoCmd.SelectObject acForm, strForm
    End Sub

    This worked OK in tests with Northwind.mdb already open, or no database open. Of course if some other Access db is open, GetObject will get the "wrong" db, resulting in error msg. You may want to add additional code to ensure the open db & form gets the focus from Word - in my tests, the db got focus if not already open, but if already open, the form opened but focus did not move to db & form, focus stayed on Word doc.

    HTH

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Automate Access from Word (Access 97)

    As an alternative, have you considered leaving the document open and visible in Word when you create it in the Access application and popping the form up at the end of the document creation process so that all the user has to do is switch from one application to the other? It seems like that might be a less complicated approach, though what Mark has suggested should also work.
    Wendell

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automate Access from Word (Access 97)

    PS: Modified sample code to ensure focus will move to open Access form while in Word. Add following API declarations to Word code module:

    Option Explicit

    Declare Function SetFocusAPI Lib "user32" Alias "SetFocus" (ByVal hwnd As Long) As Long
    Declare Function SetForegroundWindow Lib "user32" ByVal hwnd As Long) As Long
    Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

    ' Show Window (SW) Constants:
    Public Const SW_HIDE = 0
    Public Const SW_SHOWNORMAL = 1
    Public Const SW_SHOWMINIMIZED = 2
    Public Const SW_SHOWMAXIMIZED = 3
    Public Const SW_SHOWNOACTIVATE = 4
    Public Const SW_SHOW = 5
    Public Const SW_MINIMIZE = 6
    Public Const SW_SHOWMINNOACTIVE = 7
    Public Const SW_SHOWNA = 8
    Public Const SW_RESTORE = 9

    Add these variables to TestOpenAccessDB sub:

    Dim strForm As String
    Dim hwnd As Long

    Then after opening form using Run method, add these instructions:

    ShowWindow app.hWndAccessApp, SW_SHOWNORMAL
    hwnd = app.Forms(strForm).hwnd
    SetForegroundWindow hwnd
    ShowWindow hwnd, SW_SHOWNORMAL

    This will display Access in restored ("Normal") window, with specified form (strForm) in foreground (also restored). If you run this with Word open in maximized window, the Word doc will remain visible when Access form is displayed. This worked OK whether or not db was already open. There are various other Windows API functions you can use if you really want to micromanage the Word & Access window arrangement when running this.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Automate Access from Word (Access 97)

    When using Automation with Access & Word, I almost always open the Word doc from Access (typically after exporting data from Access to Word), rather than the other way around. It's a lot simpler, and probably a good idea if practical in this case.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automate Access from Word (Access 97)

    Thanks to Mark and Wendell

    I will spend some time today trying this stuff out .

    Just a clarification. From Access, I create the document, which is then automatically opened to complete the task of writing . The documents are letters so the automation just puts in some info, most has to be written individually. The typist will be transcribing from dictation. When she gets to the end, she may then find an instruction (on the tape) to include something extra in the letter. I could suggest that they manually go back to Access, and initiate this from there, but they want to initiate it from the letter.

    These letters are only ever opened from within the database, so whenever a letter is open, the database is going to be open. But there is no guarantee just what form will be open in the database, as there are a number of places where you can open previously written letters. The decision to include the extra info in the letter may not be made when it is written, but could be made at any time up the point it is sent off.
    Regards
    John



  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Automate Access from Word (Access 97)

    My original question was :
    Is it possible to point an Access.Application variable to a currently open db.

    It seems to me that the reason I couldn't find out how to do this is that it happens automatically.

    This is now working just how I want it, thanks

    I was able to Activate the form opended in Access just by putting in an AppActivate line in the called procedure.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •