Help Checking For Duplicate Entries

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

View Replies


ADVERTISEMENT

Checking For Duplicate Values

May 10, 2005

Hi,

I have a form in a database for our products (doors). I have a button that duplicates one of these records (all new products are based on standard template records).

When this record is created a new product number is produced by the system which was the number of the original (template) record plus the customer enquiry number.

I want to be able to warn the user if they are trying to save this newly created record with a product number that already exists so that they can alter the number to be unique. It will also warn them if they re-number an existing record to another already existing product number. Since the field is defined as unique, at present the system says absolutely nothing and just doesn't save the newly created record which seems is a touch annoying!

I have tried several things with dlookup, dcount arguments but can't seem to get anything to work.

Hopefully someone here can point me in the right direction?

Thanks in advance

John

View 2 Replies View Related

Checking For Duplicate Numbers

Jan 31, 2006

I have a form in which new equipment is added. I want to be able to check when the ID number is added (first field on the form) that there is not a duplicate ID number. If I wait till the end of the form and save, then Access tells me that there is a duplicate present but by then I have spent time putting in all the rest of the data.

If have added this code in the before update section for the relevent field, but it doesn't seem to work. Can someone please help clarify or suggest an easier way

Dim IntIDCheck as integer
Dim Response

IntIDCheck = DCount("[EquipmentID]","Equipment Inventory","[EquipmentID] =" & Me.EquipmentID)

if IntIDCheck > 0 then
Response = MsgBox ("Duplicate Present")
Me.EquipmentID.SetFocus
Cancel = True
Exit Sub
End if

Thanks

View 2 Replies View Related

Modules & VBA :: Checking For Duplicate Records

Jun 29, 2013

What would be the Access 2007 VBA code for checking for duplicate records?

View 14 Replies View Related

Modules & VBA :: Duplicate Checking - Multiple Criteria

Jun 15, 2013

I'm using Access 2013. I want to check duplicate data. I have a form where from insert data into table but before insertion I want check multiple criteria with form. How can I do this

Field are:
BatchID
BillNum
CIH
IH

back end form code is:

Private Sub cmdCheck_Click()
Dim criteria As String
criteria = "[BatchID]=" & Me.cboBatchID & " AND [BillNum]=" & Me.txtBillNum & " AND [CIH]=" & Me.txtCIH & " AND [IH]=" & Me.txtIH & ""

[Code] ....

Finally I'm getting this error: Run Time Error 3075 Syntax Error Missing Operator in qry Expr.

View 3 Replies View Related

Forms :: Checking For A Filtered Duplicate Value On A Form Before Closing

Apr 11, 2014

Below is some code that I'm struggling with and I get an error message "Type Mismatch".

Description: The database contains multiple projects and the subset of each project is a release. The "PublishedNumber" can't be a duplicate within a release, but it can be a duplicate within the table where the data is stored. There can however be multiple occurrences of 0.

Here's my code:

Dim stLinkCriteriaRelease As String
Dim stLinkPublishedNumber As String
Dim DuplicateNumber As Variant

stLinkCriteriaRelease = "[AssociatedRelease]=" & [AssociatedRelease]
stLinkPublishedNumber = "[PublishedNumber]=" & [PublishedNumber]

[Code] ....

View 3 Replies View Related

Duplicate Entries

Mar 30, 2006

hello,
Currently i have a database that holds information about computers. Each computer has a Service tag and is associated to a person. I need to be able to prevent duplicate service tags from being entered. I am using indexing and don't allow duplicates from the table design view. But, if a duplicate service tag is indeed entered, i want to inform the user who that service tag currently belongs too without having to search through the DB manually. I need then to give the user the option to delete the duplicated record they just entered or change the service tag that they just entered. Thanks.
Jared

View 2 Replies View Related

Prevent Duplicate Entries

Oct 25, 2006

I've set up a booking system and am looking to stop the system recording double bookings.

I've 3 fields, RoomID, Date and Time that need to be searched, however I can't just simply set the properties to No Duplicates (as this would mean a booking cannot be made on the same day at 9:00 and 12:00 for example).

How can I do this?

View 9 Replies View Related

Preventing Duplicate Entries.

Nov 26, 2006

Hi again, thanks to all for the help given so far.

Is there anyway (other than making that field a primary key) to prevent the same value being entered into a different record?

