Sendkeys/Compacting inconsistencies (1 Viewer)

[AlanS]

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

 

[willem]

Try for case 1
dim blncomp as boolean

blncomp=true
docmd.close

and for case 2:

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

I don't know how the boolnvalue should be initiated. Public, static, private.

Willem

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

 

[Talismanic]

To help keep this question from going in seperate directions here is the other post.

Topic: SendKeys/Compact inconsistencies - From the forms section.

 

[AlanS]

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.

 

[Rich]

You could buy that wonderfull product A2K which compacts on exit.

 

[AlanS]

Yes, I could buy that wonderful product, but I'd rather go for a root canal. I'd prefer to deal with a minor inconvenience like not being able to compact on exit, than put up with all the new "features" in Access 2K (can't use Access 97 databases without conversion, breaks Access 97 VBA code, a totally lame help system, etc.). This seems to me one more example of how Microsoft products tend to alternate between reasonably decent releases (Access 2.0, Access 97) and really awful ones (Access 95, Access 2K).