Access 2007 form_BeforeUpdate Event does not fire

[Fritz G]

Hi I have a simple one table, one form MS Access 2007 DB. I want to have the data entry person verify their changes to a record before closing the form or moving to the next record only if any changes were made. I copied some VB code from MS KnowledgeBase (Article ID: 197103 ACC2000: How to Prompt User to Save Changes to Record in a Form for Access 2000) that works perfectly, but only one time. When I close the DB and reopen, the code no longer fires and I can make changes to any field on the form then move to the next record or close the form and those changes are immediately written to the table which is exactly what I want to avoid. Is this just a bug or am I missing something? I have tried to decompile then recompile but that didn't work. I can create a new blank DB then import the old DB and again the code will fire perfectly one time, as soon as I close the DB then reopen it the event code no longer fires. Any help would be greatly appreciated.


I pasted this code into the form_BeforeUpdate property.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   ' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.
 
   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?", 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

[NeoPa]

I don't know Access 2007 very well, but I know that it can be quite unhelpful sometimes when it blocks code from running. I can't tell you exactly where to look but it sounds as if your code is being blocked and you need to tell it to trust your project. Exactly what the steps are I'm afraid I can't tell you.

This means it's not a problem with the code as such, but with the settings that block the code from even starting.

[Jerry Maiapu]

I am certain that your code will work perfectly well with MS access 2003 so just export your table and form to MS2003.

Just an idea
Passing by

[Fritz G]

thanks for quick replies, i guess i should give up on access 2007, but kind of a pain dealing with work station upgrades, i tried saving in 2003 format without success but only with 2007 client, i'll try full 2003 tomorrow, just seems like really simple VB should just work in 2007.

[colintis]

@Fritz G
Not really, you can first try setting the trust center correctly and it may give you the answer. Just keep in mind there may be some coding styles will not be accepted in 2007.

As the article you found for reference is for Access 2000, it may be better off to search with Access 2007 instead.

[missinglinq]

To trust your folder, click:

• Office Button (top left)
• Access Options (bottom of dialog)
• Trust Center (left)
• Trust Center Settings (button)
• Trusted Locations (left)
• Add new location (button)

Welcome to Bytes!

Linq ;0)>

[NeoPa]

I'm sure the idea of moving back to 2003 would work for you Fritz, but you should probably ask yourself first if this is really the direction you want to move in. Getting into a cul-de-sac is rarely good long-term thinking.

I wasn't suggesting earlier that this was a difficult problem to resolve. Simply that I didn't know the solution myself. It seems that others have already posted quite good solutions that should have you working in no time. I'm sure that's a better approach for you.

[Fritz G]

Wow! Thanks so much to all of you for the help. It looks like adding the directory where the DB lives to the "Trust center" has allowed to event code to fire each time now and this case is closed. Thanks again!