E.g.

If under 'Name' Radion has already been written, I want an error message coming up saying there is already a record with radion.

View 3 Replies View Related

Finding Duplicate Entries

Oct 12, 2006

I am running a query to find out duplicate in two fields in one table. Postcode and Address Line 1. Can anyone tell me the formula for only picking up the first 6 charaters in the Address Line 1 field??? I cant remember how to do it!

HELP!

View 2 Replies View Related

Remove Duplicate Entries

Mar 8, 2007

I am using information pulled from a separate database to create a new database for mail merger purposes. I have created delete queries that go out and delete specific records from the new database to help us get the proper list before setting up the mail merge. However, now I am stuck. We have customers that have multiple accounts with us that are qualified for this mailing, yet each customer needs to receive only one mailing. The table looks like this essentially. Table 1id statCode acct custNumber* addy-etc. *This is the field in which we can find exact duplicates. What I need to do is remove the duplicate entries and leave at least one of the entries within the table. It does in fact need to remove the whole record, but as I said leave at least one of the records behind. Can someone please help me to find a solution to this. I guess I should add that I am not very good with Access and I use it very sparingly. I assume that the above can be accomplished using a SQL statement. Any help will be greatly appreciated. LR**EDIT** I also needed to add that I do need to exclude 000 00 0000 from being removed because of duplication.

View 6 Replies View Related

Duplicate Entries And Queries?

Jul 30, 2007

Is there a way to find and remove duplicate entries in tables by using queries.....if so, how specifically?

View 1 Replies View Related

Preventing Duplicate Entries

Aug 2, 2006

Hi all,
I am currently using this code in the before Update event of the Surname Textbox on a form. The first Textbox is FirstName.
The second one is Surname.

************************************************** **

If (Not IsNull(DLookup("[FirstName]", _
"Employee", "[FirstName] ='" _
& Me!FirstName & "'"))) And (Not IsNull(DLookup("[Surname]", _
"Employee", "[Surname] ='" _
& Me!Surname & "'"))) Then
MsgBox "Someone already exists with the same name! Please check for duplicates", vbCritical, "IPDMS"
Me.Undo
End If

************************************************** **

However once the user has entered the surname and tries to save the record the MsgBox is coming up.
Basically what is happening is the first name is coming up as the duplicate entry only.
I need to prevent the whole name (first name and surname together) from being duplicated.

Thanks if you can help me.

View 3 Replies View Related

Stopping Duplicate Value Entries

Oct 17, 2006

I have a booking form database
With a date field.
I want users to be warned when they try to enter a date that already exists in the database in order to avoid double booking:confused: .....

all suggestions appreciated:)

View 4 Replies View Related

Get Rid Of Duplicate Entries In A Combo Box?

Sep 10, 2004

hey,

sorry to bother everyone again, this is such a great forum, and ive gotten soo much help here.

i have a customer table, containing custRef, custName, drawerRef, Address, Phone, Fax

now it contains site addresses so the custName can be the same for quite a number of entries. no the combo box in my form obviously shows all entries for custName which means the same name repeated sometimes 6 or 7 times, as i only need to select one, is there a way without looking to different tables etc to just show unique entries. i dont want to list them all in another table because i dont know them all yet, so i want it to lookup the list as i enter them.

cheers

Baz

View 3 Replies View Related

Error 3022 Duplicate Entries

Aug 6, 2006

Hi,
I have a rather large database that was built in Access '97 and recently converted to 2003. I have a couple problems now. I get an error message about not being able to update the form because it would create a duplicate value in the primary key, index, or relationship. (Error 3022) I didn't build this particular database so I'm not totally sure why somethings are done the way they are. I've spent a long time going through everything to try to understand the relationships and I've asked lots of people at work to look at it, but no one seems to understand why it's doing what it's doing. The Access Help information has helped me understand some things better, but not this problem. I've gone through every table and removed all primary keys and set all indexes to allow duplicates. Then, when I was looking at the relationships and object dependancies, I think everything looks to be in order. However, when i add info directly to the table instead of using the form, the form is not updated with the new info. the table keeps the information, but isn't communicating with the form and i suspect reverse is true which is where the error is coming from. But how do I detect that for sure and how can I fix it with out starting over? I've gone through and added data to one field in the form at a time to try and narrow down what field is causing the problem, but no matter what i get the same error. Is there a way to find out exactly what field is causing the error? Thanks for any help you can offer!

