I have created an employee database in MS Access 2003. However, I have now discovered a couple of cases of the same person being entered into the database two or more times (The problem being they have worked for two or more departments over the years).
How can I set up some kind of validation when record is entered that if a person's name and Date of Birth match someone already in the database, a message box or something will pop up and say "This person already seems to be in the database"?
Thanks
5 4312
I have created an employee database in MS Access 2003. However, I have now discovered a couple of cases of the same person being entered into the database two or more times (The problem being they have worked for two or more departments over the years).
How can I set up some kind of validation when record is entered that if a person's name and Date of Birth match someone already in the database, a message box or something will pop up and say "This person already seems to be in the database"?
Thanks
Hi
Perhaps you could adapt this function/code to inclued DOB. - Private Function NameOK() As Boolean
-
Dim NameCount As Integer
-
NameOK = True
-
-
If Surname & "" = "" Or Forename & "" = "" Then Exit Function
-
If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
-
-
NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")
-
-
If NameCount >= 1 And Me.Dirty Then
-
MsgBox "An employee with this name already exists." & vbLf & vbLf & _
-
"One name must be altered.", vbCritical, "Employee Name"
-
NameOK = False
-
If Not Me.NewRecord Then Me.Undo
-
End If
-
-
End Function
You then decide which event to use it in, for instance in the control(s) Exit Event ie - Private Sub Forename_Exit(Cancel As Integer)
-
If Not NameOK Then
-
Cancel = True
-
End If
-
End Sub
??
MTB
Or you could design your database properly and you wouldn't have this problem of data redundancy occuring. You obviously need a minimum of 3 tables - 1 for the employee, 1 for the department, and a bridge table between the 2 with date fields such as Start_Date, End_Date. You need then to identify your business rules such as Each employee may belong to many departments but not during the same date period. Each department can have many employees. This way an employee is only ever entered once but may move between departments and you have a history of their employment.
This will stop the problem you are having.
Checking for a person's DOB and comparing it to a name is not the way to properly do this. Think of an organization like MS - many thousands of employees across the world - i am sure they have people with the same name and DOB - not a good solution.
You design properly to start with you avoid all these problems.
cheers,
Rob
Hi
Perhaps you could adapt this function/code to inclued DOB. - Private Function NameOK() As Boolean
-
Dim NameCount As Integer
-
NameOK = True
-
-
If Surname & "" = "" Or Forename & "" = "" Then Exit Function
-
If Surname = Surname.OldValue And Forename = Forename.OldValue Then Exit Function
-
-
NameCount = DCount("Surname", "tblEmployees", "Surname = """ & Surname & """ AND Forename = """ & Forename & """")
-
-
If NameCount >= 1 And Me.Dirty Then
-
MsgBox "An employee with this name already exists." & vbLf & vbLf & _
-
"One name must be altered.", vbCritical, "Employee Name"
-
NameOK = False
-
If Not Me.NewRecord Then Me.Undo
-
End If
-
-
End Function
You then decide which event to use it in, for instance in the control(s) Exit Event ie - Private Sub Forename_Exit(Cancel As Integer)
-
If Not NameOK Then
-
Cancel = True
-
End If
-
End Sub
??
MTB
Thanks. I'll try this out.
Rob's advice is the best way forward, I'd say. Your employee table should include a unique identifier for each employee and the entry routine for new employees should include a test for date of birth and last name. The only time this wouldn't work is if your organisation employs twins!
Another problem might be mis-spelling of last names - it happens all too often. Here I'd suggest that the add routine returns a list of all people with the same date of birth already entered, showing their full names and departments and having the user confirm that this really is a new entry.
Finally, I'd log the the user details and time when each entry is made and let it be known that this logging takes place. It is a fact that users take a little more care when they know that their actions are subject to scrutiny.
When adding this sort of refinement, though, you need to balance the consequences of duplicate entries against the time taken to build in and maintain such checks.
Rob's advice is the best way forward, I'd say. Your employee table should include a unique identifier for each employee and the entry routine for new employees should include a test for date of birth and last name. The only time this wouldn't work is if your organisation employs twins!
Another problem might be mis-spelling of last names - it happens all too often. Here I'd suggest that the add routine returns a list of all people with the same date of birth already entered, showing their full names and departments and having the user confirm that this really is a new entry.
Finally, I'd log the the user details and time when each entry is made and let it be known that this logging takes place. It is a fact that users take a little more care when they know that their actions are subject to scrutiny.
When adding this sort of refinement, though, you need to balance the consequences of duplicate entries against the time taken to build in and maintain such checks.
Thanks for the user logging suggestion. I will definitely use this.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Catherine Jo Morgan |
last post by:
Can I set it up so that a certain combination of fields can't contain the
same entries, on another record? e.g. a combination of
FirstName/LastName/address? Or FirstName/LastName/phone? Or...
|
by: Lee-Anne Waters via AccessMonster.com |
last post by:
Hello,
just a small problem i'm hoping someone would help me with.
i have a form which adds skills to a person but i want to make sure that the skill is not entered twice.
is there anyway of...
|
by: MP |
last post by:
I need a code in the Before Update event procedure, which prevents to
enter duplicate record in a form.
I have the main formX, which takes values from the table named tblX and
a subformY, which...
|
by: Kathie G via AccessMonster.com |
last post by:
Hello,
I have a delima that I am not sure how to resolve. Records get entered
manually or through an import. The records contain, RecordID, Client ID,
Specimen Date, Visit Date, Client DOB, and...
|
by: dee |
last post by:
I can prevent a duplicate ID from being entered, but can't figure out
how to go to the record with that already existing ID. Could someone
help? Thanks in advance.
Private Sub...
|
by: eazyone |
last post by:
I can prevent a duplicate ID from being entered, but can't figure out how to go to that specific record. I keep getting an error message on:
Me.Bookmark = rsc.Bookmark
Its not opening and...
|
by: bruce24444 |
last post by:
I am designing a database to use a work to assign files to certain people. Form includes Date textbox, File Number textbox, File Type combobox and Assigned To combobox. The form is working fine and...
|
by: teser3 |
last post by:
I have my PHP inserting into Oracle 9i.
But how do I prevent duplicate record entries?
I only have 3 fields in the insert in the action page:
CODE
<?php
$c=OCILogon("scott", "tiger",...
|
by: rjshrader |
last post by:
I have a table (tblStatus) with three fields (CustomerID, StatusType
and StatusDate). I use an unbound form with three text boxes to enter
data into the table when a command button (cmdSave) is...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |