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.
--
HTH
Mr B
askdoctoraccess dot com
"Mr B" wrote:
> 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
>
>
> "Sarah" wrote:
>
> > Yes, I am entering into a form.
> >
> > "Mr B" wrote:
> >
> > > 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
> > >
> > >
> > > "Sarah" wrote:
> > >
> > > > 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....
|
Similar Threads
