Wait for Function to complete before continuing

A procedure is synchronous or Asynchronous.

Shell is the only Asynchronous procedure I could think.

Synchronous means it takes over and the next line executes when it is done. Asynchronous means it starts the process but continues running in parallel. The same principles used with Shell may work as apparently you have seen that solution.

What is your code to compact repair?

You need a 'Please Wait' function. Have a look at this:

HI lameID here is the code I'm using for the compact/repair

CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities").Controls("Compact and repair database...").accDoDefaultAction

Axworthy It's not the user I'm having problems with it access waiting untill the compact is complete

Thanks Chris

You need a table Exit_Now with a field Exit_Now with a datatype of Yes/NO. It needs to have a record. Add a second field X and make it Yes/No and the default value Yes and a validation rule making it yes. This will keep additional records from being created.

Access is asynchronous in the way it executes all commands. When running a line of code that must be completed before executing the next line of code, the usual workaround is to insert the DoEvents command between the two. This relinquishes control to the system so that it can finish its task before Access moves on.

Another approach, especially useful when opening up a second form to do something,such as adding an item for inclusion in a combobox, is to open the second form in Dialog Mode. This halts code execution i the primary form until the second for is closed.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

Access is asynchronous in the way it executes all commands. When running a line of code that must be completed before executing the next line of code, the usual workaround is to insert the DoEvents command between the two. This relinquishes control to the system so that it can finish its task before Access moves on.

Another approach, especially useful when opening up a second form to do something,such as adding an item for inclusion in a combobox, is to open the second form in Dialog Mode. This halts code execution in the primary form until the second for is closed.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

While do events is interesting, compact and repair technically closes the database and reopens it so doevents won't be helpful unless you are compacting and repairing from another instance.

In looking back over the original post it occurs to me that if the purpose is to do a C & R and exit the app, why not simply go to Options and set Compact On Exit, not that I'd ever do that, but people do. The problem with simply automating C & R is that, paradoxically, compacting a database is ofter cited as a cause for corruption! Hence, it really should always be preceded by copying the db to a safe location.


The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

Chris,

The Please Wait function will stop execution of code until the 'please wait' form is closed. Calling a function where the example does the OpenQuery will complete before the code continues where the DoCmd.RunMacro "Test1" called it.

Give it a try.

How are ya Ktx7ca . . .

I've been following this thread, looking for a place to step in, and I can't wait any longer. Visually ... on screen, if you take into account what [blue]Compact & Repair Database ...[/blue] does, you'll see the following:
[ol][li]Compact/Repair is performed as [blue]depicted by the status bar.[/blue][/li]
[li][purple]The database is closed![/purple] teminating everything ...[/li]
[li]The database is opened [blue]in its newly compacted state![/blue][/li][/ol]
So ........ any code set to execute [blue]compact/repair the db[/blue] is terminated anyway!

Your problem is how to continue when t[blue]he db is reopened in its newly compacted state![/blue] ... Ya Think! [thunmsup2] ... are you with me? ...

You go forward by creating a db property... 1st, then you use this property to continue your code thru an [blue]AutoExec[/blue] macro!

[blue]Your Thoughts? . . .[/blue]



See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

TheAceMan1,

Database property as opposed to my table solution. I like it.