Graceful Exit after Search Error in Access Form

Lookup Error when search value is not foundDrop Down LookupHi,

  In my  ACCESS database, I have a table where I store company names and passwords.
 and form where I have two records - "AAA" and "CBS" . When searching for the record, if I enter non-matching value, then I get an error.
  Getting an error is ok, but it does not have what I call "graceful exit".
  For example, if I am typing "ab" and press [enter], I get an error message (as seen in the screenshot).
  What kind of visual basic code can I use to provide a graceful exit?

Thanks.
LVL 1
sgleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Yes, you can use the Not In List event to give the user a nice prompt to add the value or not and gracefully handle either answer.  Look over http://www.databasedev.co.uk/not_in_list.html for an example of how this is done.
sgleeAuthor Commented:
@Daniel,

I will try that code and report back tomorrow.
Mark EdwardsChief Technology OfficerCommented:
Since the default setting for the Not In List property is "No", then someone probably had to want to change it for a reason. If you want to get out, just hit the Escape key twice after clicking OK.  It will revert the entry to the last legal entry and allow you to get out, or you can just clear the value in the control if you want to keep the Not In List property set to "Yes".

But as mentioned earlier, changing the setting Not In List property to "No" will allow the user to enter items NOT in the list (free entry).

If you are using a Value List as a rowsource, you can set the Allow Value List Edits to "Yes" and get a pop-up list edit form that allows the user to add/delete values to the Value List.  You can enter the name of a custom list editing form of your own design in the List Items Edit Form property to edit a list too.

It would help to know what kind of Rowsource you are using.
Gustav BrockCIOCommented:
Hmm .. that error message is to the point. Somehow the user must know the truth.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.