validation rule in form

[Guest]

I have a simple form I use for my football team. name address etc.etc.
I have a place for the number on the jersey.
I want to be able to not allow duplicates. In the table I have it set up
with that item indexed and yes (no duplicates)
say in record #1 I type in 34 as the number.
on the next record if I type in 34 again I want to see an error message
before I go the next item in the form and not wait until the end to find out
I have aduplicate number. I am looking for some sort of simple validation
rule. any idea's?
Thanks

 

[Guest]

Hi jfab1,

In the after update event of your jersey number field, have it use a dlookup
to see if there is another jersey with the same number, like this:

if not isnull(dlookup("JerseyNumber", "TABLENAME", "JerseyNumber = " &
me.lngJerseyNumber & " and RECORDID <> " & me.lngRECORDID)) then
msgbox "This is a duplicate number"
me.lngJerseyNumber = null
endif

Then you just need to work out what you want to do about it - perhaps set it
to blank as I did in my sample above.

Hope this helps.

Damian.

 

[Guest]

Damian,
Thanks for the reply.
I seem to have trouble with the part
& me.lngJerseyNumber
i keep getting a compile error.
Any suggestions?

Thanks