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! -
Private Sub Form_BeforeUpdate(Cancel As Integer)
-
-
If IsNull(Me.EquipNumber) Then
-
Do
-
InputEq = InputBox("An equipment number is required. Please enter a value:", "Missing Equipment Number")
-
If InputEq = "" Then
-
If MsgBox("An Equipment Number is required to save." & vbCrLf & _
-
"Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
-
DoCmd.RunCommand acCmdCloseWindow
-
End If
-
End If
-
Loop Until InputEq <> ""
-
-
Me.EquipNumber = InputEq
-
End If
-
-
-
If IsNull(Me.txtUser) Then
-
Me.txtUser = Environ$("Username")
-
Me.txtEnterDate = Date
-
End If
-
-
-
End Sub
-
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.
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
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.
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? - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
-
If IsNull(Me.EquipNumber) Then
-
Do
-
InputEq = InputBox("An equipment number is required. Please enter a value:", "Missing Equipment Number")
-
If InputEq = "" Then
-
If MsgBox("An Equipment Number is required to save." & vbCrLf & _
-
"Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
-
Cancel = True
-
InputEq = "1"
-
End If
-
Else
-
Me.EquipNumber = InputEq
-
End If
-
Loop Until InputEq <> ""
-
-
End If
-
-
End Sub
You can attempt to put a Me.Undo after Cancel=True to back out the user's changes: - ...
-
Cancel = True
-
Me.Undo
-
InputEq = "1"
-
...
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.
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: - Private Sub Form_BeforeUpdate(Cancel As Integer)
-
-
If IsNull(Me.EquipNumber) Then
-
Do
-
InputEq = InputBox("An equipment number is required. Please enter a value:", "Missing Equipment Number")
-
If InputEq = "" Then
-
If MsgBox("An Equipment Number is required to save." & vbCrLf & _
-
"Would you prefer to delete the current record?", vbCritical + vbYesNo, "Delete Record?") = vbYes Then
-
With CodeContextObject
-
On Error Resume Next
-
DoCmd.GoToControl Screen.PreviousControl.Name
-
Err.Clear
-
If (Not .Form.NewRecord) Then
-
DoCmd.RunCommand acCmdDeleteRecord
-
End If
-
If (.Form.NewRecord And Not .Form.Dirty) Then
-
Beep
-
End If
-
If (.Form.NewRecord And .Form.Dirty) Then
-
DoCmd.RunCommand acCmdUndo
-
End If
-
If (.MacroError <> 0) Then
-
Beep
-
MsgBox .MacroError.Description, vbOKOnly, ""
-
End If
-
End With
-
-
DoCmd.RunCommand acCmdCloseWindow
-
InputEq = "1"
-
End If
-
Else
-
Me.EquipNumber = InputEq
-
End If
-
Loop Until InputEq <> ""
-
-
End If
-
-
End Sub
Thanks again!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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 =...
|
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...
|
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 "...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |