code only works when run twice, why?

[Robin]

I have an OLE unbound frame linked to a Word 2007 document on a form and on a
subform embedded in a report. And the following code will change the Source
Doc property files. But it has to be run twice to accomplish the changes. I
am at a loss for why. A command button on click property executes the code
which opens a file selector dialog box to specify the path and filename, and
then updates the Source Doc properties.

But when I click the command button it goes through all of the proper steps.
Asks for the file and when selected, I can see the instances of word open on
the task bar. But the files are not changed. If I click the button and
select the same file a second time, everything appears to work the same, word
instances, etc., but the files actually change.

Private Sub cmdChangeAD_Click()
On Error GoTo Err_cmdChangeAD_Click

' Allows User to select file for new AD copy on the Billing Invoices.

Me!BillingMemo = BrowseForFile("Please Select Desired Word Document")

' Opens the form and updates the Source Doc property of the ole field.

DoCmd.OpenForm "InvoicesToPrintForm", acNormal, , , acFormPropertySettings,
acHidden
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.Class = "word.docx"
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.OLETypeAllowed = acOLELinked
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.SourceDoc = Me!BillingMemo
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.Action = acOLECreateLink
DoCmd.Close acForm, "InvoicesToPrintForm", acSaveYes

' Opens the subform on report and updates the Source Doc property of the ole
field.

DoCmd.OpenForm "frmBillingFormSub", acNormal, , , acFormPropertySettings,
acHidden
Forms!frmBillingFormSub.Controls!OLEUnbound0.Class = "word.docx"
Forms!frmBillingFormSub.Controls!OLEUnbound0.OLETypeAllowed = acOLELinked
Forms!frmBillingFormSub.Controls!OLEUnbound0.SourceDoc = Me!BillingMemo
Forms!frmBillingFormSub.Controls!OLEUnbound0.Action = acOLECreateLink
DoCmd.Close acForm, "frmBillingFormSub", acSaveYes


Exit_cmdChangeAD_Click:
Exit Sub

Err_cmdChangeAD_Click:
MsgBox Err.Description
Resume Exit_cmdChangeAD_Click
End Sub

Any suggestions would be greatly appreciated. If I can nail this down I can
deliver the project.

Thank you,
Robin

 

[Tom Wickerath]

Hi Robin,

I don't know if this will help, but you might try adding a few DoEvents
statements. I'd start by adding several of them. Then, if the problem appears
to be solved, start removing them one-by-one until you experience the problem
once again.
Something like this:

Private Sub cmdChangeAD_Click()
On Error GoTo Err_cmdChangeAD_Click

' Allows User to select file for new AD copy on the Billing Invoices.

Me!BillingMemo = BrowseForFile("Please Select Desired Word Document")

' Opens the form and updates the Source Doc property of the ole field.

DoCmd.OpenForm "InvoicesToPrintForm", acNormal, , , acFormPropertySettings,
acHidden
DoEvents '<----
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.Class = "word.docx"
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.OLETypeAllowed = acOLELinked
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.SourceDoc = Me!BillingMemo
DoEvents '<----
Forms!InvoicesToPrintForm.Controls!OLEUnbound49.Action = acOLECreateLink
DoEvents '<----
DoCmd.Close acForm, "InvoicesToPrintForm", acSaveYes
DoEvents '<----

' Opens the subform on report and updates the Source Doc property of the ole
field.

DoCmd.OpenForm "frmBillingFormSub", acNormal, , , acFormPropertySettings,
acHidden
DoEvents '<----
Forms!frmBillingFormSub.Controls!OLEUnbound0.Class = "word.docx"
Forms!frmBillingFormSub.Controls!OLEUnbound0.OLETypeAllowed = acOLELinked
Forms!frmBillingFormSub.Controls!OLEUnbound0.SourceDoc = Me!BillingMemo
DoEvents '<----
Forms!frmBillingFormSub.Controls!OLEUnbound0.Action = acOLECreateLink
DoEvents '<----
DoCmd.Close acForm, "frmBillingFormSub", acSaveYes
DoEvents '<----

Exit_cmdChangeAD_Click:
Exit Sub

Err_cmdChangeAD_Click:
MsgBox Err.Description
Resume Exit_cmdChangeAD_Click
End Sub



Good Luck,

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robin]

Thank you for the suggestion. I tried a couple DoEvents previously, just
prior to each of the docmd.openform lines. And now again as you say with
every other line being DoEvents. But no difference.

Woke up early bothered by it as always. And I thought that Access mut not
be accepting my path/filename the first time. So I added a Requery of the
form just after the BrowseforFile piece and it works on the first go around
as we would have expected initially. Thank you.

This does still leave me with the fact that each of the users must update
the word docx individually to have the latest AD copy print on the billings.
Do you know of any way to accomplish the linked file type action at runtime.
It seems the links are embedded in the forms/reports at runtime. I have had
no success what so ever trying to do this with bound object frames.

I was hoping to have one user, management, be able to adjust the billing AD
and have it populate through the other users. I have stored the
path/filenames in the BE but can find no way to make this useful to the
SourceDoc property at runtime. But as I say this is the best I've been able
to come up with to date. It seems I'm missing something on the overview of
the OLE automation

Thank you very much for your help.
Robin

 

[Tom Wickerath]

Hi Robin,

I'm glad to hear that you got the first part solved.

Do you know of any way to accomplish the linked file type action at runtime.

Not really. I can't say that I've ever had a need to try doing this before.
Sorry.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________