Checking for Duplicates as data entered on form

[Sam]

I need help coding the following solution...

When users are entering data into a form, I would like
the system to check and indexed field "ID NO" on the
table. If the "ID NO" does not exist, then it will
continue normally, but, if the "ID NO" already exists,
then a edit form will pop up where the user can edit the
existing record.

I have been trying to use NewRecord function and/or
Dlookup with no success. I have the edit pop up form
already created.

 

[Jeff Boyce]

Sam

Another approach would be to use a combo box and list existing IDs, or
better yet, more meaningful information (say, person name, if that's the
topic). You can add a NotInList event to handle adding a new record.

From a user's standpoint, not having to remember and correctly enter an ID#
is a plus!

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks Jeff...

But the database will be keeping student records (around
6000 of them) so that option will not work for that many
records. That's why i need this feature, so the data
entry person doesnt duplicate students.

thanks anyhow for your suggestion

Sam

 

[Patrick G]

Couldnt you make the ID No field in the table Unique not
allowing duplications?

Another option is to use DLOOKUP to check for the IDNo.

IsNull(DLookup("[IdNo]", "tblOfStudentsInfo", "[IdNo]= "
& ctlYourIDnoControl))

If its null theres no duplication.

 

[Tom Ross]

The Dlookup technique from patrick will work but think again about the
combobox option from Jeff

With the combo box, you can still type in the ID (you don't need to select
from the list) but then the NotInList event would work to identify