stop record from being saved

[gfultz]

Hi All,

This has been discussed in an older thread, but I still wasn't able to get this resolved. I was informed to start a new thread and go into more detail so here goes:

1. I want my form to only add new records.
2. Like it should, when you start typing information in one of the fields, the autonumber increments.
3. I have a cancel button that works fine, although it bypasses the beforeupdate code. (I know this because I tested the value of the public variant after using the cancel button)
4. If I try to use the save button, it returns the msgbox from the else case of the beforeupdate event and does nothing else.

Here is the code:

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

[/SIZE][/FONT]

I have thought of a work around and that is to create an additional button and remove the Control Box / Close button from the form. The first button would cancel by cleaning the form and closing it out. The second would just clean the form and the last would save the record and execute the other necessary code. I would prefer to have a safeguard in there to prevent adding a record with out executing the SQL code to create the child records.

 

[gray]

Hi Gfultz

I was part of that eralier thread... I had all sorts of problems with this method of preventing a save... (I finally used a temporary table method to make sure users could cancel updates)... Some weeks later I discovered that there is an Access Constant called 'vbtrue'

Although none of my code bombed out with "Cancel=True" , it didn't prevent the Save from happening. When I substituted it for "Cancel=vbtrue", however, it worked (i.e. cancelled the save).

Not sure if it'll resolve your problem but it's got to be worth a try?

 

[Steve R.]

I have a "solution" for adding a new record that some may not consider elegant. We were having a problem that new records were being created that were blank and/or incomplete. The solution below seems to have solved that problem.

1. The form is opened without any reference to a record source. All the controls are unbound.

2. The close (add record) button performs a test on the form to verify that all the data is correct. If correct, the record is added. If not, the user is given an error message with an opportunity to cancel the data entry. (Of course you could also have a separate cancel button.)

3. When the close button is pressed (and the form passes the data inspection) a datasource is then assigned to the form, the controls are assigned to specific fields, and the record is added.

 

[Galaxiom]

I have a "solution" for adding a new record that some may not consider elegant.

When using unbound controls on an unbound form I simply write the values held in the controls to the fields in the appropriate tables using a command. This would seem much more straightforward than assigning a RecordSource to the form and a ControlSource to each control.

 

[Steve R.]

When using unbound controls on an unbound form I simply write the values held in the controls to the fields in the appropriate tables using a command. This would seem much more straightforward than assigning a RecordSource to the form and a ControlSource to each control.

Good point, there are always alternative approaches. One reason for the approach I took is that the same form is used for both data entry and to scroll through (edit) the dataset. Once the new record is created, the user has access to the entire recordset. (A case statement is used to differentiate which mode (edit/new) is used for the form.)

 

[double2]

Hi guys,

Having looked at a number of solutions to removing automatic saving of records from entry forms I suddenly invented my own, super easy solution to the problem! Excuse me if this is considered messy or a bit of a bodge but it is working very well for me so I felt the need to share it.

What I have done is add an extra field to my table named "SaveOK". This is a required field that does not allow zero length. At the end of my data entry form I have placed a combo box titled "Complete?" with the only option being "OK". This then means that the record itself can only be saved once this field has been populated. Then it is simply a case of making the particular error relating to this box not being filled say "Exit without saving?" and the end user experience is extremely fluid.

Also, upon editing existing records [I imagine you can] have a bit of OnOpen code that simply blanks this field, essentially meaning updates will only be aknowledged once this field have been repopulated.

If anyone can think of a reason (apart for the required "evil" of having a superfluous field on your tables) that this would not be an advisable solution please let me know, as I am about to roll this out accross a widely used database of mine.

 

[gray]

Hiya

I think you'll find that if any of your controls are bound the updates you make in them will be written immediately to your tables... unless you use the Cancel=Vbtrue in your form's beforeupdate event.... as I have discovered to my cost, the downside of using the cancel=vbtrue method is that Access won't let you click on anything outside the immediate form (whilst it thinks there is an outsanding change)...

This is OK if your forms are not particularly complex but if you are using your own custom buttons (in the header or footer) and/or subforms, you'll find you can't use any of them whilst the main form update is outstanding.

If you don;t use bound controls then you will need a fair bit of code to cycle around all your controls to grab their values and write them to your tables...

Catch 22 I think our American cousins say?

If you don't meet these hitches and you don;t need reams of code, I'd love to hear more about how you've done it cos' I've been grappling with the problem of preventing updates before a user presses a Save button for months...

 

[double2]

I think you'll find that if any of your controls are bound the updates you make in them will be written immediately to your tables... unless you use the Cancel=Vbtrue in your form's beforeupdate event....

Hmm...this is potentially my ignorance coming to surface but surely if you haven't put anything in a required field then no fields in that record will be saved as it renders the whole record invalid? Obviously you would need one of these SaveOK fields for each table, including any other tables on subforms. Surely you could have one command button which populates all necessarry SaveOK fields, maybe stored in invisible textboxes?

 

[gray]

Hmmm... I guess that will work ... but I think you will get the same effect with the 'official' Cancel=Vbtrue method...

Just add a variable at the top of your Module ( I use: Allow_Save As Boolean) then test your variable in the Beforeupdate event of your form

If Allow_Save <> True Then
Cancel = vbTrue
Exit Sub
End If

And hey presto the save is prevented....

In your Save button:

Allow_Save = True
Me.Dirty = False
Allow_Save = False

Which'll save the record and set it up for the next one......

To prevent the user from doing an invalid save, as you say, set the relevant table fields to 'required'..or.. you can carry out, more complex, validations in your BeforeUpdate event ...

I've gone through the mill and back again on this... in the end I decided to stick with the... err... 'official' Access model...

It seems to work great for simple forms... my problem is that I have some fairly complex ones with subforms, headers, footers and tabbed pages.... and trying to prevent auto saves whilst allowing focus to move to subforms seems to be mutually exclusive.. hey ho!