Alicia

View 3 Replies View Related

Complex Duplicate Entries - How To Flag Up Etc.

Jan 8, 2007

Hi everyone,

I'm aware of the wizard in MS Access that creates a new table containing duplicated entries that have been entered under one column, however, my problem is slightly more complex...

A contractor of mine has recorded information from CCTV surveys of sewers (not very glamorous I know!) in a Microsoft Access database. If you can imagine - the camera can be pushed along a pipe from both ends. Sometimes, the survey is abandoned in one direction and then repeated along the same length of pipe but from the other direction. The database contains (amongst other columns) a "start manhole" reference and a "end manhole" reference.
eg:

16014

As you can see, the information contained by both entries will be the same (as they are the same length of pipe). Therefore, I was wondering how I could get MS Access to:
1. Recognise these 2 entries as duplicates
2. Create a table that hides the duplicated entry with a shorter "SectionLength" value.

Many thanks in advance.

View 1 Replies View Related

Inner Join Returns Duplicate Entries?

Aug 29, 2007

I'm using a pretty simple inner join on two tables but the some of the same results are showing up twice. If I just remove the inner join all the results are uniquely present. For some reason the inner join creates about 10% more records that are all duplicate entries. Any idea what might cause this? :confused:SELECT products.product_nameFROM products INNER JOIN categories ON products.category = categories.category_nameWHERE ((products.X)=True);

View 2 Replies View Related

Combo Boxes And Duplicate Entries

Aug 17, 2005

Hello

I am trying to get a 2nd combo working from the first .

I have a table called Keystage (tblKeystage) and I have also named my 1st combo cboKeystage. then by following the vba below I created another 6 tables with corresponding names and combo's.

Private Sub cboKeystage_AfterUpdate()

On Error Resume Next

Select Case cboKeystage.Value
Case "Foundation"
cboYear.RowSource = "tblFoundation"
Case "KS1"
cboYear.RowSource = "tblKSOne"
Case "KS2"
cboYear.RowSource = "tblKSTwo"
Case "KS3"
cboYear.RowSource = "tblKSThree"
Case "KS4"
cboYear.RowSource = "tblKSFour"
Case "Post16"
cboYear.RowSource = "tblPost"

End Select

End Sub

The combo boxes both work, brilliant. My problem is that when I enter another record and make a selection from the 1st combo box, it changes all the records.

Could somebody please help.
Thanks
Wong

View 3 Replies View Related

How Do I Remove Duplicate Entries From A Table ?

May 19, 2007

Hi all,

I have imported a table from Excel. The table contains a long list of names and there are quite a lot of duplicates.
How do I remove the duplicates so I can import the clean list into a value list in Access ?

Thanks in advance !

View 2 Replies View Related

Duplicate Field Entries Ecords

Aug 16, 2007

I have a table that contains doctor information including their license number. This came from a worksheet that tracked referrals from last season. I need a query that will show me only those record that have a unique MD License number. In other words I only need to see the 5-600 doctors out of the 2420 referrals in the table. Once I get the query of unique MD's I can create a report for the salesmen to use to contact them for the upcoming year.
I know this is probably simple but I can't find the solution.
Thanks in advance.

View 3 Replies View Related

Queries :: Number Of Duplicate Entries?

Jun 25, 2014

I'm trying to use access to identify how many times an indiviual appears in my database. I've got a large file which gives me all referrals, each referral comes with two identifiers, one unique to the person, another unique to the referral. This means that the person unique number may be repeated multiple times (if a person has more than one referral) but the referral number would be different each time.

I've run a query to identify the duplicates, i.e. the records where the person unique identifier is repeated. What I am trying to do is work out now how many people have been referred twice, how many three times etc. The original query looks something like this (but on a much bigger scale).

Person Referral
1 A
1 B
1 C
2 D
2 E
3 F
3 G
3 H

What I'm after is a query that will tell me that two people were referred three times (1 and 3) and that one was referred twice (2) and so on.

I'm on Access 2010 if it makes any difference.

View 6 Replies View Related

Forms :: Preventing Duplicate Entries

Mar 7, 2014

