Error message about duplicate values

How to resolve the following error message?

'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.'

We have 2 tables, one for the main form and one for the subform. They a relationship to one another.

What should or needs to be changed in the tables??? Or is there something else that needs to be changed?

Thanks for any assistance!

Post your answer or comment

comments powered by Disqus
I am about to loose it......
For weeks on end I have worked on adapting parts of northwind to an old existing database. After spending months trying to import an excel spreadsheet, removing invoice, inventory and other worthless parts; I am now struggling to just enter a freaking order. This programs is going to cause some property damage if I dont get some guidance. Help!

Have tables
Principals (Suppliers)

All have primary keys - on auto number - indexed no duplicates

I have a form
Order details - which I use to enter information/Orders.
Form is sourced on Orders Tbl
The form has a main section which asigns a new order ID
Sets status
Has Combo box for customers, employees, and text box for email addy and order date.

There is a sub form which is currently sourced on Orders TBL (though I have tired any and every possible version)
Sub form has text boxes for order amount, comm earned and statues
There is a combo box for Principals (Suppliers).

The issue.
When I enter a new record and try to save or close, I am given error message that duplicate values in the index, primary key or relationship. I have deleted relationships, changed primary keys, removed primary keys, etc....... Still get the message.

You can reopen through use of this form a record and change any field, IE Customer, employee, principal (Supplier) etc and it will save with out error.

What is going on? All of the indexs should be unique auto numbered, and dups not allowed, though I have tried to change this field to no avail. Help, guidance or a bullet for computer would be much appericated.

We’re using a single table to record daily data – the date field is set as the pk so only one record per date is allowed.
If the user enters a date (when creating a new record) that already exists in the table what is the best way to
a) avoid the Access error message about duplicate values
b) allows the user to enter an alternative date for the new record?

how can i customise access messages forexample when a user enteres a dublicate value in product id with pk it gives me an error message as

duplicate value

how can i customise error message. inshort if the value is already in the pk and i enter it again it should look up that value and then give me message like this

"The value you entereed is already in Database. Please enter Unique value"

We’re using a single table to record daily data – the date field is set as the pk so only one record per date is allowed.
If the user enters a date (when creating a new record) that already exists in the table
what is the best way to;
a) avoid the Access error message about duplicate values,
b) allows the user to enter an alternative date for the new record?

Dear All,

I have a table with a compound key
I have a form with a button that adds new records each time pressed

	DoCmd.GoToRecord , , acNewRec
Me!sfr_LineManID.Form!ParticipantID = Me.user
Me!sfr_LineManID.Form!StatusAlloc = "Pending approval"

If I want to add duplicate values data is added then when I want to move to the next record I get the standard Access error message about index, etc so I have to hit Esc to revert the new record
My question is: how can I set up the code so that if duplicate is added I get a custom msgbox and the new record is not added? (without pressing Esc)
Many thanks

I am getting the error in a grid which has combobox.
This combobox allows only to select unique values from the list.
Whenever i selects the same value in another combo which is already selected in the grid, its shows the popup with the below message and makes us not to click anywhere else in the application.

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries, and try again".

And when i delete the duplicate value selected in combobox it shows the popup with this message "Index or primary key cannot contain NULL value".

I simply wants this :
Whenever user selects duplicate value, message must be displayed and user comes out from that combo.

Please help urgently.

Returning novice user

I need to prove the principle of DLookUp in a small example database before using it elsewhere.

I have set up two tables.

contains two fields of standing data
(alphanumeric, aannnn where a = alphabetic and n = numeric character)

containing a text string

contains two fields
where the user has a choice from a drop down list populated from tbl_Name field Number (this part is working!)

where the DLookUp should retrieve the Name value in tbl_Name that matches the number the user chose in tlkp_LocName field LocNumber

IncorrectDLookUp code
Code: SELECT DLookUp([tbl_Name]![Name],[tlkp_Test]![LocNumber],[tbl_Name]![Number]) AS Expr1; I have tried many sources of guidance, help and browsed this forum, but have to confess myself beaten. Various attempts have resulted in a request for parameters, error messages about incorrectly defined data sources or blank refusal to work.
I'd be really grateful for your help so that I can make progress with the work that depends on this.

