Need help with adding a prompt to save changes in table

[Astron2012]

Hi,

I am converting a large spreadsheet of jobs into a Access 2007 database for me and one other to use. Due to the large number of columns it is impossible to fit it all comfortably in a form with continuous forms view.

We need to be able to see all records and columns at once, like excel (I no this is probably every Access programmers worst nightmare as this is not really what access is designed for) but we need to be able to access/modify the data simultaneously so therefore excel is not a viable option.

Due to this I have created a form with subform that shows in datasheet view. This works and looks fine apart from the autosave feature as there may be instances where users may make changes to the data without realising. I would therefore like to add code to the subform that will prompt the user to save changes.

Normally I use the following code for usual forms which works a treat;
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
On Error GoTo Err_BeforeUpdate
' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save your changes?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then


Me.Undo
End If
End If
Exit_BeforeUpdate:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

However when I add this to the subform with the datasheet view it prompts to save after each modification (which is what it is asked to do) but this is very annoying as it pops up each time u move to another record which you would expect to do in datasheet view. I have tried to only run the code on the On Close event but it does nothing.

I would just like it to prompt to save on exit instead of after each change to the data.

If you could help with this I would be really grateful.

[RuralGuy]

You may be up against the wall here since Access saves all modified data when you try and leave the record.

[Astron2012]

I was hoping there would be some way around this, maybe with creating temp tables?

[RuralGuy]

Using transactions may do what you want. MVP Allen Browne uses them on occasion: http://allenbrowne.com/appaudit.html

[Astron2012]

I think this is for use with forms

[RuralGuy]

I think this is for use with forms

Which is what you have, right?