Help checking for Duplicate Entries

GohDiamond · Dec 28, 2006

Hello,

I'm new to using VBA for MS Access. May I enlist your help, Please.

I'm working on a human resources database for a group of employees. I'm trying to automate their login process after they have opened the database so that each employee's privacy is respected. Using an initial form, employees are separated into "New User" or "Returning User". My problem concerns setting up the "New User" in the database.

The New User is directed to form "frm_NU". The form has several unbound fields which are later carried to the table "tbl_Employees". Two of the fields on the form are used to create the EmployeeID. Those are Initials [INI] and Phone Extension [EXT]. The EmployeeID will be used in a filter so that only that employee's information is made available to the user (simulated Privacy).

Because the employees are unfamiliar with MS Access and its cryptic error messages, I need to create a message box which will give a plain English warning when a user attempts to use an existing ID to create a new record. [EmployeeID] is a field in the table. it is set as a key and does not allow duplicates.

When a new user enters his/her initials and phone extension I want the new ID to be compared to the table and if found, a message should appear. This is a preemptive strike to prevent the user from getting to the more cryptic MS Access error message.

I've tried using Dlookup and Dcount to try to pass some meaningful data to the If statement variable VarX to trigger the message box; assuming that Varx should be something other than Null or Empty

Dim EmpID As Variant
Dim VarX As Variant

EmpID = [INI] & [EXT]

VarX = DLookup([EmployeeID], "qry_Employees", [EmployeeID] = [EmpID])

If VarX <Is Not Empty> Then
X = MsgBox("Another record already exists by that name", vbOKOnly)
Else
Y = MsgBox("Passed", vbOKOnly)
End If

I keep getting an error at the DLookup formula or DCount formula saying: Run-time error; 2001 You canceled the previous operation. It also appears that the DLookup never looks up anything nor does DCount count anything even though I'm purposely entering duplicate information which exists in "qry_Employees". I was expecting VarX to be either the duplicate ID or a record number or record count or something.

Any ideas or constructive suggestions?

Thanks,

Goh

pbaldy · Dec 28, 2006

Note proper syntax when referring to the variable would be like referring to a form:

http://www.mvps.org/access/general/gen0018.htm

Moniker · Dec 29, 2006

To create your own error message, you use error trapping. At the very top of your login routine (where you're trying to compare to see if an extension/employeeID exists), add something like this:

Code:

Sub YourLoginRoutineName
[COLOR="Red"]On Error GoTo Err_Login[/COLOR]
.
<your normal login code here>
.
<and at the very end of the subroutine>
[COLOR="Red"]Err_Login:
    Select Case Err.Number
        Case <whatever the error number is for primary key violation>
            MsgBox "Your Custom Error Message Here"
        Case <Any other errors you want to trap>
            MsgBox "Your other custom error messages"
        Case Else
            MsgBox Err.Description
    End Select[/COLOR]
    [COLOR="DarkGreen"]'Here, you can use [B]Cancel = True[/B], which will cancel a form opening, like this
[/COLOR]    [COLOR="Red"]Cancel = True[/COLOR]
    [COLOR="DarkGreen"]'Alternatively, you can use Resume <where to resume>.  Look this one up in Access help for the various options.[/COLOR]
End Sub

When you use error trapping like this, any errors that Access encounters are first passed to your custom error handling routine. Note that the "Case Else MsgBox Err.Description" covers any errors you are not specifically capturing. It will display whatever the default message is. However, any error numbers trapped in the CASE <err.number> statements will instead display your custom error message.

~Moniker

GohDiamond · Dec 29, 2006

Thanks to everyone whose input lead me to expand my search and dig harder to find what I'd been looking for.
I wasn't quite up to creating an error handling routine. I'll save that avenue of study for a later date.

It was, afterall, a feeling that the DCount function should provide a simpler solution. I just didn't get it at first. But after looking up "DCount" here in the forums I found a similar problem addressed. Many thanks to KeithG(Registered User) and Matt Greatorex for great advice.
Here's the solution I came up with and want to share with others:

To Review: I'm using 2 unbound fields on a form to create an EmployeeID.
Those fields are: Initials [INI] and Phone Extension [EXT].

I'm trapping the attempted duplication of the EmployeeID upon exiting the [EXT] field.
-------------------------------------------------------------
Private Sub EXT_Exit(Cancel As Integer)
'Set variable to represent the proposed ID'
Dim EmpID As Variant

'Concatenate the 2 unbound fields to become the proposed EmployeeID'

EmpID = [INI] & [EXT]
'Check if the proposed EmployeeID already exists in the table Employees'
'Be especially careful of the Single and Double Quotation marks due to VB syntax for DCount Function'
'Thanks to KeithG post 11/30/2006 and Matt Greatorex post 12/1/06 at http://www.access-programmers.co.uk/forums'
'DCount checks the EmployeeID field of the underlying tbl_Employees to see if any EmployeeID matches the proposed ID represented by the variable EmpID'
'Using the IF statement when the response is true then the first condition is executed, if false then the "Else" second condition is executed'

If DCount("[EmployeeID]", "[tbl_Employees]", "[EmployeeID]='" & [EmpID] & "'") Then

X = MsgBox("Another record already exists by that name", vbOKOnly)
Else
Y = MsgBox("Passed", vbOKOnly)
End If
End Sub
-------------------------------------------------------------

This is a simple trap. The next process to execute after finding out that a duplicate exists is my next challenge.
Thanks everyone.

Goh,
-------------------------------------------------------------
Database Development Philosophy:
How do you eat an Elephant?
One byte at a time

dayna · Mar 8, 2008

similar solution - check for dups before appending records

I was struggling with a similar problem, and thanks to this thread and the others it refers to, I was able to devise a solution. Since my code works slightly differently, I thought I would post it in case someone else can benefit from it in the future.

Background: This code lives in the AfterUpdate event of a command button on an unbound main form called Attendance. The purpose of this form is to:

1.) collect class and date criteria from the teacher via two unbound controls ([Combo0] and [Text2] respectively),