I am using Access 2010, and have come across this problem before which miraculously solved itself, but now it's come up again. The db is about 800MB in size, and the table I am using has just over 1000 records in it.

I have a list of samples, each with a unique id. When I go to add a new sample to the list, it tells me it can't do it as it would create duplicate values in the index, primary key, or relationship. I know for certain it is not a duplicate number, and I can put in completely random values and it always gives the same message. I normally add values to the table using an append query, but whether I use the query or try to type manually, it won't let me add the field. I have also removed the primary key from the sample number field, and it still gives the same message.

I am wondering if it is a relationship issue, I use the table in a query but it has worked fine for months and so I don't know why it would all of a sudden stop working now. If it is a relationship issue I wouldn't know how to start looking for it though.

Any help would be appreciated.

Happy Friday All!

My issue: I have a DLookup-After Update event on a continuous subform that will not allow me to edit the values and continue to the next row. I can edit the record persay but when attempting to move on to the next row in the subform to pull another record, it tries to save the current record as a new record to the table (duplicating the value) rather than simply updating the existing information. It then generates an error about duplicate values.

What the form does: The main form act as a manifest. Each row of my subform has a property item, to which belong to separate individuals. The manifest has a date indicating what date the items were sent out along with other individuals property. For example

Main Form Data:
Date: 1/4/13
Sent By : Steve

Subform Data:
BagNum: 77878, DateIn 1/2/13, PropertyTypeID 1, DetentionID 28999, PropertyID 33
BagNum: 77879, DateIn 1/1/13, PropertyTypeID 2, DetentionID 32998, PropertyID 34
BagNum: 77881, DateIn 12/1/12, PropertyTypeID 1, DetentionID 31959, PropertyID 35
BagNum: 77876, DateIn 1/1/13, PropertyTypeID 1, DetentionID 38919, PropertyID 39

*bold indicates text boxes

I have a main form [frmPropertyTransfer] with record source from a table [tblPropertyManifest] with a continuous subform [SubfrmPropertyTransfer] with a record source from a select query [qryPropertyTransfer]. The main form is a manifest for items being shipped out where the Parent to Child relationship is:

frmPropertyTransfer – PropertyManifestID
SubfrmProeprtyTransfer – PropertyManifest_fk

On my subform, I have an After Update event:

	Private Sub BagNum_AfterUpdate()
'Control Source = Nz(DLookup("Control Source", "my table", "[Control Source after update value] = _
''" & [Control Source after upate value] & "'"))
Me.DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
End Sub

I haven’t had luck with a previous post last month and attempting to explain the situation again.

Thank you in advance!

Is it possible for me to bypass the error message #3604 'duplicate key was ignored'. When this error message is thrown it stops processing the subsequent stored procedures so just want it by-passed and not displayed to screen.


I have two primary keys and when duplicates are entered in the form, it is not allowed. I would like to display a custom error message that gives the user the option to go back and change the information they entered, or to be taken to the form with the duplicate information. So far my code is doing half of this. The Cancel option works fine, but when I press OK to be taken to the duplicate record, I am just taken back to the first record. Here's my code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim strWhere As String
Dim rst As DAO.Recordset

On Error GoTo HandleErrors

