Check for all-ready resist?

[Denniz79]

Hello,

I have a customerinfo table. I've made a form in which you can add the new customerinfo. Now i really want to know how i can make the textbox so that i can see if the name already excists in the table or not! E.g. when i today add mr. Kirkland and tomorrow i do this again, i want to see it, understand?

Can you help me with this problem? I'm a beginner in Access!

Thank you already,

Dennis

 

[dcx693]

Why not set up your customer table so that duplicates are not allowed? Of course, you could legitimately need two different Mr. Kirklands in your database, so make the last name and first name, or last name+ first name+address the primary key for the database. If you've got an autonumber that you'd prefer to use as the primary key, then you could use a DCount function in the Before Update event of the control to check the table for any previous entries.

 

[Denniz79]

Hello DcX693,
thank you for your reply, yes i indeed i have the autonumber as the prim. key.
So, can you explain me how to do it then. I only know what the BeforeUpdate means for the rest i can't follow you, sorry... i'm a beginner!

Thanks!

Dennis

 

[maxmangion]

in the Before update of you control you should do something similar to the following:

Dim counter As Integer
counter = DCount([FieldName], [QueryName], "Validate = [Forms]!FormName!ControlName")
If counter > 0 Then
If MsgBox("This record already exist!" & vbCrLf & "Discard Record (Yes) or Amend Record (No)", vbYesNo, "Duplication Error") = vbYes Then
Cancel = True
Me.Undo
MsgBox "Record Discarded Successfully", vbOKOnly, "Note"
Else
Me.ControlName.Value = ""
Me.ControlName.SetFocus
End If
End If

in the query you are getting the data from place a new field "Validate" and set it's criteria to [Forms]![FormName]![ControlName]

Note, that you have to replace your "Names" (field names, form names etc) in the above code

 

[ColinEssex]

Another way that I have used on occasions is to force the user to go to a search screen first and check if the person is already registered.
In other words, they can't go direct to the person detail screen without first going to the search screen.

Col