How to stop (cancel) adding a new record

[Christine Pearc]

I can't for my life figure out how to prevent adding a record to a table when the user decides they don't want to add the record, or when they "save" the record but then decide they want to delete it. (Yes, our company has a lot of finicky and confused people.) Problem is, Access automatically adds a record after you've entered something in a field. I've been in a lot of forums looking for answers but the answers are either over my head (theory or incomplete) or not quite what I need. PLEASE can someone help me, a complete novice?

Here's a bit of background.

The application will be used by many people and will reside on our server.

The Main Menu has a button "Add New", with the following command:
DoCmd.OpenForm "Create CAR frm", acNormal, , , acFormAdd, , 1

The Create CAR frm opens to allow the addition of one new record. The form's record source is tblCARs. Control sources for all the fields on the form are set to fields in tblCARs. (I think this means they are "bound"). One of the fields, CARNum, is currently set to be an AutoNumber.

I want this AutoNumber to increment only when the user presses the SAVE button, and the rest of record to be saved only when SAVE is pressed.

If the user presses the CANCEL button, then nothing is saved to tblCARs.

If DELETE is pressed (which becomes active only after SAVE has been pressed), then the record should be deleted.

A very nice Microsoft MVP at the Access community newsgroup offered the following suggestion:

Create another table to hold the highest CARNum assigned so far. Then in the BeforeUpdate event procedure of the form where a new record is added:
a) Open a recordset into this number table, locking it exclusively.
b) Increment the number and hold the lock on the table.
c) Assign the new number ot the record in your form, and save the form.
d) Once the new record has saved, release the lock on the number table.
e) Incorporate error handling that involves random delays and a fixed number of retries to handle the case where multiple users try to append records at the same time.

Unfortunately, I do not think he has the time to help me actually figure out how to do all of this. I've read all the Help info and searched the newsgroup for recordsets, etc. but still can't figure out what to do/what to write in the code.

I need alot of hand-holding for this. Could someone here take me under their wing?

Thank you,
Christine

 

[Pat Hartman]

I want this AutoNumber to increment only when the user presses the SAVE button, and the rest of record to be saved only when SAVE is pressed.

- You don't have any control of this. Access assigns the autonumber as soon as a single character is typed in the form. If the user later cancels the add, the assigned autonumber is discarded. You need to understand that Access does this for a reason. And that reason is to prevent potential conflicts if two users attempt to add a new record at the same instant. If you decide to generate your own "autonumbers" you need to take this problem into account. That is what the MVP was trying to tell you.

Autonumbers are simply a way of uniquely identifying a record. They have no other meaning and you should not attempt to assign one. The fact that there are gaps in the numbers is not a problem. Don't worry about it.

To prevent records from being saved automatically, you need to place code in the form's BeforeUpdate event. That event is executed just prior to the saving of the record. It is never bypassed no matter how the form is closed (except of course if you power off or reboot) so it is the ideal place to stop the save. If your code determines that the current record should not be saved, you cancel the update with:
Cancel = True

 

[Christine Pearc]

Thanks, Pat. Can you (or anyone else out there) please tell me, if I put Cancel = True in the BeforeUpdate event of the form, what else needs to be done? I have a Cancel button whose OnClick event looks like this:
------------------------
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click

MsgBox "Are you sure you want to cancel creation of this CAR?", vbYesNo, "Operation Cancelled"
If vbYes Then

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.Close , , acSaveNo
Else
'do nothing
End If

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox err.Description
Resume Exit_cmdCancel_Click

End Sub
------------------------
How does the above know to go to the BeforeUpdate event; doesn't something need to be set? I am also assuming that the "Cancel = True" will absolutely NOT write the record, so it would not have to be deleted later, correct?

Thanks,
Christine

 

[Pat Hartman]

use a global boolean variable to help control when to cancel an update.

In the form's Current event set your variable to false.
In the cancel button's Click event set your variable to true.
In the form's BeforeUpdate event, check the value of the boolean.

