Validation rules

[Caroline]

wonder if anyone can help me - I have created a form to allow users to input
data from notices - each notice has a unique number. As our processes allow
for input of data from a master copy or the customers copy on occasion we
have duplicates. In the table I have designated the field "Notice Number" as
the primary key and in the properties made it required and indicated in the
index that no duplicates are allowed. However, if a duplicate notice number
is entered the system error message indicating that duplicate data has been
entered is not displaying until the whole form has been completed and the
user is attempting to close or move onto the next record - which is wasting
time. How can i create a validation rule in the table or the form which
prevents duplicate entries being entered and displays text to the user
warning that the notice is already in the databse as they move into the next
field rather than when they finish entering all the other data?

 

[Jeff Boyce]

Caroline

A simple (and some would argue "user-friendly") way to handle this is to NOT
require the users to enter the [NoticeNumber] and wait to the end to find
out it's already in there!

If your form has a combobox (the Header section is a good location) that is
NOT bound to any field, but displays all current [Notice Number]s, your user
can begin typing the number and Access will display a matching value if it
has it, or warn that the value isn't in the list (if you set the combo box's
LimitToList property to Yes).

You can add code to the combo box's NotInList event (see Access HELP for
syntax and examples) that prompts the user however you choose to (e.g.,
"This Notice Number is not in the system ... do you wish to add a new one?")
and accept the new one as the user determines.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Tom van Stiphout]

On Fri, 12 Oct 2007 03:54:01 -0700, Caroline

I'm not sure about a validation rule, but in the NoticeNumber
control's BeforeUpdate event you could write:

if DCount("NoticeNumber", "SomeTable", "NoticeNumber=" &
Me.NoticeNumber) > 0 then
Msgbox "Arrcchhhh, this number already exists!", vbCritical)
Cancel = True
end if

Of course you need to replace my sample object names with your real
ones.
Also, above I am expecting a numeric NoticeNumber. If it is
alphanumeric, don't forget to wrap the value with single-quotes.
Cancel=True cancels the BeforeUpdate event, and thus keeps the user
from moving out of the control until a non-existing NoticeNumber is
entered.
BTW, don't use spaces in your field names or control names, or pay the
penalty of having to wrap every name with square brackets.

-Tom.