473,327 Members | 2,016 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Runtime Error '2501'

mjoachim
I've reviewed numerous solutions to others with the same error and for some reason I just can't seem to get past my issue... please help!

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If IsNull(Me.EquipNumber) Then
  4.         Do
  5.             InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
  6.                 If InputEq = "" Then
  7.                     If MsgBox("An Equipment Number is required to save." & vbCrLf & _
  8.                     "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
  9.                         DoCmd.RunCommand acCmdCloseWindow
  10.                     End If
  11.                 End If
  12.         Loop Until InputEq <> ""
  13.  
  14.         Me.EquipNumber = InputEq
  15.     End If
  16.  
  17.  
  18.     If IsNull(Me.txtUser) Then
  19.         Me.txtUser = Environ$("Username")
  20.         Me.txtEnterDate = Date
  21.     End If
  22.  
  23.  
  24. End Sub
  25.  
The objective is to simply verify that a required field has been entered prior to saving/closing the form. If the field is null, it prompts the user for a value. If they click OK or cancel, a secondary prompt asks if the record should be deleted. If they select "No", the code properly loops back to the input request, but if they select "Yes", I get the Runtime error 2501 "The runcommand was cancelled".

All I want it to do is close the active form without saving a record.
Feb 25 '16 #1

✓ answered by jforbes

I don't think you can do what you are attempting in the manner you are attempting to do it in Access.

The BeforeUpdate Event provides a last validation check before saving a record. The BeforUpdate Event is nestled into Access' Save Process and provides an option to Abort the Save by setting Cancel to True. So in practice, Access starts to save, sees that there is some code that needs to be ran and runs it, then it checks the value of Cancel and if it hasn't changed it performs the actual Save, then an Endpoint is encountered and control is returned back to the UI. Access is written so that there is only one exit point out of the Save Process, even though there can be some user code run as part of that process, the process needs to finish at the one and only endpoint.

In your code example, you are trying to effectively create a different endpoint with the DoCmd.RunCommand acCmdCloseWindow and Access won't have it. So in the BeforeUpdate Event, you really only have the option to Cancel the Save.

I think you are going to have to split the Validation check and the Cancel of save. ...Maybe by placing a Cancel button on the screen.

4 2488
jforbes
1,107 Expert 1GB
I don't think you can do what you are attempting in the manner you are attempting to do it in Access.

The BeforeUpdate Event provides a last validation check before saving a record. The BeforUpdate Event is nestled into Access' Save Process and provides an option to Abort the Save by setting Cancel to True. So in practice, Access starts to save, sees that there is some code that needs to be ran and runs it, then it checks the value of Cancel and if it hasn't changed it performs the actual Save, then an Endpoint is encountered and control is returned back to the UI. Access is written so that there is only one exit point out of the Save Process, even though there can be some user code run as part of that process, the process needs to finish at the one and only endpoint.

In your code example, you are trying to effectively create a different endpoint with the DoCmd.RunCommand acCmdCloseWindow and Access won't have it. So in the BeforeUpdate Event, you really only have the option to Cancel the Save.

I think you are going to have to split the Validation check and the Cancel of save. ...Maybe by placing a Cancel button on the screen.
Feb 26 '16 #2
jforbes: thanks for that bit of info. Based on what you said, I re-examined my code and moved a few things around so that I was able to use Cancel. The action functions as I would like it to now, but the default Access prompt, "You can't save this record at this time." still pops up prior to actually closing the form. Is there a good way to silence this prompt or to preemptively select yes?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If IsNull(Me.EquipNumber) Then
  4.         Do
  5.             InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
  6.                 If InputEq = "" Then
  7.                     If MsgBox("An Equipment Number is required to save." & vbCrLf & _
  8.                     "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
  9.                         Cancel = True
  10.                         InputEq = "1"
  11.                     End If
  12.                 Else
  13.                     Me.EquipNumber = InputEq
  14.                 End If
  15.         Loop Until InputEq <> ""
  16.  
  17.     End If
  18.  
  19. End Sub
Feb 26 '16 #3
jforbes
1,107 Expert 1GB
You can attempt to put a Me.Undo after Cancel=True to back out the user's changes:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Cancel = True
  3. Me.Undo
  4. InputEq = "1"
  5. ...
I'm not sure this is the best solution, but it may work for you. If you continue to have difficulty in getting Access to do what you want, it may be better to take a step back and look at your approach again. Access has a particular way it goes about letting a user enter data and if your idea for an application differs too much from Access' approach, you'll either need to change your approach to a more Access centric way or you'll want take much more control and use a Form with unbound controls to get the user input and then when the data is fully validated, build up an insert statement and insert the records yourself instead of letting Access do it.

Just wanted to give you fair warning of the path ahead of you and some options.
Feb 26 '16 #4
Thanks again. Adding Undo didn't appear to change anything. I had already started thinking of other approaches as there is always more than one way to accomplish the same task. While reading your comments again, I realized that I had some code elsewhere that was created from a system macro converted to VBA that has essentially the same task, but without the prompt. I was able to borrow that chunk to accomplish exactly what I want.

I'm not going to pretend to fully understand the reason this works, but here is what I was able to do:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If IsNull(Me.EquipNumber) Then
  4.         Do
  5.             InputEq = InputBox("An equipment number is required.  Please enter a value:", "Missing Equipment Number")
  6.                 If InputEq = "" Then
  7.                     If MsgBox("An Equipment Number is required to save." & vbCrLf & _
  8.                     "Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
  9.                         With CodeContextObject
  10.                             On Error Resume Next
  11.                             DoCmd.GoToControl Screen.PreviousControl.Name
  12.                             Err.Clear
  13.                             If (Not .Form.NewRecord) Then
  14.                                 DoCmd.RunCommand acCmdDeleteRecord
  15.                             End If
  16.                             If (.Form.NewRecord And Not .Form.Dirty) Then
  17.                                 Beep
  18.                             End If
  19.                             If (.Form.NewRecord And .Form.Dirty) Then
  20.                                 DoCmd.RunCommand acCmdUndo
  21.                             End If
  22.                             If (.MacroError <> 0) Then
  23.                                 Beep
  24.                                 MsgBox .MacroError.Description, vbOKOnly, ""
  25.                             End If
  26.                         End With
  27.  
  28.                         DoCmd.RunCommand acCmdCloseWindow
  29.                         InputEq = "1"
  30.                     End If
  31.                 Else
  32.                     Me.EquipNumber = InputEq
  33.                 End If
  34.         Loop Until InputEq <> ""
  35.  
  36.     End If
  37.  
  38. End Sub
Thanks again!
Feb 26 '16 #5

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

Similar topics

2
by: ColinWard | last post by:
My previous message was blank so Im trying again. I have a button on a form which opens the Import dialogue box. This works fine except if I click on the "X" to close the form I get Run-Time Error...
1
by: Bob Dydd | last post by:
Hi everyone It's me again. I have an access 2000 database with 12 landscape reports which sometimes have to be FAXED and other times printed, so I have written the following code and put it...
5
by: fearblanco | last post by:
Hello - I am receiving the below error message when attempting to open a report. This database is used by approximately 20 users and only one user is having this problem (even I can't duplicate...
4
by: Keith | last post by:
I have the following code in the On No Data event of a report: **** On Error GoTo err_trap MsgBox "No items matching criteria.", vbInformation, gcApplication Cancel = True err_trap: If...
3
by: robertsheeran | last post by:
Hi all, I have an database created that I currently use on MS Access 02 and now want to use on a different computer with MS Access 03. All the reports and queries run perfect on Access 02 but...
2
by: dkohel | last post by:
What is wrong with the following code? I am trying to filter a form based on the selection of a combobox named cblPRD Dim currentFilter currentFilter = " = '" & Me!cboPRD & "'" Filter =...
3
by: razjafry | last post by:
Hi, I am trying to create a combo box to search record on the base of ID but it gives runtime error message 2501 "the openform action was cancelled" all the times. I posted this question before but...
7
sassy2009
by: sassy2009 | last post by:
Hello, I am running an insert query from xl spreadsheet using the DoCmd.RunSQL to insert values from the spreadsheet into the Access database. When i run this query it gives an error saying "...
8
by: tomric | last post by:
Hi, I have an option group to select different reports, each report is based off a query that asks for criteria for that report. I have no problem opening any the querys through the group regardless...
3
by: Marc Brown | last post by:
I keep on getting the same error message and I don't know how to either solve the problem or prevent it from showing the error. I have set up a switchboard item that opens a report. When the report...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.