If MyCancelButtonPressed = True Then
    Cancel = True
    Exit Sub
End If
Do any other edits and cancel the update if any errors were found.

 

[eliotchs]

Pat,

I am trying to do the same exact thing,

Can you give a little more guideance on what to put in the events below?

Thanks

use a global boolean variable to help control when to cancel an update.

In the form's Current event set your variable to false.
In the cancel button's Click event set your variable to true.
In the form's BeforeUpdate event, check the value of the boolean.

If MyCancelButtonPressed = True Then
    Cancel = True
    Exit Sub
End If
Do any other edits and cancel the update if any errors were found.

 

[Pat Hartman]

To set the variable to true:
MyCancelButtonPressed = True

To set the variable to false:
MyCancelButtonPressed = False

 

[gray]

Hi

Access 2002
Win XPpro

I need a little guidance on this too please cos' i've spent hours on the problem.

I have a form with my own New, Edit, Save and Cancel buttons. There are several textbox controls on it bound to fields in an Invoices table. The invoices table has an Autonumber field (hidden from the user). There are non- Autonumbered fields containing Invoice numbers and tax dates. My Form by default is AllowAdditions = False

When New is pressed, I set AllowEditions to True and call "DoCmd.GoToRecord , , acNewRec". Then I programmatically calculate the next Invoice number together with a Tax date and add the results into textboxes. At this time, the record appears to get saved as the AutoNumber field gets incremented.

However, I don't want the new record to get saved until the user has added extra detail into it and to give them chance to Cancel the new record. However, when the Cancel button is pressed, the record still gets saved. So far I have tried the following techniques from the Click Event in my Cancel button:-

1) Me.Undo
2) 'SendKeys "{ESC 2}", False
3) The Cancel = True in the BeforeUpdate Form event as suggested above.
4) CurrentDb.Execute ("DELETE * FROM [Invoices] WHERE Invoice_ID = " & Me!Invoice_ID)
5) Disconnected Recordset

Techniques 1) and 2), just don't seem reliable, 3) prevents any navigation through the records and my record still seems to get saved... and 4) understandably fails because I've already got the table open. Finally figured out how 5) worked only to find that where a record was deleted, the changes to any edited records did not get made when the recordset was re-connected.. even though the status flags were set! I guess the bookmarks or something were affected by the deleted record?

I guess there is a best practice for this type of thing? I've read quite a few posts on the subject so there seems to be a demand for manipulating one's own Save, Cancel etc buttons. I'd be very grateful if someone could point to a reliable technique please... Should I, for example, temporarily unbind all my controls when the New button is clicked, unlock them, add in my program generated values to the controls, allow the user to put their values in and re-bind the controls and call AcSave when the Save button is pressed? Seems an awful lot of work to me?

Thanks in advance.

 

[Pat Hartman]

Ultimate control rests with the form's BeforeUpdate event. This is where you need to do any multi-field or null field validation and this is where you would put your query to the user as to whether he wants to save. I don't use these queries except in rare cases.

If a record fails any edits, you would cancel the update by setting the Cancel argument to True ---
Cancel = True

I always use the built in Access funtionality unless my customer insists on custom coding. One of the main selling points of Access is to not have to code all these buttons.

But basically you need to use a public variable that gets set depending on what the user is trying to do.

Public MyAction As Variant

CurrentEvent:
MyAction = Null

Click Event of Save Button:
MyAction = "Save"
DoCmd.RunCommand acCmdSaveRecord

Click Event of Cancel Button:
MyAction = "Cancel"

