Trying to trap when a user cancels an insert from a form.

L

Legacy 456155

Guest
Sort of like an UnDirty event or CancelInsert event. I could not find anything about this on the web so maybe my design is faulty or not reasonable. I have an unbound textbox (txtSearch) that shows a listbox if the user enters search text. The listbox contains a list of existing records, which, when one is selected, loads an existing record. If the user adds a new record, I disable txtSearch to disallow trying to add a new record using the listbox in the midst of an insert operation. However, if the user cancels the insert by pressing the escape key or in some other way (is there another way?), I want to re-enable and set focus to txtSearch. I suppose that I could use KeyPreview and capture the escape key. What do you think? Is that comprehensive enough? Any ideas? Thanks for your input! :)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,268
Office Version
  1. 365
Platform
  1. Windows
Not exactly sure of what you have there (I mean is the form unbound or not? A subform on a form?) Maybe the form Undo event will work for you. There are other ways to undo a record but I think you would have to design it in, so I cannot think of anything other than esc. Designed methods could include a Cancel button or getting user response to a prompt if they attempt to navigate off the record.
 
L

Legacy 456155

Guest
Thanks Micron. It is a simple form bound to a single table (Contacts) that allows a user to select existing contacts or create new contacts. If they begin an insert operation by creating a new contact and then decide to search through existing contacts using the listbox without canceling the current insert, that is where the problem is. They are in the midst of an insert operation and also trying to add an existing contact. So, I disable txtSearch when an insert begins and re-enable when all required fields have been entered because they may now move off the record. I suppose that I could or maybe should use more prompts, but I tend to try and develop some basic understanding of the rules to avoid so many prompts. It's an in-house system.

VBA Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
    txtSearch.Enabled = False
End Sub

There are other ways to undo a record but I think you would have to design it in, so I cannot think of anything other than esc.
If the escape key is all I have to account for, then...

VBA Code:
Private Sub Form_KeyUp(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyEscape And Not Dirty Then
        txtSearch.Enabled = True
        txtSearch.SetFocus
    End If
End Sub

...may do. I can always add prompts if my users have problems with my design. Thanks for your input. :)
 
L

Legacy 456155

Guest
Thank you, Micron, for your time and insight. Have a nice weekend. :)
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,268
Office Version
  1. 365
Platform
  1. Windows
NP, thanks. Good luck with your database(s)!
 

Forum statistics

Threads
1,144,704
Messages
5,725,847
Members
422,647
Latest member
madelinea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top