Run Time Error 3022 when there are no duplicates

[Adam Tippelt]

Hi,

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:

Private Sub cmdSave_Click()
Dim db As dao.Database
Dim rst As dao.Recordset
Dim strFileName As String
 
Set db = CurrentDb
Set rst = db.OpenRecordset("ResultsScreenshots")
 
    'Check a file path was entered.
    If getFileName(txtPath.Value) <> "" Then 'See modGlobals for getFileName function.
        'Next check that the file exists.
        If fileExists(txtPath.Value, getFileName(txtPath.Value)) Then 'See modGlobals for fileExists function.
            rst.AddNew 'Prepare recordset for a new record.
            If ReadBLOB(txtPath.Value, rst, "Screenshot") = 1 Then 'Stores the binary data see modBlob for ReadBLOB function.
                rst!FileName = getFileName(txtPath.Value) 'Stores the filename.
                rst!FileExtension = getFileExtension(txtPath.Value) 'Stores the file extension.
                rst!UploadedBy = GUsername 'Stores the user who uploaded the file.
                rst.Update 'Update the record.
                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:

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!

[Adam Tippelt]

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.

[NeoPa]

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.

[Adam Tippelt]

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.

[TheSmileyCoder]

Do you have a key column for your table? Do you have any other indexes specified?

[Adam Tippelt]

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!

[NeoPa]

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?

[Adam Tippelt]

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!)

[NeoPa]

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.

[Adam Tippelt]

Ah right that makes more sense.

Thanks for the info, I shall have to do some more reading!

[NeoPa]

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.