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!

[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.