I am attempting to make a system where the lifespan of baskets in a production process can be tracked. In order to do this I am looking to create a form to input when a new basket is introduced.Each basket has a 'disc' associated with a number on (BasketNumber), however these discs are reused once the basket breaks which is making it more difficult.

I have a form (Add_New_Basket) which feeds into a table (Active_Basket); what I am trying to do is make sure that a new basket cannot be introduced with the same disc number as an active basket. The way I am trying to determine which baskets are active is that I have an end date, that is filled in once the basket has been scrapped.

Therefore I am trying to set my Add_New_Basket form to restrict me entering a new record with BasketNumber 999, if there is already a BasketNumber 999 in the table with 'EndDate' null. If there is BasketNumber 999 and EndDate is dd/mm/yyyy then it can create the entry.My form Add_New_Basket has fields ID (which is hidden), BasketNumber, StartDate (=Date()).My table has fields: ID, BasketNumber, StartDate, EndDate

View 3 Replies View Related

General :: Duplicate Entries In Combo Box

Apr 21, 2013

I've got a combo box that lists duplicates. I want the dupes removed.

I went to the Query Builder for my Combo Box and set Unique Values from "No" to "Yes" - Saved, then re-opened the form. It still lists dupes.

I went back into Query Builder and unchecked the box for the Patient_ID (Autonumber) field. I saved it, then viewed the Datasheet for the Query, and it correctly lists the data I want WITHOUT dupes.

However, when I go to Form View, the combo box lists several blank entries..

The entries come back when I re-enable the Autonumber field in the Query.

This is what I currently have...

SELECT DISTINCT Patients.FullName FROM Patients ORDER BY Patients.FullName;

View 4 Replies View Related

How Can I Check For Duplicate Entries After Leaving A Field?

Aug 30, 2005

I'm pretty new to Access, so please overlook any stupid questions :)
I have a data entry form that has one field that cannot be a duplicate. So, in the table I have it Indexed with No duplicates.
The problem is that the form doesn't notify the user that they have entered duplicate information until they close the form. So the 10 minutes they spent filling it out is wasted.
So, after consulting various posts, I did a BeforeUpdate event with the following code:
(the field that must be unique is called "LS Number", in the form "Q and D" based on the table "Q and D Database")


Private Sub LS_Number_BeforeUpdate(Cancel As Integer)
On Error GoTo LS_Number_BeforeUpdate_Err

If (DLookup("[LS Number]", "Q and D Database", "[LS Number]=Forms![Q and D]![LS Number]")) Then
MsgBox "The LS Number you entered already exists. Enter a unique LS Number", vbInformation, "Duplicate LS Number"

End If

LS_Number_BeforeUpdate_Exit:
Exit Sub

LS_Number_BeforeUpdate_Err:
MsgBox Error$
Resume LS_Number_BeforeUpdate_Exit

End Sub


When I test this by entering a duplicate LS Number, it gives me the error "Type Mismatch." Unfortunately, I have no idea what this means, nor do I know how to fix it.

Please help, my head hurts!

View 3 Replies View Related

If Statement To Check For Duplicate Entries With List Box

May 17, 2006

I have a problem where in a table called tblPatientLanguage
that contains three fields

PatientLanguageID
PatientID
LanguageID

a patient who is identified by PatientID can speak several languages
identified by the LanguageID.

The problem is with my current form and code


For Each varSelected In Me!lstLanguage.ItemsSelected
rs.AddNew
rs("PatientID") = [Forms]![frmPatientDemographics]![PatientID]
rs("LanguageID") = Me!lstLanguage.ItemData(varSelected)
rs.Update
Next varSelected



when the user click on the command button called cmdOK it will put the
data in the table "tblPatientLanguage" and corresponding fields. without
checking to see if there is already a duplicate languageID that corresponds to the
same patientID in the table

This has lead to duplicates in the table

example
PatientID = 9

if I look in the table I will see 7 entries that say

PatientID = 9 and LangaugeID = 7

I want to prevent this from happening with and if statement
I could make both the PatientID and LanguageID primary keys
this would prevent this from happening. But I would rather do it in
the VBA code? This way in will move thorugh the selections without poping an error and the person inputing the date. Any ideas sorry for being long winded. I attached the
database.

PS. If looking at the database you have to use form frmPatientDemographics and click on the language link

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved