473,899 Members | 4,321 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Run Time Error 3022 when there are no duplicates

137 New Member

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
10 18570
32,584 Recognized Expert Moderator MVP
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

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

Similar topics

by: Lyn | last post by:
Hi, this has been driving me nuts. I'm on Office 2003 SP1, Win XP SP1. I am opening a form with a number of subforms based on various tables. The subforms are populated via the main form's Current event. I am using similar code to open each of the subforms successfully -- except for this one case which gives the above error. I have simplified the SQL to its most basic level. It runs just fine as a query. I just can't make it work...
by: ColinWard | last post by:
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!
by: Jono | last post by:
Hello, I've been getting this message when closing excel (not necessarily when closing the workbook by itself, but when closing Excel and the workbook at the same time): -------------------------- Run-time error '1004': Method of 'Worksheets' of object '_Global' failed -------------------------- When I hit Debug, it highlights the code marked below:
by: Jan | last post by:
Hi: When I searched the newsgroup for this problem, I saw two or three instances of the question being asked, but it was never answered. Not too promising, but here goes: I have a form with four subforms, and bit of code that cycles through the data in the subform (bound to a local temp table) and writes it to a table on the server. This code has run at the client with no problems for over a year.
by: Melancholy Man | last post by:
I am getting a Run-time error 3349 when importing a .txt file into the front end of a DB (DB is split). If I use the same specs on the back-end I do not get the error. Is there some file limitation to what can be imported into a front end (linked) table? Note: I am ultimately trying to do the import through VBA in a form on the front end. Any explanation or help greatly appreciated.
by: accessvbanewbie | last post by:
All I am trying to do is based on month value of date typed in 2 unbound form text boxes assign it to summer,winter or mixseason. I am getting a runtime erro 13 from the statement below. When I debug both mStart and strJan etc(months) appear as string. I am not sure where I am going wrong. Please help Private Sub txtSeason_Click()
by: Sep410 | last post by:
Hi all, Here is my code: Private Sub Command12_Click() Dim strSql As String strSql = "Delete from tbl_city where CityId=" & Val(Me!txtEmail.Text) & ";" Set cn = CurrentProject.Connection cn.Execute strSql Debug.Print "MyTableView created"
by: grippy10 | last post by:
I'm trying to delete sheets 1,2,4,5 and make sheet 3 the active one. I get the error on " Sheets(1).Select" and "Sheets(Array(1, 2, 4, 5)).Select". I get a run time error when I try and run the following macro. Help? zFolder = zPath & "\Kitty*" Application.DisplayAlerts = False Sheets(1).Select ActiveWindow.ScrollWorkbookTabs Position:=xlFirst ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets(Array(1,...
by: bullfrog83 | last post by:
On the OnClick event of a command button I have code that copies a parent record and all of its child records in the same table using recordsets: Private Sub cmdCopy_Click() On Error GoTo Error_Handler Dim lngMatricIdFrom As Long Dim lngMatricIdTo As Long Dim lngDiscId As Long Dim lngDiscClassId As Long
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.