I have a form (Section_Editor) that includes an OLE control (Editor_Window). The click event on the OLE control opens an existing or new Word document for editing. After editing, a ribbon control click retuns the edited document to the OLE control, returns some other values to other form controls and then quits Word.
It works, but when I then click on any other form control or navigate to another record, the Word document again pops up in Word. I can then return again to Access, with Word quitting again. At that point, I can click on other form controls or navigate to another record without the document launching again in Word (i.e., it only happens once).
I am a novice with VBA, so the problem may be obivious--but unfortunately not to me. Here is the code I am using:
ACCESS OLE CONTOL CLICK EVENT
Private Sub Editor_Window_Click()
On Error GoTo NoOLEObject
Me![Editor_Window].Verb = VERB_OPEN
Me![Editor_Window].Action = OLE_ACTIVATE
Exit Sub
NoOLEObject:
If Err = 2684 Then
Me![Editor_Window].OLETypeAllowed = OLE_EMBEDDED
Me![Editor_Window].SourceDoc = "C:\Documents and Settings\ My Documents\Section_Template.docm"
Me![Editor_Window].Action = OLE_CREATE_EMBED
Resume
Else
MsgBox "Error - " & Str(Err) & " : " & Error
End If
Exit Sub
End Sub
WORD RIBBON BUTTON CODE TO RETURN TO ACCESS
'Callback for ButtonA1 onAction
Sub ButtonA1Macro(control As IRibbonControl)
On Error GoTo Err_ButtonA1Macro
Dim acApp As Access.Application
'***Reference running instance of Access***
Set acApp = GetObject(, "Access.Application")
**********some omitted code that defines and sets SecVariableString and SignatureString**********
'***Transfer fields to Access form***
With acApp
[Forms]![Section_Editor]![Variables].Value = SecVariableString
[Forms]![Section_Editor]![Signature].Value = SignatureString
End With
'***Shut down Word document***
Application.Quit
Exit_ButtonA1Macro:
Exit Sub
Err_ButtonA1Macro:
MsgBox Err.Description
Resume Exit_ButtonA1Macro
End Sub
Thanks for any help!
It works, but when I then click on any other form control or navigate to another record, the Word document again pops up in Word. I can then return again to Access, with Word quitting again. At that point, I can click on other form controls or navigate to another record without the document launching again in Word (i.e., it only happens once).
I am a novice with VBA, so the problem may be obivious--but unfortunately not to me. Here is the code I am using:
ACCESS OLE CONTOL CLICK EVENT
Private Sub Editor_Window_Click()
On Error GoTo NoOLEObject
Me![Editor_Window].Verb = VERB_OPEN
Me![Editor_Window].Action = OLE_ACTIVATE
Exit Sub
NoOLEObject:
If Err = 2684 Then
Me![Editor_Window].OLETypeAllowed = OLE_EMBEDDED
Me![Editor_Window].SourceDoc = "C:\Documents and Settings\ My Documents\Section_Template.docm"
Me![Editor_Window].Action = OLE_CREATE_EMBED
Resume
Else
MsgBox "Error - " & Str(Err) & " : " & Error
End If
Exit Sub
End Sub
WORD RIBBON BUTTON CODE TO RETURN TO ACCESS
'Callback for ButtonA1 onAction
Sub ButtonA1Macro(control As IRibbonControl)
On Error GoTo Err_ButtonA1Macro
Dim acApp As Access.Application
'***Reference running instance of Access***
Set acApp = GetObject(, "Access.Application")
**********some omitted code that defines and sets SecVariableString and SignatureString**********
'***Transfer fields to Access form***
With acApp
[Forms]![Section_Editor]![Variables].Value = SecVariableString
[Forms]![Section_Editor]![Signature].Value = SignatureString
End With
'***Shut down Word document***
Application.Quit
Exit_ButtonA1Macro:
Exit Sub
Err_ButtonA1Macro:
MsgBox Err.Description
Resume Exit_ButtonA1Macro
End Sub
Thanks for any help!