469,323 Members | 1,560 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Run Time Error 3022 when there are no duplicates

137 100+

This one has been bugging me for a while, and I can't find a solution to get round it.

The error message is:

Run-time 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. 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.

Where this occurs:

I have a form with a BLOB file upload section. The code for this is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. Dim db As dao.Database
  3. Dim rst As dao.Recordset
  4. Dim strFileName As String
  6. Set db = CurrentDb
  7. Set rst = db.OpenRecordset("ResultsScreenshots")
  9.     'Check a file path was entered.
  10.     If getFileName(txtPath.Value) <> "" Then 'See modGlobals for getFileName function.
  11.         'Next check that the file exists.
  12.         If fileExists(txtPath.Value, getFileName(txtPath.Value)) Then 'See modGlobals for fileExists function.
  13.             rst.AddNew 'Prepare recordset for a new record.
  14.             If ReadBLOB(txtPath.Value, rst, "Screenshot") = 1 Then 'Stores the binary data see modBlob for ReadBLOB function.
  15.                 rst!FileName = getFileName(txtPath.Value) 'Stores the filename.
  16.                 rst!FileExtension = getFileExtension(txtPath.Value) 'Stores the file extension.
  17.                 rst!UploadedBy = GUsername 'Stores the user who uploaded the file.
  18.                 rst.Update 'Update the record.
  19.                 MsgBox "File uploaded.", vbInformation 'Confirm to the user that the file was uploaded.
I've set the form up so that the user can upload the file, but if they quit without saving, the file is deleted. However, next time the form is loaded, when a user tries to upload a file, the run time error pops up. The exact line that it errors on is:

Expand|Select|Wrap|Line Numbers
  1. rst.Update 'Update the record.
The only explanation that I can think of is that by deleting the previous file upload, the system is getting fooled into thinking that the ID number used by that deleted file is available, when it isn't. So it thinks that it's duplicating a file, when the previous 'copy' of that file was actually deleted. Even so, I can't understand why it's not just going onto the next available ID number. However the recordset is re-opened upon clicking the save button, so this surely should flag remove the file from the recordset?

Currently the only way to upload another file is to shut down Access and re-open it again, though this is because it compacts on close and the deleted files actually ARE deleted, so the ID number becomes available again (as it is the next number that follows the Maximum ID actually in use).

Can anyone offer any solutions to either fixing, trapping or bypassing this error?

Many thanks!
Apr 8 '11 #1
10 17451
Adam Tippelt
137 100+
Another thing to add:

The table in question contains only one field that is indexed with no duplicates - it is the ID field which is an AutoNumber field. I have checked all the other fields, and none of them restrict duplicate information.
Apr 8 '11 #2
32,173 Expert Mod 16PB
The only obvious problem I see is that the rst.AddNew line is not within the same conditional level of the code as the rst.Update line. Not that you need more than that of course.
Apr 9 '11 #3
Adam Tippelt
137 100+
Thanks for the response NeoPa. Unfortunately, changing the rst.Update line so that it falls on the same conditional level doesn't change anything.

Right now the only way I can get round the problem is to put an On Error Resume Next. Not something I wanted to do, as it's like brushing the dirt under the rug, but I'll have to do that and come back to it when I have more time.
Apr 11 '11 #4
2,321 Expert Mod 2GB
Do you have a key column for your table? Do you have any other indexes specified?
Apr 11 '11 #5
Adam Tippelt
137 100+
Yes there's a unique key field that is indexed with no duplicates.

I just looked again and found that the foreign key field was also indexed but to allow duplicates. I've just turned the indexing off for that column, along with the Resume Next, and it seems to be working without erroring.

Just turned the indexing back on for that column - that now works as well...

If I wasn't confused before...I certainly am now!
Apr 12 '11 #6
32,173 Expert Mod 16PB
That happens sometimes Adam. It's hard to go back and repeat previous tests after the design has been changed too.

I've just noticed though, that you seem to be using Recordset processing within your form to handle updating the table. Access is designed to handle this for you relatively straightforwardly. I would suggest that trying to take that offline and handle this in your own code is not a sensible approach. It's quite a common mistake for those experienced in other areas of development, but who don't yet think the way one needs to for Access. Does that make sense?
Apr 12 '11 #7
Adam Tippelt
137 100+
That's just the thing though - once I hit a brick wall there, I went onto a different section to get away from it and have a think (I often find a problem slowly solves itself in the back of my head of I go onto something else). I didn't change anymore coding for it, aside from putting in the On Error Resume Next line, and yet when I checked it again, it worked. :S

Heck I even logged into a backup version I took a few days ago and it even worked on that! Very strange.

Half of what you said made sense...
Are you suggesting that by using Recordset I am "trying to take it offline and handle with my own code" or is that a warning to continue using Recordset instead of trying another approach?

I'm only going on what I know/have learnt so far/have googled, so I am constantly learning as I go! (Especially thanks to people on this forum!)
Apr 12 '11 #8
32,173 Expert Mod 16PB
The former Adam. The approach you are using is unnecessarily complicated as you are trying to redo Access's job. It already handles record manipulation in forms (quite well). You would be better advised learning about how to do it with Access, rather than finding alternative approaches (that can work, but leave you forever going the complicated way around).

Does that make sense?

PS. I do understand that changing your approach half-way is not without a certain level of inconvenience. Long term though, I expect it is an approach that will benefit you.
Apr 12 '11 #9
Adam Tippelt
137 100+
Ah right that makes more sense.

Thanks for the info, I shall have to do some more reading!
Apr 13 '11 #10
32,173 Expert Mod 16PB
There are various tutorials around, but I found the wizards available give a good insight into what is available for forms. That might be a good place to start.

BTW. I'm certainly not averse to using VBA with forms and doing all sorts of clever things, just redoing what's already provided for you. That still leaves plenty of scope.
Apr 14 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.