Error 3022 - Duplicate Key/Index
Mar 1, 2007
Just when I thought I had my problem licked...
I created a subforum with an updateable query, and this works. I can view the results of the query, modify them and add new records.
However I shortly realized that once I deleted a record (either by some VBA code, or by SQL) that I could not re-add the same record through the subform.
I've made sure that the record is deleted, so there is no duplication of the key.
I've made sure that Index is Duplicates OK, so there is no duplication issues there.
What am I missing?
View Replies
ADVERTISEMENT
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
Jun 27, 2011
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code:
Private Sub ItemCode_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'")
If Not IsNull(Answer) Then
MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True
Me.ItemCode.Undo
Else:
End If
End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
View 3 Replies
View Related
Aug 3, 2006
Okay, I'm kind of stumped here.I have a subform that has a button that sends a user to a "sibling" subform on another tab page, pass some information to ensure they are adding more details to the same records rather than creating two separate record.First time I programmed it, I got an error 3022 (keys cannot have duplicate values). I checked the query of the sibling subform and saw that the ID is from the one side table. I changed it so many table's foreign key is used. Second try, I got an error 3341 (there isn't a matching key in one side table).After some thinking, it also occured to me that I had set the query this way to allow addition of new record which wouldn't be possible if I had the query pulling the many side key, not the one side key.How do I get the subform to accept the ID that is being passed and create a new record using that ID?:confused:
View 5 Replies
View Related
Feb 14, 2007
Hi there
I am using a select query meanz no modification at all but still getting the error 3022(The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022))
Anybody have any clue?
Thanks
Danny
View 7 Replies
View Related
Jul 31, 2005
My table has an auto generated key as a string.
So on the offchance that two clients are trying to autogenerate a key at exactly the same time, I'm trying to trap the 3022 error raised when a duplicate key is entered so that I can repeat the operation with a goto.
I'm raising the error with requery like this:
DoCmd.Save acForm, Me.Name 'next line triggers 3022
DoCmd.Requery
After I realised that the normal handler wouldn't trap it I got the database from this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=77529&highlight=3022
But it doesn't seem to work for me:
http://www.imagedump.com/index.cgi?pick=get&tp=288631
Anyone?
View 12 Replies
View Related
Mar 27, 2008
I dont use linked tables very often, and when I always seem to run into the same problem.
I have two tables. The pri table has an index key defined as an autoindex, with no duplicates. The sub-table is linked to the pri
table key in a many-to-one relationship. The linked field in the subtable is defined as a long integer non-keyed field.
All is okay, until I attemp to add a SECOND record. At that point I get a 3022 error, stating I cant do something because I am attempting to create a duplicate key.
I would love to fix it, nut I am not sure exactly what the problem is that needs to be fixed. The pri key would not SEEM to be getting duplicated, and the subtable linked field is not keyed. Hmmm, I guess I need a steer here please.
eatc7402
View 1 Replies
View Related
Dec 1, 2014
I have an Access 2010 database with a form that allows me to input data which is saved into two tables (i.e. Orders and OrderDetails). I use a simple select query to bind the form to the tables.
When the form loads it asks for an order number, which it uses to pull details from the Orders table and auto-fill some fields on the form (these fields are not enabled as they are predefined, such as the order date).
So, when the order number is entered my VBA code does a Form.Refresh, which then breaks the code with the Run-time error 3022 - "The changes you requested to the table were not successful yada yada.." (I'm sure you all know the one)...
I have checked that there are no duplicated keys in my tables, I have checked that what should be indexed, is indexed. I have done a compact and repair on the database, and even created a new database and imported all the tables (used new data). Still get the same issue.
View 8 Replies
View Related
Dec 18, 2006
hi guys i was wondering if you can help me this is my code: i have a main form with this code, this form contains a subform linked by the All_PricingID
Set rst = CurrentDb.OpenRecordset("tblAll_Pricing") 'main table
' adding data to the table
rst.AddNew
' Main table
rst!All_PricingID = Me.txtPricingID 'Main table pk
rst!MainContract_ID = Me.cmbMainContract 'combo box in parent form
rst!ItemNumber = Me.txtItem 'Main form text
rst.Update
'sub Table
Set rst2 = CurrentDb.OpenRecordset("tblPricing") 'sub table
For varItem = 0 To Me.lstsubContracts.ListCount - 1 'this is a list in the main form
'--- loop through all the items in the list box and create a new row in the subform for each subcontract in the listbox lstSubcontracts.
rst2.AddNew
rst2!ID = Me.All_PricingID 'sub table foreign key
rst2!SubContractID = Me.lstsubContracts.Column(0, varItem) 'sub table
rst2.Update
Next varItem
'--- close the tables
rst.Close
rst2.Close
Set rst = Nothing
Set rst2 = Nothing
the subform appears correctly with the rows i wanted added but i need the user to be able to edit a column in the subform for the rows just created (my form is on datasheet view). but everytime i move to cursor into the subform, i can't even scroll up and down.
i keep getting an error that says :
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)
but when i check my tables tblAll_Pricing and tblPricing , everything is inserted correctly according to my recorset above, do you know why this is happening? and why i am not able to edit my subform. my subform allowsedits and additions.
help!!
View 2 Replies
View Related
Feb 27, 2006
Hi - it's been a while since I did much access and am trying to help someone out and have got into a pickle! A quick job has turned out to be a lot more complicated! Plus I'm having to use *spit* Acc 2k.
When I go into frmHighCostDrug (from the "add new drug episode" button on the frmPatient) I often get the message "The changes you requested were not successful because they would create duplicate values in the index, primary key, relationship etc etc."
Now, I didn't think I'd forgotten that much. I've got primary keys, values in the table set to Null, relationships set and ref integrity set and cannot for the life of me see why this is happening.
I know though, that one of you will say "oh it's that" and point it out and I'm going to feel completely stupid, but at the moment, I don't mind! I really can't figure it out.
I've attached db - it's in Acc2k and I tried to do a copy Acc'97 for those who don't have it but it caused no end of problems. Came up with messages that this form contains data that Access doesn't support!!!! ??? :confused: If I can sort it I will.
If anyone's got any ideas, I'd be v grateful!
Tnx
Ally
View 9 Replies
View Related
Apr 1, 2015
I am trying to add a "T" in front of an article number and write this new number to a table.Access cannot find the new Article strArt because it does not exist. When I put a msgbox after "if .nomatch then" it correctly shows the message. Yet it refuses to create the new record because of a key/index violation. Apart from an index on the recordkey, it has an index on Artno, both indexes do not (obviously) want duplicate values.
Here is the code:
Private Sub btnCopyT_Click()
Dim db As Database
Dim rstArt As Recordset
Dim strArt As String
Set db = CurrentDb
strArt = "T" & Me.frmNomenclatuurSubfrm.Form.Artno
[code]....
View 8 Replies
View Related
Nov 29, 2005
This is a real mind bender.
I am running a2k. I am merging two databases. table1 is in the backend database. table2 is linked to the second database.
I run an append query to add table2 entries to table1. The append fails for 96 recs saying key violation. Turns out I can rerun the append if I drop the zip code field.
I then try to manually change the zip code and receive the duplicate values in index, primary key, or relationship.
I ran a compact & repair the databases, still no go. I can't edit the value of the zip code. I import the table to a new database. Still can't change the zip code. I drop MANY of the indexes. Even the index on the zip code field. Run compact&repair. still getting the index message. I even re-imported to a new db again. Still same error message.
I may start again by importing JUST these two tables to a new blank db and see if I get the same issues. I'll keep you posted...but maybe there is someone out there who has seen this error before.
fyi-if I enter a new record, the autonumber field correctly increments to the next available autonumber field. (I say this because of another thread on this matter w/ autonumber fields and bug in A2003)
View 1 Replies
View Related
Mar 2, 2005
Hello, I keep getting this error when opening up a database. What can I check to find and fix the problem? TIA
View 3 Replies
View Related
Apr 22, 2012
I'm trying to record a payment, but I keep getting this error upon completion of my form:
Index or primary key cannot contain a null value.
I've checked the data types and relationships and they seem to be fine.
Here are links to my Access files (2007 & 2003)for your reference:
[URL]
View 2 Replies
View Related
Jun 16, 2005
I want the recordnumber displayed on a continuous form. The code below works with the command button, but not afterinsert.
Here's the code: (function GetPosition() is the control source)
Private Sub Command28_Click()
numbers
End Sub
Private Sub Form_AfterInsert()
numbers
End Sub
Private Function getPosition() As String
If Me.RecordsetClone.RecordCount > counter Then
counter = counter + 1
getPosition = "#" & counter
Else
getPosition = "#new"
End If
End Function
Private Sub numbers()
counter = 0
Me.Requery
End Sub
So it should do something like this:
#1 [ctrl 1]
#2 [ctrl 1]
#3 [ctrl 1]
#new [ctrl 1]
But called from after insert only the records on the screen get updated unless I scroll up, so simplified it looks like this:
----------------- top of subform after scrolling up
#3 [ctrl 1]
----------------- top of subform before scrolling up
#1 [ctrl 1]
#2 [ctrl 1]
#new [ctrl 1]
Where else can I put it or how can I modify the code to make it work?
Aidan
View 2 Replies
View Related
Aug 22, 2014
Using an Access 2003 format database, opening in Access 2007...When I try to open my database I get two errors and it will not open.ID is not an index in this table.ParentId is not an index in this table.
I get the error when it opens with autoexec and when I bypass autoexec. I have a master copy of the database that I tried to link to the first database to import tables but I still get the error.
View 4 Replies
View Related
May 16, 2015
I have a Composite Index to prevent duplicates I get the error message. How can I trap this?
I resolved it with this PHP Code:
'Trap Error.
Dim DataErr As Integer
Dim Response As Integer
Dim Message As String
If DataErr = 3022 Then 'Duplicate value entered
Response = acDataErrContinue
End If
View 2 Replies
View Related
Oct 8, 2013
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
View 3 Replies
View Related
Aug 11, 2006
I got this "AOIndex is not an index in this table" error with Access 2000 after replicating a database and storing it in the same network folder. I am not able to open my database now, i am afraid 2days work on my forms is lost. I usually have backup, but the last backup i have is 2 days ago i have made alot of changes to 2 FORMS after that :(
Please anybody have suggestions on how to retrive my forms??? I am really concerned about the forms. I have backup for data and for the reports. Please help me?
View 8 Replies
View Related
Mar 2, 2007
I am working with an unbound form, with fields populated by an updateable query.
User may edit the fields, then attempt to save changes, done with a simple recordset based on the same query.
I trap 3022 upon Update.
I would like to know if there is a good, clean way to set the focus back to the field which caused the 3022.
Any help much appreciated.
Edit: Or, a good way to determine which field is the offending one. Thanks.
View 10 Replies
View Related
Jun 14, 2005
Hi there
I have the following field in my access project.
Sno
Name
PortNo
Location
Speed
Package
In the above field i had set Sno is PRIMARY KEY. The problem is after entering all field, then only it will show the duplicate Sno error. What I want is, if Sno is duplicate, when i type SNo , successively i have to get the SNo is duplicate.
Help me to solve this issue
thanx in advance
by
yasi
View 2 Replies
View Related
Jul 6, 2005
hi,
the below scrip was taken from another forum member.
i have put this in the Forms / error VBA
but i still a little lost
i have set 3 fields to be indexed not to allow duplicate information (Forename, Surename, DOB)
i was then advised to error trap so it would highlight that i have enterd a duplicate record on typing in the DOB (As that as third feild to be entered)
so i then used the below script, however this doesnt highlight that it is a duplicate it just doesnt let me go on to the next record, the only time the error message is highlighted is when i go into design mode.
anyideas on where to go from here.
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Tis MerID alredy exist!"
Exit Sub
End If
If DataErr = 2169 Then
Response = acDataErrContinue
MsgBox "Tis record will not be saved!"
Exit Sub
End If
End Sub
View 3 Replies
View Related
Dec 21, 2006
I'm trying to make a simple DB that we can enter our time for working on projects.
So far I have a Form with the employees name and the date in it. Then below that is a subform with the project number, name, client name, and hours.
I want to use this whole "sheet" for one days worth of work for 1 employee. New sheets will be used for each day.
When I try to make a new sheet I get an error that it cant change the table because it would be creating a duplicate entry.
How do I solve this?
Also, should it be difficult to be doing what I'm trying to do?
View 2 Replies
View Related
Aug 17, 2005
hi everyone,
i have a form that i'm trying to create a button to duplicate the record. i'm using the control wizard which produces the following code:
Private Sub DuplicateRecord_Click()
On Error GoTo Err_DuplicateRecord_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
Exit_DuplicateRecord_Click:
Exit Sub
Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click
End Sub
When i try to execute the code via a comand button, i get an error and the duplicate record operation does not occur. one thing i noticed is that i have a lot of lookup fields (i.e. FK's to lookup tables one-to-many relationships) in underlying table being populated by the form. i've created combo boxes on the form to populate the FK's in the underlying table. the error that occurs when trying to use the above code produces a "paste errors" table and in that paste errors table instead of the bound column values from the combo boxes (i.e. PK values from the lookup tables) it shows the display values from the combo boxes. i'm not sure if this has do with anything, but i couldn't figure out why it was doing this.
does anyone have any ideas how i could get this duplicate record procedure to work?
many thanks for any help or suggestions.
View 1 Replies
View Related
Mar 29, 2006
I have a table with one primary key (ClientID) which is an autonumber (cannot be duplicated), and another field which is a number field which is set to a random 6 digits (also cannot be duplicated) whenever a new client record is created.
Now, the main database is on a desktop, and my partner and i are connected to this main database through our laptops whenever we're in the office. For the last 6 months, I had only the ClientID in the form (from the Client Table), and everything would work fine whenever we created a new record in the Form, even if we created a new record at the same time on our personal laptops, the numbers would automatically be increased by one w/ no error.
I just added the other field now, the regular 6 digit random number field, and sometimes (only sometimes) when we both have a different client and start a new record, we get error messages that says we're creating duplicates. When that error message comes up, one of us has to close down the form completely, and reopen and start a new record again. Not sure why this is happening. Here is the error message.
http://members.shaw.ca/hgkma/errormessage.jpg
Any help appreciated.!!
View 14 Replies
View Related
Nov 29, 2006
I have a form - see attached image.
The first text box is called Job_No. When a number is entered here how can I check that the number doesn't already exist when the user tabs to the next box. Then if it does exist display a custom message to the effect "This already exists" and not the Access default duplicate error message.
Thanks
Michael
View 1 Replies
View Related
