Ms Access Vba Error 3022
a Question Need help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's quick & easy. Run Time Error 3022 when there are no duplicates 100+ 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,
Need help? Post your question and get tips & solutions from a community of 418,595 IT Pros & Developers. It's quick & easy. Custom error message for Runtime error 3022 P: n/a g_man I am trying trap Runtime error 3022 (duplicates) in the click event of a command button that closes the form. I have code in the Form_Error event that does a good job of providing a more meaningful error message than the default. It works in every situation except when the user clicks the close button. I am using Me.Dirty=False to force a save but if there are duplicates I just get the standard Runtime 3022 error message. I am wondering why the Form_Error https://bytes.com/topic/access/answers/911527-run-time-error-3022-when-there-no-duplicates event is not triggered. Here are the two relevant subs: First the Form_Error Sub... Private Sub Form_Error(DataErr As Integer, Response As Integer) Dim strMsg As String Dim Group As String Dim Selection As Integer Const conDuplicateKey = 3022 Group = DLookup("[Name]", "tblGroups", "[GroupID] = " _ & Me.GroupID) If DataErr = conDuplicateKey Then ' Don't show built-in error messages Response = acDataErrContinue strMsg = "You have already entered hours for " & Group & vbCr ' Show a https://bytes.com/topic/access/answers/562625-custom-error-message-runtime-error-3022-a custom error message MsgBox strMsg, vbOKOnly, "Duplicate Value" If Response = vbOK Then 'Me.Undo cboGroupID.SetFocus End If End If End Sub And the Close button's Click event Private Sub cmdClose_Click() Me.Dirty = False DoCmd.Close acForm, Me.Name, acSaveYes End Sub I tried putting a On Error GoTo statement with a select case that tested the Err.Number and then had similar code to Form_Error sub above to produce a custom error message, but the standard error message was being produced as soon as the Me.Dirty=False statement was reached. and the On Error Go To ErrorHandler was ignored. I obvously don't understand the sequence of events enough. Can anyone Help me out? By the way, The Form allows entry of Volunteer Hours worked at a seniors home by various church groups. Nov 11 '06 #1 Post Reply Share this Question 8 Replies P: n/a Ed Robichaud Why not use the BeforeUpdate event of the form and/or the entry field for the primary key? This would alert users to a duplicate before they continue to enter all the other info, rather than after the fact. BTW, you can set the form's properties to remove the Close box. -Ed "g_man"
Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts http://www.access-programmers.co.uk/forums/showthread.php?t=195626 Go to Page... Thread Tools Rating: Display Modes 07-08-2010, 06:29 http://p2p.wrox.com/access/37362-handling-no-duplicates-index-error.html 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 ms access 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 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 ms access vba 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 * 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
Unanswered Topics Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access Handling the no duplicates index error User Name Remember Me? Password Reminder Password Register Register | FAQ | Members List | Calendar | Today's Posts | Search Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. Search Forums Show Threads Show Posts Advanced Search Find All Thanked Posts Go to Page... Welcome to the p2p.wrox.com Forums. You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free . Page 1 of 2 1 2 > Thread Tools Display Modes #1 (permalink) January 18th, 2006, 01:53 PM Enoch Authorized User Join Date: Dec 2005 Location: , , . Posts: 14 Thanks: 0 Thanked 0 Times in 0 Posts Handling the no duplicates index error I know how to handle errors and provide custom error messages for errors generated within a sub or function. However, if the error is thrown by a no duplicates index, I don't know where or how to catch the error in order to provide a more user friendly error message. Thanks in advance for your help Enoch __________________ Enoch #2 (permalink) January 18th, 2006, 02:42 PM Bob Bedell Friend of Wrox Join Date: Jun 2003 Location: , , USA. Posts: 1,093 Thanks: 1 Thanked 10 Times in 9 Posts Don't know exactly how you're doing the insert, but the Jet error you're interested in is 3022. Code: Sub DuplicateValueError(strNewValue As String) On Error GoTo Err_Handler Dim db As DAO.Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset) With rst .AddNew !Field1 = strNewValue .Update '<--- Error 3022 thrown here. End With rst.Close db.Close Set rst = Nothing Set db = Nothing Exit Sub Exit_Here: rst.Close db.Close Set rst = Nothing Set db = Nothing Exit Sub Err_Handler: If Err.Number = 3022 Then strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf strMsg = strMsg & "Please enter a unique value for this field." MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _ vbCritical + vbOKOnly, "Duplicate Value Error" Else MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description End If Resume Exit_Here End Sub HTH, Bob #