Ms Access Error Code 3022
Contents |
a Question Need help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's
Compacting An Frx Database
quick & easy. Run Time Error 3022 when there are no duplicates 100+ the changes you requested to the table were not successful because they would create duplicate P: 137 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: Expand|Select|Wrap|Line Numbers PrivateSubcmdSave_Click() DimdbAsdao.Database DimrstAsdao.Recordset DimstrFileNameAsString Setdb=CurrentDb Setrst=db.OpenRecordset("ResultsScreenshots") 'Checkafilepathwasentered. IfgetFileName(txtPath.Value)<>""Then'SeemodGlobalsforgetFileNamefunction. 'Nextcheckthatthefileexists. IffileExists(txtPath.Value,getFileName(txtPath.Value))Then'SeemodGlobalsforfileExistsfunction. rst.AddNew'Preparerecordsetforanewrecord. IfReadBLOB(txtPath.Value,rst,"Screenshot")=1Then'StoresthebinarydataseemodBlobforReadBLOBfunction. rst!FileName=getFileName(txtPath.Value)'Storesthefilename. rst!FileExtension=getFileExtension(txtPath.Value)'Storesthefileextension. rst!UploadedBy=GUsername'Storestheuserwhouploadedthefile. rst.Update'Updatetherecord. MsgBox"Fileuploaded.",vbInformation'Confirmtotheuserthatthefilewasuploaded. 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 rst.Update'Updatetherecord. 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
Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rating: Display Modes 07-08-2010, 06:29 AM #1 Michael.K Registered User Join Date: Oct 2007 Posts: 5 Thanks: 0 Thanked 0 Times in 0 Posts Run-time error 3022 with .AddNew Hi all, I have a problem with something that I thought would be very simple and trivial. I have a table with three fields: an Autonumber field, a date field and a text field. I also have a form with a command button to add a new record. When I add the record for the first time https://bytes.com/topic/access/answers/911527-run-time-error-3022-when-there-no-duplicates it works fine. If/when I try add another record(s) it gives me run-time error 3022 (no duplicates allowed). Here is my code: Code: Private Sub cmdGetInMod_Click() Dim intModID As Integer Dim strTable as string strTable = "tbl_TEST" intModID = getInModID(strTable) end Sub Private Function getInModID(strTable As String) As Integer Dim tempDB As DAO.Database Dim tempRST As DAO.Recordset Dim strSQL As String Dim strUsername As String Dim strDate As String strUsername = Environ("username") strDate = Date strSQL = "SELECT http://www.access-programmers.co.uk/forums/showthread.php?t=195626 * FROM " & strTable & ";" Set tempDB = CurrentDb() Set tempRST = tempDB.OpenRecordset(strSQL, dbOpenDynaset) With tempRST .AddNew .Fields(1).Value = strDate .Fields(2).Value = strUsername .Update '<= this is what gives error message .bookmark = .LastModified getInModID = .Fields(0).Value .Close End With Set tempRST = Nothing Set tempDB = Nothing End Function Other words, I am trying to add a new record and get its autonumber ID. When I open my table I can only see one line. However if I delete that line and add another "first" record I can see that the Autonumber field is actually changed, so my code is working (just not the way I want it to... ). Any help or advice will be appreciated. Regards, Michael Michael.K View Public Profile Find More Posts by Michael.K 07-08-2010, 06:41 AM #2 vbaInet AWF VIP Join Date: Jan 2010 Location: U.K. Posts: 26,373 Thanks: 0 Thanked 2,403 Times in 2,372 Posts Re: Run-time error 3022 with .AddNew Hello there, 1. How many fields do you have as the Primary Keys? 2. Did you set the INDEXED property of some fields to Yes (No Duplicates)? vbaInet View Public Profile Find More Posts by vbaInet 07-08-2010, 09:31 AM #3 Michael.K Registered User Join Date: Oct 2007 Posts: 5 Thanks: 0 Thanked 0 Times in 0 Posts Re: Run-time e
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be http://www.tek-tips.com/viewthread.cfm?qid=1545986 Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed ms access What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft: Access Forms Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft: Access Forms Forum Trap the 3022 error thread702-1545986 Forum Search FAQs Links ms access error MVPs Trap the 3022 error Trap the 3022 error annie52 (TechnicalUser) (OP) 30 Apr 09 16:03 I can't figure out how to trap the 3022 error that's generated when I enter a duplicate record and then try to leave the record via the navigation buttons.None of the form's events seem to catch this error. RE: Trap the 3022 error kjv1611 (IS/IT - Management) 30 Apr 09 16:08 How have you tried?Are you trying to capture the code in VBA with an error-handler routine? --"If to err is human, then I must be some kind of human!" -Me RE: Trap the 3022 error annie52 (TechnicalUser) (OP) 30 Apr 09 16:27 Hi, kjv1611.I'm not sure I know what you mean by "an error-handler routine".Here's what I have in the form's OnError event:On Error GoTo Err_Form_ErrorExit_Form_Error:Exit SubErr_Form_Error:If Err.Number = 3022 ThenMsgBox "That requisition number already exists."Response = acDataErrContinueElseMsgBox "Error #" & Err.Number & "" & Err.DescriptionEnd IfResume Exit_Form_ErrorIt doesn't trap this error though. RE: Trap the 3022 error missinglinq (P