How to Not AutoSave Record when Exiting Form

[PaulaJ]

I am working with a form that has a table as a Record Source with fields to enter new records only. That same form has the table displayed as a subform as well. This same form will also serve as a display of existing records. How do I prevent a new record being added each time the form is displayed? I would like to be able to save the new record if entered (SAVE Button), but otherwise not save anything if the form is closed.

If I make the fields unbound that I want to be saved when the Saved Button is clicked, how do I add the new record? Sometimes Access is just too automatic. Any design suggestions would be appreciated.

 

[ajdesalvo]

Place the following code in the form's Before Update event:

If Me.Dirty = True Then
If MsgBox("Press Yes to Save this record. Press No to undo changes.", vbYesNo, "Save Changes&quot = vbNo Then
DoCmd.CancelEvent
Me.Undo
End If
End If

Hope this helps! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[FancyPrairie]

I'm not sure I follow exactly what you're trying to do. But, if I understand you correctly, you want one form to serve 2 purposes. If the user comes into the form one way, you want them to only be able to add new records. If they come into the form another way, you want them to be able to edit existing records. To do that, when you open the form via the OpenForm method, use the OpenArgs option of the OpenForm method. For Example,

Docmd.OpenForm "YourForm",,,,,,"New Records Only"

Then, in the OnOpen event of the form, do something like this:

if (me.OpenArgs = "New Records Only&quot then
Me.DataEntry = True
Else
Me.DataEntry = False
End If

You can also take advantage of the BeforeUpdate event of the form and check to see if the record is dirty or if it's a new record, etc. and then process it from there. For example,

If (Me.Dirty) then 'If True, Record was changed

If (Me.NewRecord) then 'If True, New record is about to be added

Does this come close to answering your question?

 

[PaulaJ]

It's a little simpler than that. The Subform is strictly display only (No update). I tried the .Dirty keyword and it seems that it is always Dirty. If I change a field programmatically (put today's date as a default in one of the fields), does that make it dirty?

I want to have the New Notes Record available for entry if desired and then Save it (either by Command Button or exiting the form) OR have nothing happen when I exit the form, including not saving the new record that was created when the form was loaded but had nothing entered into it. Currently, I am getting a blank record for each time I open the form. (The Exit is a command button as well).

 

[PaulaJ]

I have put in the functions suggested. Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

Dim rst As Recordset
Dim db As Database

If IsNull(Me.[Note]) Then
DoCmd.CancelEvent
Me.Undo
GoTo Exit_Form_BeforeUpdate
End If

If Me.Dirty = True Then
If MsgBox("Press Yes to Save this record. Press No to undo changes.", vbYesNo, "Save Changes&quot = vbNo Then
DoCmd.CancelEvent
Me.Undo
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("PC-MemberNotes&quot
rst.AddNew
Me![Member#] = Me![InMember#]
Me![EntryDate] = Me![InDate]
Me![Note] = Me![InNote]
rst.Update
rst.Close
End If
End If

Exit_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Form_BeforeUpdate
End Sub

Unfortunately, this does not add a new record. It is updating the existing Note. I never get more than one note for a member in the table. What am I doing wrong?