Undo does not work like Esc key, for a combo box / text entered is not in the list (1 Viewer)

[Ihk]

May be there is already solution for it on web but but could not find it.
Looking for exact match of "ESC" key in vba
Reason:
I am facing two problems on my form.
My form has combo box with listed items.
This field in table is set to "required" and "Numbered", because its source is from another table and has enforced referential integrity.

Problem# 1
If someone enters words, which are not in combo list, then the user gets stuck. Can not udo it.
Error message received is "text you entered is not in the item list" "select an item which in the list......" I agree with this, yes this has to be, but the problem now the person is stuck.
I tried a click button with
1) Me.undo
2) by default access undo button macros, also tried
3) SendKeys "{esc}", True
4) DoCmd.RunCommand acCmdUndo
5) Me.Field1.value = Null , or Empty or "" etc
all of them wont work.

Only "ESC" key from keyboard works.

Backspace also works from keyboard, but with this I have another problem#2.

Problem#2
With backspace (keyboard) words are deleted, now user gets stuck at an error "You must enter the value in table field........." because it is required.
Now user can not close the form, can not undo and just stuck here.

How can these two problems be solved? Thanks

 

[theDBguy]

Hi. Is your Combobox bound? Have you tried using the Not In List event? Did you set a Default Value for it?

 

[Isaac]

Train your users to use the Delete key on their keyboard to delete the text that is invalid in the combobox.

And get rid of whatever VBA code you have that is causing this situation:

With backspace (keyboard) words are deleted, now user gets stuck at an error "You must enter the value in table field........." because it is required.
Now user can not close the form, can not undo and just stuck here.

Rather, your validation should be mostly in form's beforeupdate event.

 

[Ihk]

Hi. Is your Combobox bound? Have you tried using the Not In List event? Did you set a Default Value for it?

Yes Comobobox is bound, Limit to list. It does not have any default value. No I have not tried not in list event. how do you suggest this? thank you.

 

[Ihk]

Train your users to use the Delete key on their keyboard to delete the text that is invalid in the combobox.

And get rid of whatever VBA code you have that is causing this situation:



Rather, your validation should be mostly in form's beforeupdate event.

even if the text is deleted with backspace or with delete key, then Problem#2 appears (mentioned above).
Because the field is set to required in the table.
I tried this, in the table I set this required = no, then it works... but
Though my form has "before update event" to confirm record save. In this case if user accidently press "Yes" then Empty field value is entered in the table... thats why I must have to
set field required = yes and before update event on top of it.

 

[Isaac]

even if the text is deleted with backspace or with delete key, then Problem#2 appears (mentioned above).
Because the field is set to required in the table.
I tried this, in the table I set this required = no, then it works... but
Though my form has "before update event" to confirm record save. In this case if user accidently press "Yes" then Empty field value is entered in the table... thats why I must have to
set field required = yes and before update event on top of it.

You're saying that just because the column is set to Required at the table-level, a person is incapable of having their cursor in that field, entering nothing (blank), and then Tabbing out of the textbox control?

 

[Ihk]

You're saying that just because the column is set to Required at the table-level, a person is incapable of having their cursor in that field, entering nothing (blank), and then Tabbing out of the textbox control?

Now half of the problem (Problem#1) little bit solved. As suggested by @theDBguy , I tried "Not in list event" on combo control.
Msgbox ..... VbOkOnly, By pressing OK result into "DoCmd.RunCommand acCmdUndo"
It clears the field, but
1) still gives DB default error message "Text entered is not in Item list. Afterwards, it automatically expands combo list for users to select from.
2) Also at this stage if user decides to close the form, and dont want to enter any data, Now it is letting to tab out user. keeps giving message "You must enter the value in table field..."
Only Escape key escapes from this situation, after getting errors.

 

[Isaac]

You need to study and understand how Access saves records. You're all over the place. The NotInList solution is nice, I'm glad you found it helpful, but I was interested specifically in the root cause of why you felt you "had" to enter something in the field.

You need to study up on:
1) how and when access saves records, if you don't design to correct that (i always do, as it only takes a minute, and access's aggressive saving is counter intuitive to any normal user & defies their expectations - as you are finding out & this post proves)
2) how form cycling works, and why that might be giving you a bad result on Tabbing out of a field, since simply tabbing out of a textbox that is bound to a Required table column and leaving the textbox or combobox empty is NOT a problem, all else being equal. Your probably is probably coming up because that last Tab tries to cycle the form to a new record - which then triggers the current record to save - which it can't, because the column in the table is required. Nothing to do with trying to simply tab out of a blank control that's bound to a required field.

 

[theDBguy]

Yes Comobobox is bound, Limit to list. It does not have any default value. No I have not tried not in list event. how do you suggest this? thank you.

Take a look here.

ComboBox.NotInList event (Access)

Office VBA reference topic

docs.microsoft.com

 

[arnelgp]

simple code to "revert" the combo if "notInList" occurs:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)
    MsgBox "Item not on the list."
    Me.Combo0.Undo
    Response = acDataErrContinue
End Sub