Duplicate Warning Box

  • Thread starter Thread starter Sarah
  • Start date Start date
S

Sarah

I have a recruiting database which has the name (first, last) of the
individual who is applying for a position. The first and last names are in
different cells. I would like to find a way where when an individual is
entering a new record a warning will pop up stating that there are other
records with that same name (most likely last name) and allow the individual
to choose if they would like to continue or not.

Example:

Candidate Information Table
First Name Last Name
Jane Doe

When the individual wants to enter a record for John Doe a warning pops up....
 
Sarah,

You do not say but are you using a form for data entry?

I appears from reading your posting that you may be entering data directly
into a table.
 
Yes, I am entering into a form.

Mr B said:
Sarah,

You do not say but are you using a form for data entry?

I appears from reading your posting that you may be entering data directly
into a table.

--
HTH

Mr B
askdoctoraccess dot com
Click to expand...
 
Sarah,

You will need to use VBA code to run an SQL statement (query) to determine
if there are any existing records in the table where the Last Name and the
First Name already exist.

The most simple way to do this would be to use the BeforeUpdate event of the
Last Name Control and the BeforeUpdate event of the First Name control and
place code like:

Private Sub ItemID_BeforeUpdate(Cancel As Integer)
If not isnull(me.NameOfLastNameControl) and _
not isnull(me.NameOfFirstNamecontrol) then


End Sub
 
Sorry for the previous post. I hit the wrong key and sent it long before I
was ready.

I will get more info available and resend in a little while.

Mr B
askdoctoraccess dot com
 
Well, let's try this again. Sorry abou all that.

First, you will be adding VBA code to the BeforeUpdate event the First Name
control and the Last Name control.

I will assume that you already know how to get to where you can add code to
these events of your controls. (If not, let me know I will assist.)

When you are in the VB editor with the code window for your form open, make
sure that at the very top of the code you have this:

Option Compare Database
Option Explicit

Next, locat the Tools menu option and select the "References" option. From
the dialog box presented, locate the "Microsoft DAO 3.6 Object Library" item
and place a check mark in the box at the left of this entry. (If you have a
different version number use the one available.)

Next, add the following lines just below the Option Explicit statement:

Dim strMsg As String
Dim vbResponse
Dim strFirstName As String
Dim strLastName As String

Function CheckForNames(LastName As String, FirstName As String) As Boolean
Dim tmpRs As DAO.Recordset
Dim strSql As String
Dim varRecCnt As Long
Dim cntr

strSql = "SELECT Count(tblClients.ClientID) AS CountOfClientID " _
& "FROM tblClients " _
& "WHERE (((tblClients.FirstName)='" & FirstName & "') " _
& "AND ((tblClients.LastName)='" & LastName & "'));"
Set tmpRs = CurrentDb.OpenRecordset(strSql)
varRecCnt = tmpRs.Fields("CountOfClientID").Value
tmpRs.Close
Set tmpRs = Nothing
If varRecCnt > 0 Then
CheckForNames = True 'this name already exists
Else
CheckForNames = False
End If
End Function

Now, in the BeforeUpdate event for the First Name control place this code:

If Not IsNull(Me.txtFirstName) And _
Not IsNull(Me.txtLastName) Then
strFirstName = Me.txtFirstName
strLastName = Me.txtLastName
If CheckForNames(strLastName, strFirstName) = True Then
strMsg = "The name you entered already exists. " _
& "Do you want to continue?"
vbResponse = MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbCritical, _
"Name Exists!")
If vbResponse = vbYes Then
Cancel = True
Me.txtLastName.Undo
End If
End If
End If

Now place the followin code in the BeforeUpdate enent of the Last Name
control:

If Not IsNull(Me.txtFirstName) And _
Not IsNull(Me.txtLastName) Then
strFirstName = Me.txtFirstName
strLastName = Me.txtLastName
If CheckForNames(strLastName, strFirstName) = True Then
strMsg = "The name you entered already exists. " _
& "Do you want to continue and use this name?"
vbResponse = MsgBox(strMsg, vbYesNo + vbDefaultButton2 + vbCritical, _
"Name Exists!")
If vbResponse = vbNo Then
Cancel = True
Me.txtLastName.Undo
End If
End If
End If

Now test your data entry. When a duplicate for both the first and last names
is entered, you should see the message telling you that that name already
exists and asking if you want to continue and use this name.

Good luck with your project.
 
Back
Top