Validation Field, that checks for dup values

[supportt]

Hi, I have a simple form that users enter customer info on. One field is call T#, using Validation, could it check for dup T#'s ? after the user steps off the field?

Currently, if the user enters a new record and using an exsisting T#, it does write the record to the DB; but you can't use that record. So, I want to avoid dup records that have the same T#.

Thanks

David

 

[dcx693]

Why don't you set up the table so that you don't allow duplicates in the call T# field?

Otherwise, you can use a DCount to check for previous existence.

 

[supportt]

Validate Field values

The data is coming from a seperate table and I do have the T# filed set for no dups. So, I guess what I need is a way to let the user know they entered a dup T#?????

Thanks for the quick reply, you guys/gals have the BEST Forum around....

David

 

[dcx693]

In your form, set up some code in the form's Before Update event to check for a dupe number.

Use the DCount function. Something like this:

If DCount("[T#]","tablename","[T#]=" & Me.FieldName)>0 Then
'display message or something
Cancel=True
End If

 

[supportt]

That did not work for me

I tried adding this code to my Form, BeforeUpdate function.

If DCount("[T#]", "CARRIERS", "[T#]=" & Me.T#) > 0 Then
'display message or something
Cancel = True
End If

And when I goto to compile, it gives me this error:
Compile error: Method or data member not found

Then it highlights the .T# at the end of the first line. I did check to make sure I have the correct table and field name, no problem there.

Any suggestions????

Thanks

 

[Mile-O]

T# is a bad choice of name for a field or object.

Look at the bottom of this article.

If you persist with it then Me.[T#] should end that first line of yours.

 

[supportt]

Additional thoughts

I thought about the field name and did change it to T_NUMBER and added the [] around it. Well no more errors and if you type a T# that already exsist, it does not write the record to the DB. But, it does not let the user know it was a dup T#

What is your thoughts on that....?

Thanks again for your help

 

[dcx693]

Did you see that line of code in my post that read:
'display message or something

That was meant to show you where you can put some code to display your own custom message. You can put some code like this in it's place:
Beep
MsgBox "That's a dupe!"

 

[supportt]

Sorry, I missed that...

Dah! I guess that's what happens when you don't get enough sleep...

I will give that shot...

Thanks Again

 

[supportt]

One last question

Everything seems to be good, except for one last item....After the message pops up and basically says you enter a duplication T#, the user clicks OK, but, then the window closes. Is there a way to keep it open so they could enter a good T# ?

Thanks Again

 

[dcx693]

You mean instead of just disappearing and placing the user back in the form? Yes. You can use the MsgBox function instead of the MsgBox action. The big difference being that the MsgBox function can take input from the user. Look it up in the Access online help.