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!
10 18556
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 32,572
Recognized Expert Moderator MVP
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.
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.
Do you have a key column for your table? Do you have any other indexes specified?
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 32,572
Recognized Expert Moderator MVP
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 straightforward ly. 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?
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 32,572
Recognized Expert Moderator MVP
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.
Ah right that makes more sense.
Thanks for the info, I shall have to do some more reading!
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: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
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...
| |