Form's BeforeUpdate Event:
Select Case MyAction
   Case "Cancel" 
      If Msgbox("Do you want to cancel this update?, vbYesNo), = vbYes Then
        Cancel = True
        Exit Sub
      End If
   Case "Save"
   Case Else
       MsgBox("Please press the Save or Cancel button", vbOkOnly)
       Cancel = True
       Exit Sub
End Select

Do any other editing you normally would do

 

[gray]

Hi Pat

Thanks for your assistance with this problem. I've coded the form's module as per your example thanks. Unfortunately, upon replying yes to the "Do you want to cancel this update?" prompt, the following error is generated.

" The setting you entered isn't valid for this property"

Using a breakpoint indicates that the Cancel = True works OK and that it's the Exit Sub that throws this error.

Incidentally, my form has a subform and I wonder if my previous attempts at cancelling have suffered from some focusing problem?

 

[Rich]

What are you trying to Cancel, the Main form before you enter the Sub or the Subform from a button on the Main?

 

[gray]

Hi Rich

The Cancel = True is in the mainform and the cancel button also on the main form (but see my capitulation para below ...)

My subforms are located on tab pages of the mainform and I had hoped to have the Cancel button, located on the mainform footer, carry out a cancel action for all three forms (main plus 2 subs). As I learn more about Access I realise how difficult this is to achieve. I tried to implement the Canel=True technique for all 3 forms but the various eventing and focus'ing seems to defeat all my attempts.

Thanks very much to you and Pat for showing an interest in this problem but, having experimented with it for nearly two weeks, I have decided to abandon the idea. I did think about achieving this functionality with temporary tables but then when I go multi-user I think a whole lot of other problems might appear.

Thanks again everyone... your help really is appreciated!

 

[Pat Hartman]

The problem isn't Access per se, the "problem" is that in a relational database, the "parent" record MUST be saved before a "child" record may be saved since the child record MUST contain the primary key of the parent record as a foreign key in order to maintain the relationship. Generally, the primary key will be an autonumber which will not even be known until the record is actually saved (if you're using SQL Server or other ODBC database).

Trying to control an entire set of records is best done with temporary tables. When the set is "complete", you would run whatever validation procedures you need and then copy the data from the temp tables to the perm tables and delete from the temp tables. You would need to track the "owner" of a set of records so that you can remind people to clean them up or delete them if they hang around too long.

I think there is something else at play. The Exit Sub shouldn't cause an error.

 

[gfultz]

I have been fighting with this for a little while now. Here is the code I have:

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
If Me.Dirty Then
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If
    MyAction = "Cancel"
    DoCmd.Close
Exit_Cancel_Click:
    Exit Sub
Err_Cancel_Click:
    MsgBox Err.Description
    Resume Exit_Cancel_Click
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MyAction
   Case "Cancel"
        Cancel = True
        Exit Sub
   Case "Save"
   Case Else
       MsgBox "Please press the Save or Cancel button", vbOKOnly
       Cancel = True
       Exit Sub
End Select
End Sub
 
Private Sub Form_Current()
MyAction = ""
End Sub
 
Private Sub AddMR_Click()
On Error GoTo Err_AddMR_Click
    'Save Record
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    'Turn warnings off and insert records into related tables, then turn warnings back on
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Insert INTO QUOTE(MRID) VALUES(" & Me.MRID & ")"
    DoCmd.RunSQL "Insert INTO PO(MRID) VALUES(" & Me.MRID & ")"
    DoCmd.RunSQL "Insert INTO PREQ(MRID) VALUES(" & Me.MRID & ")"
    DoCmd.SetWarnings True
    
    'Declare variables for openform command
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "MRSTAT"
    
    stLinkCriteria = "[MRID]=" & Me![MRID]
    
    MyAction = "Save"
    
    'Close this form
    DoCmd.Close
    
    'Open MRSTAT form to status new record
    DoCmd.openForm stDocName, , , stLinkCriteria
    
    
Exit_AddMR_Click:
    Exit Sub
Err_AddMR_Click:
    MsgBox Err.Description
    Resume Exit_AddMR_Click
    
End Sub

The cancel button works fine. It closes the form out and doesn't save it. The Add button does not.

Any help would be appreciated.

 

[georgedwilkinson]

A couple of things:
1. Open a new thread.
2. Describe how you know it is not working.
3. Include and errors and highlighted lines.
4. Include more detail!