Microsoft Office 2010 Professional Plus Power Tip

When you are in the process of entering a new record in an Access database,Office 2007 Enterprise, you can press the Escape key to cancel the entry of the record. However, some events, such as clicking or tabbing into a subform on the main form, can save the record on the main form. At that point, you can't press Escape to cancel the entry—you must use the Delete command to delete it. The end-users of your database might not understand the difference, and would probably want a single button or keyboard shortcut that just gets rid of the record, regardless of whether it’s been saved or not. To do this, you need to be able to which of these two states the record is in: a) The record is being built up, has been assigned an AutoNumber ID,Office Professional 2010, but hasn't been committed to the table yet
- OR -
b) The record has already been committed and the ID indicated is already persistent. The solution is to add a module-level variable (mboolIsInsert ) to the parent form. The variable is set to True in the BeforeInsert event on the form, and reset to False on the OnCurrent event. The OnCurrent event doesn’t fire for the parent form when you click or tab into the subform, only when you directly navigate on the main form.  You can then check the value of the module-level variable in your code to see if you’re in an insert event and respond as appropriate. Option Compare DatabaseOption Explicit Private mboolIsInsert As Boolean Private Sub Form_BeforeInsert(Cancel As Integer) mboolIsInsert = TrueEnd Sub Private Sub Form_Current() mboolIsInsert = FalseEnd Sub Public Sub CancelOrDeleteRecord() If mboolIsInsert Then 'Add code to perform escape-equivalent cancel Else 'Add Delete command to delete the current record End IfEnd Sub Running the CancelOrDeleteRecord() procedure (for example, from a button on the main form) provides a more usable form by always getting rid of the record,Windows 7 Download, regardless of whether it's been saved or not. Thanks to Access PM Russell Sinclair for the tip,Office 2010 Product Key! The Power Tips feature relies on you,Microsoft Office 2010 Professional Plus! Send your tips to Mike & Chris at accpower@microsoft.com. <div