Delayed event in a Macro? 2

[owens2024]

Hi -

Here is what I am trying to do...

After a user enters information into a form a message box pops up and asks if he/she would like to print follow-up letters (based on the data just entered).

If the user clicks 'Yes' a query runs (to select records that don't have a follow-up letter) and then MS Word opens with a Mail-Merge document based on the query. The query then closes.

Up to this point everything works fine but I would like to add a step where after the user closed Word (or possibly even after a certain amount of time say 10 minutes) an update query would run to indicate that the letters had been sent. I tried a messagebox that the user could click when done but this stopped Access and Word couldn't get to the database to do the merge.

Any ideas?

Thanks!

- Lee

 

[Yarcadian]

Have you tried to create the update query and have it run after the code to open word if the user selects yes?

 

[CajunCenturion]

How is MS Word being opened?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[owens2024]

Thanks for the replies...

Yaracadian - yes I have created the update query and tried to make it run after Word opens if the user selects yes. The problem is that Word needs to get to Access to do the merge and once I do the messagebox Access stops and waits for user input. I guess what I need to figure out is how I can create a messagebox that won't stop Access...

CanjunCenturian - I am opening Word through a RunApp command in the macro.

Thanks!

- Lee

 

[CajunCenturion]

There are a couple of approaches that come to mind, but require the use of some APIs.

One use the FindWindow API inside of a timer to check for the existence of the MS Word window. Once the window is not found, then you know that MS Word has been closed.

A second approach is a little more involved, using the GetWindowThreadProcessId API to get the Process ID of the MS Word application, and then the WaitForSingleObject API to suspend your application until Word process terminates.

There are pros and cons to either approach. Are you interested in pursuing either of these two options?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Yarcadian]

Okay, so why not kill the message box all together, add a command button that they can click when they're ready to run the merge? This way you're not waiting for their input and the query would be completed before the merge is started.

 

[owens2024]

Would the command button be in the form?

- Lee

 

[Yarcadian]

Yes, I would put it on the same form where they do the key entry. With the button there, they won't have to open/close another form to run a button. You could also create a second query and tie that in with the current record to only merge that one record. I would point the record number field in the query to the one on the form via the criteria.

David