Select Case DataErr
Case 3022 'Duplicate record
If MsgBox("This record already exists. Click OK to go to the existing record, or Cancel to change your entry", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "MfrPartNumbers_MfrPartNumber ='" & Me.MfrPartNumbers_MfrPartNumber & "' AND MfrPartNumbers_Mfr" = " & MfrPartNumbers_Mfr & "
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
End With
Response = acDataErrContinue
Response = acDataErrContinue
End If
Case Else
End Select

Exit Sub

Select Case Err.Number
Case Else
MsgBox Err.Description & " (" & Err.Number & ")"
End Select
Resume ExitHere
End Sub

Thank you,

This is a rather complicated thing to explain but I will try.
I have a database that has two main tables... one table is to hold data temporarily "untill it is doouble checked" the other table is for the "permanent storage of the data. I use an append query to move the data.
The form I use to input the data uses a query that gets the date from the last record created and puts it in the calander picker (ActiveXCtl)
The form also uses a query to get the employee Number from the last record created and puts it into the Employee Number combo box...these queries draw from the "temp table"
The idea being that the info from the last record will automatically be in place for the next record (The users have to fill in many occurances of the form that contain the same date and several in sequence that contain the same Date and same Employee Number.)
The problem occurs when the "temp Table" is empty. My queries come up empty and I get error messages about saving null values in required fields.
How can I get around these errors.
The form works but you have to click "end" in response to the run time errors.
I hope that this message is somewhat coherent. I barely am.


I am designing a "User Registration" form in which I have 5 textboxes and 1 command button:

Text Boxes:

Command Button:

Now the problem is, I want if any duplicate 'username' is going to be registered then VBA should display an error message like "Username already exists."

Kindly help me out.

I have a table with about 3600 keys, numbered from 1 to 3600. When I try to enter a new record, the autonumber starts in the 20's. I get an error message about duplicate keys, and I have to cancel. If I try again the autonumber goes up by one, but it will take hours to do this with approximately 3600 tries to get past the highest exisiting key number. Is there a way to make Access reset this?

THis table is linked to a many-to-many table and I can't start over or I will lose all my relationships.

Any ideas?

Hi Guys

I have a form in which there are diff controls. What I want to do is when the user enters duplicate barcode in the textbox then an error message showing duplicate value should appear. I can't apply primary key on the table field barcode as in the table there is autonumber field present which is unique. I have wriiten the following code on the textbox lost focus event and I have used dlookup function to check for duplicate values but its not working .

	Private Sub Text17_LostFocus()
Dim Same As String
If Same = DLookup("Barcodevalue", "[Completed_table]", "Barcodevalue = Text17") Then
MsgBox "Duplicate Entry of Barcode is not allowed"
Exit Sub
sSQL = "Insert into Completed_table(Date1,Location,Department,Barcodevalue,Signature) values (#" & Format(Me.Text4.Value,
"mm-dd-yy") & "#,'" & Me.Combo12.Value & "','" & Me.Combo15.Value & "','" & Me.Text17.Value & "','No')"
   DoCmd.SetWarnings False
   DoCmd.RunSQL sSQL
end if
end sub

Thanks in advance for your help.



the following error message appears when trying to access my database. I can't seem to get into it at all, not even by trying to import the data tables over into a new database. the following message appears:

"The database has been converted from a prior version of Microsoft Access by using the DAO Compact Database method instead of Convert Database Command on the Tools Menu. This has left the database in a partially converted state. If you have a copy of the original format use the convert Database on the Tools menu to convert it. If the original database is no longer available, create a new database and import your tables and queries to preserve your data. Your other database applications can't be recorded''..

the copy and the back up wont let me in either, this just suddenly happened.

Please help!

Thanks in advance

hi guys,

search forums but cant find exact answer.

i have a db that manages details of properties/addresses. on my data entry form (frmBoards) when i enter an address name i want it to bring up an error message afterupdate/on change to tell me if this address already exists. if it does exist i want it to take me to that entry in another form for editing entries (frmBoardsEdit).

any help would be appreciated

thanks, james

I have a form that captures info on a case BUT if the case (record) already exits and the user wants to add services I need to add a P to the end of the case # - which is the primary key - and paste append. How do I bring up a custom message box when there is a record present that asks if they want to add services to this record instead of the canned Access message about duplicate values etc. If yes, copy certain fields of this record and paste append to the table with case #+P, open a form to capture service info. If no, erase the fields on the form and close the message box.
Thanks for you help!

My data is like that:

I open the "Input" form
there 2 textboxes
search1 => Field name from Table
value1 => value from the table depend on search1 name

it is a filter.
And, this form has "Input_sub" form, because I want to do filter.

// this case is work well
search1: Vendor Name
value1: Amy
//Do Again
search1: Date
value1: 8/31/2005
//Do Again
search1: Vendor Name
value1: Jimmy

// This case is Not OK, has error
// this one is OK
search1: Date
value1: 8/31/2005
// Do Again, Error come out
search1: Vendor Name
value1: Amy
//Error message appear
"The value you entered isn't valid for this field, For Example, you may have entered text in a numeric field or a number that is larger than the Fieldsize setting permits"

Please give me the direction to solve this problem. I tried many ways, it still be there. I ever change the Datatype of date to be Text of the "Input" Table, then it works, but I want to use datatype of Date to be Date/Time.

Please help.

Thanks. Thanks a lot.

I'm getting an error message which says 'your microsoft access database or project contains a missing or broken reference to file dao2535.tlb version 3.5, to ensure your database works properly, you must fix this reference.' If I click OK enough times i am still able to use the database but does anyone know how I can fix this?


I have a form that works fine when accessed on it's own.

I have placed this as a subform within a larger form. Now whenever a new entry is added it repeats the same autonumber as on the last entry and then I get a barrell load of error messages about duplicating my primary key...

Any ideas on what i have done wrong?

Hi Guys,

Is there a way I can recreate the small search box at the bottom of my form within the actual form? (I'm on MS Access 2007)

The reason...
I have 2 tables. Table A has contact details, Table B has Call Logs, so..

Table A fields
Phone Number

Table B Fields
Date of Call
Details of Call

I have created a form which uses Table A as the main form with Table B as a sub-form so that I can view all call logs against a particular ClientID.

This works great, until I type another clientID number in the form then I get an error message about duplicating data. I get the feeling it thinks I want to change the call log data to a different clientID.

Now... when I use the little search box at the bottom of the form to type in the ClientID, it simultaneously fills the number in the Client ID textbox in my form, only this time it actually brings the new client records, I get no error. Which is why I would like to re-create that box somehow.

Any help greatly appreciated!!!!

I have a form which is based on a query which contains 5 tables in a one-to-one relationship (tblHeader, tblPartA, tblTrimester1, tblTrimester2, tblTrimester3). In the query there is a relationship from CaseNbr of tblHeader to CaseNbr in each of the other tables. The form contains a tab control and there is a page for entering data for each table (other than tblHeader, which has a separate form). tblDrugs is a "many" side table, and another page of the tab control contains a subform based on tblDrugs. This was all working fine for a few days. Now the following happens:

The user enters data into the first 4 pages of the form. (Each page has code to take the user directly from the last field on one page to the first field on the next page.) Page 5 (containing the subform for tblDrugs) comes up automatically, but without setting the focus on the first entry field (a combobox). When the user clicks on the arrow of the combobox, she gets the message about "the record could not be added to the table because it would create duplicate values in the index, primary key, or relationship". Not only does this prevent entries into this subform, but also the tblTrimester2 and tblTrimester3 data (from pages 3 and 4 of the form) did not get saved.

I can't think of what I may have changed that would have caused this. I did notice that tblPartA and tblTrimester1 have a record with CaseNbr 0. Should I delete it? How could this have been created? Could it be related to these problems?

Another thing, it looks like it works for existing records that did not have this problem originally. I guess these were records that were entered before this started happening. But if you go back into the records that got this error it still occurs.

I appreciate any help. This needs to be fixed ASAP.

Dear all,

I would like to ask a question about Execute method in DAO Database object and I would appreciate if you could kindly give me a hand. I have an update SQL query operating on a table with a single numerical column. The query multiplies by 2 each even value. I tested on oracle the following SQL query and I observed that it does the job pretty well:

	UPDATE mytesttable SET numval = (numval * 2)
WHERE (MOD(numval,2) = 0);

Now I want to run the very same query with VBA by calling the Execute method of DAO.Database object. Here is my code:

	Public Sub dbExecteCmdExample()
    Dim myDatabase As DAO.Database
    Dim strSQL As String
    Set myDatabase = CurrentDb
    strSQL = "UPDATE mytesttable SET numval = (numval * 2) WHERE ((numval MOD 2) = 0)"
    myDatabase.Execute strSQL, dbFailOnError
    Set myDatabase = Nothing
End Sub

However, when I run the above code, I receive the following error message:
Runtime Error: 3061: Two few parameters, expected 1.

I have been googling for a while for this error message on different forums, yet I haven't managed to deal with this problem.

Any idea?

Where is the error in my program?

Thanks in advance,

Kind Regards,

Not finding an answer? Try a Google search.