Warning of Duplicate on Form

[copterdan]

I need to warn of duplicate entry based on a user-entered field in a form. This is for an employee database and the field in question is the Social Security Number field. This field needs to be able to accept duplicates so setting the property to unique is not an option. After the user enters the employee’s SS#, I want a message to pop up warning of a duplicate if one exists. If the record is not a dup. then there will be no message. Please help if you can. I am not to good at VB so if this is the best solution please provide as much description as possible. Thanks!

 

[FoxProProgrammer]

I am assuming that the SSN is not the primary key in the table. If it is, Access will prevent you from entering a duplicate. If it isn't the primary key, you could check for duplicates in the On Exit event of the text box where the SSN is entered.

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("yourtablename", dbReadOnly)

rs.FindFirst "SSN = " & Trim(Str(txtSSN))

If Not rs.NoMatch Then
MsgBox "Duplicate SSN Found.", 64, "Warning"
End If

Note that this example assumes that the SSN is a numeric field in the table, and that the field on the form where the user enters the SSN is named txtSSN. If the SSN is stored as text in the table, use the following line to look for the record instead.

rs.FindFirst "SSN = " & Chr(34) & txtSSN & Chr(34)

This is just one of several ways to do this. You could also use a DLookUp, but I think that FindFirst is faster. Another way to do it is to use a RecordsetClone if your form is bound to a table or query and no filter is set (i.e. the Form's recordset contains all records from the table.)
dz
dzaccess@yahoo.com

 

[copterdan]

DZ, I did as your suggested but I am getting a compile error. "User-defined type not defined". The problem is with the Dim db as Database statement. I noticed that when I wrote the code, that Database was not in the list of choices that came up; however, Recordset was. I am usning Access 2000 if that makes a difference. Also, you are correct in your assumption, the SSN is the the primary key. Thanks.

 

[copterdan]

Okay, I solved the User-Defined type not defined problem by installing the DAO 3.6 Object reference library. Now I am getting an error message stating that the method or Data Member is not found. The error is on the “If Not rs.NoMatch Then” line. I have copied the actual VB code below for reference. Please help if you can. Thanks!

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset([TblEmp], dbReadOnly)

rs.Find "SS#=" & Chr(34) & SSN & Chr(34)

If Not rs.NoMatch Then
MsgBox "Duplicate SSN Found.", 64, "Warning"
End If

 

[FoxProProgrammer]

Hi copterdan,

You were right to install the DAO library to solve the first problem. The second problem is due to not enclosing the table name in quotes. Also, do not use brackets in this case. The Set statement should look like this:

Set rs = db.OpenRecordset("TblEmp", dbReadOnly)

Just curious if you meant not the primary key?

<the SSN is the the primary key.>

Best,
dz
dzaccess@yahoo.com

 

[copterdan]

Yes, I meant NOT the primary key. Thanks for the help. Unfortunetly, I am still having problems. I am now getting an error message stating that the Method or Data Member is not found. This seems to be happening on the rs.FindFirst line as well as the rs.NoMatch line. Any suggestions? Am I missing another library? Below is the updated code. Thanks for all of your help.

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;TblEmp&quot;, dbReadOnly)

rs.FindFirst &quot;SS#=&quot; & Chr(34) & SSN & Chr(34)

If Not rs.NoMatch Then
MsgBox &quot;Duplicate SSN Found.&quot;, 64, &quot;Warning&quot;
End If

 

[FoxProProgrammer]

Hmmm, When you typed rs. were FindFirst and NoMatch in the list? If not, then it doesn't recognize rs as a valid Recordset. When you typed Set rs = db.OpenRecordset, was OpenRecordset one of the choices after you typed Set rs = db. ? If not, then it doesn't recognize db as a database. You might try explicitly defining the database and recordset as DAO, but I don't think that is the problem.

Dim db As DAO.Database
Dim rs As DAO.Recordset

If you can't get it to work and want to email me a copy, I'll take a look at it. TblEmp is the name of a table in the Current database, right?
dz
dzaccess@yahoo.com

 

[copterdan]

It worked!!! NoMatch and FindFirst were not in the list until I modified the DIM statments by adding DAO.Database and DAO.Recordset. It now appears to be working perfectly. Thanks for all of your help and patience!

 

[FoxProProgrammer]

You're welcome. Access must have defined your db and rs as ADO without the DAO designation. I'm glad that it worked, and thanks for taking the time to let me know. dz
dzaccess@yahoo.com