2.) append an attendance record to the Attendance table for each student in the class by running an append query that receives criteria from the main form controls, then

3.)display the newly appended information in a subform that is based on a query of the Attendance table. The query receives class and date criteria from the controls on the main form. The subform accepts edits and saves changes to the Attendance table.

In order to accomplish this, I had to check for duplicates before appending the records, and like Goh, I wanted to protect my users from the crytic MS error messages associated with primary key violations. This is what I finally ended up with:

Code:

Private Sub Command7_Click()

[I]‘Declare count of duplicate records as variable.’[/I]
Dim DupCount As Variant

[I]‘Declare and define append query.’[/I]
Dim AppendQry As String
AppendQry = "Attendance Roster"

[I]‘Count the number of records in the attendance table that have data in the [Class ID] and [Date Attended] fields that match the data entered into both of the controls on the main form.  Note the different syntax used for numeric and time/date fields.  See the link posted above by (the always wonderful!) pbaldy (XOXOXO!) for an excellent reference on this topic.’[/I]
DupCount = DCount("[Class ID]", "[Attendance]", "[Class ID] = " & Me.Combo0 _
& " AND [Date Attended] = #" & Me.Text2 & "#")

[I]‘If DupCount returns a value other than zero, then display an error message to the user, clear the date control, and set the cursor there so the user can re-enter a new date.  Make sure the subform remains invisible until the duplicate test is passed. (See below for further explanation on this.)’[/I]
If DupCount > 0 Then
MsgBox "You have already entered an attendance record for this class on this date.", vbOKOnly
Me.Text2.Value = Null
Me.Text2.SetFocus
Me.Attendance_Subform.Visible = No

[I]‘If DupCount returns a value of 0, then no attendance records exist for this class on this day.  Run an append query to insert a record for each student on the specified class on the specified day into the Attendance table.’[/I]
Else
DoCmd.OpenQuery AppendQry, acNormal, acEdit

[I]‘In case the form is not newly opened, and the user is changing class or date criteria to enter records for a different class or day, requery the attendance subform and make it visible to the user.   Send the cursor to the [Contact Hours] field of the subform to make data entry more convenient.  End the procedure.[/I] 
Me.Attendance_Subform.Requery
Me.Attendance_Subform.Visible = True
Me.Attendance_Subform.Setfocus
End If
End Sub

*Note* The visible property of my subform (which displays student’s names and contact hours) is set to default to “No.” This property is only changed if the data in the main form controls pass the duplicate test. I have another button called “Save” that makes the subform invisible again and clears the main form controls so new data can be entered. By turning this visible property of the subform on and off while this procedure is running, I hope to avoid confusion on the part of the user.

Many thanks to all of you merciful souls who take pity us noobs. This forum is truly the best Access resource I’ve found so far!

Help checking for Duplicate Entries

GohDiamond

"Access- Imagineer that!"

pbaldy

Wino Moderator

Moniker

VBA Pro

GohDiamond

"Access- Imagineer that!"

dayna

Registered User.

Similar threads

Users who are viewing this thread