checking if textbox value exists and then going to that record

Hi,



I'm trying but unable to come up to the solution of a problem my database has.



There's a fill in form with a textbox called cnumber (same value name in table "patient") and the value in that needs to be unique. So by default if someone opens the form it goes to a fresh new record.



Now what would be really awesome is if someone puts a number in that textbox (it's a patient number from another system) like 1000, after they leave the field it checks the column if there's one with 1000 and if there is opens the row in the form.



If it doesn't exist yet the person will fill in the other parts of the form and it'll be a new record.

checking if textbox value exists and then going to that record