I'm not sure if this is a Forms issue or a VBA issue, so I'm cross-posting - please pardon the duplication. I have a form with a command button named cmdExit. The form's module includes the two short Subs below.

Case 1: When I close the form using the "X" button at the upper right, Form_Close is executed and the database is automatically repaired and compacted. So far so good.

Case 2: When I click the cmdExit button, cmdExit_Click is executed and the application is terminated. The problem is that even though part of that termination is closing the form, the repair and compaction do not occur.

If I put a MsgBox function at the beginning of Form_Close, it is displayed in both Case 1 and Case 2, but the repair and compaction still only happen in Case 1. I've spent a day trying all sorts of work-arounds (such as copying the contents of Form_Close to the beginning of cmdExit_Click, calling Form_Close at the beginning of cmdExit_Click, changing the SendKeys [wait] argument from False to True, etc., etc.), but nothing works. I've also noticed that if I add code to the end of Form_Close, the existing code stops working in all cases. All this is especially vexing because (1) when I first deployed this application about two weeks ago, repair and compaction worked fine in both Case 1 and Case 2; and (2) this application's sole purpose is to repair and compact other databases, which it does admirably.

Any ideas on what's going on would be greatly appreciated.

Private Sub cmdExit_Click()
DoCmd.Quit 'exit application
End Sub

Private Sub Form_Close()
DoCmd.SetWarnings False 'turn off warning dialogs
SendKeys "%TDR", False 'repair current database
SendKeys "%TDC", False 'compact current database
End Sub

i can see no error in your code since you also stated that it all worked fine previously. however, i would like to suggest changing the SENDKEYS line to:

Docmd.RunCommand acCmdRepairDatabase 'repair database
Docmd.RunCommand acCmdCompactDatabase 'compact database

this is more natural than using sendkeys to run a built-in function of Access, especially, accessing menus.

and one last thing about the cmdExit_Click() sub, run a form close line before quiting the program making sure you close the form properly and that all necessary codes run.

Hi,

is there a way of getting the run command to run on the current DB ( i'm using 97) ?

cheers

Drew

ps i came across this page which may be of some use http://home.houston.rr.com/jeffkohn/compactcurrent.htm


[This message has been edited by KDg (edited 08-07-2001).]

Not sure if this is the reason but I use SendKeys "%(TDC)", False.
HTH

if you use SendKeys to access menu items then you will have problem if the menu bar/toolbar is hidden because the line will not trigger the action of the menu. it's better to use DoMenuItem, if that's the case. however, RunCommand already replaces DoMenuItem. i find the new method easy and straigtforward.

Thanks to everyone who responded for your suggestions. Unfortunately, I haven't been able to make any of them work. Changing the SendKeys [wait] argument to True makes the "Compact From" dialog box appear, and I need this operation to proceed without user input. The same thing happens when one uses DoCmd.RunCommand acCmdCompactDatabase. I also haven't been able to make this work with static or global flag variables to control the flow. If anyone has a working example of an Access application that will compact itself and close itself, all in the same operation without any user input, I'd love to see how it's done.

You can use the TSI SOON utility to close a database, compact it, and then open it again:
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Hope this helps,

--
Peter De Baets
Peter's Software - MS Access Tools - http://www.peterssoftware.com