need help with creating a rule for Access forms!!

[sonship]

I am new to Microsoft Access, and I would like to know if there is a way to create a rule so that when someone enters an address for a customer, it will search all the records already entered to prevent them from typing a duplicate. If so how do I go about creating that rule. Thank you for any help.

Charles

 

[dumdum]

Check out the Dlookup function in MS help. One of the problems you will experience is that if you check for exact matches you may not get back the correct information i.e. if someone uses the letter "W" instead of "West" or they use "Rd" instead of "Road"...

I usually use the first 4 numbers in the address to check against and then provide the user with a the list of those matches so they can determine if they are entering a duplicate.

 

[sonship]

Thank you for your help, however I am not very familiar with Access yet, and I don't understand how to create a Dlookup. What do I do to tell it to search just the first 4 numbers in the address like you suggested?

 

[dumdum]

My production machine is down but I'll try and do this from memory..

Dim varX As Variant

varX = DLookup
("[address]", "customers", "left$([address],4) = " _
& left$((Forms!customers!address),4)

I know this isn't absolutely correct. I'm just going from memory. You can also use the "Like" operator.

I am sure someone here has the correct answer.