469,342 Members | 6,785 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.    ' This procedure checks to see if the data on the form has
  4.    ' changed. If the data has changed, the procedure prompts the
  5.    ' user to continue with the save operation or to cancel it. Then
  6.    ' the action that triggered the BeforeUpdate event is completed.
  7.  
  8.    Dim ctl As Control
  9.  
  10.    On Error GoTo Err_BeforeUpdate
  11.  
  12.    ' The Dirty property is True if the record has been changed.
  13.    If Me.Dirty Then
  14.       ' Prompt to confirm the save operation.
  15.       If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
  16.               "Save Record") = vbNo Then
  17.          Me.Undo
  18.       End If
  19.    End If
  20.  
  21. Exit_BeforeUpdate:
  22.    Exit Sub
  23.  
  24. Err_BeforeUpdate:
  25.    MsgBox Err.Number & " " & Err.Description
  26.    Resume Exit_BeforeUpdate
  27. End Sub
Jul 29 '10 #1

✓ answered by 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.

7 4136
NeoPa
32,182 Expert Mod 16PB
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.
Jul 29 '10 #2
Jerry Maiapu
259 100+
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
Jul 30 '10 #3
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.
Jul 30 '10 #4
colintis
255 100+
@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.
Jul 30 '10 #5
missinglinq
3,532 Expert 2GB
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)>
Jul 30 '10 #6
NeoPa
32,182 Expert Mod 16PB
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.
Jul 30 '10 #7
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!
Jul 30 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by J.Marsch | last post: by
3 posts views Thread by Jimmy | last post: by
11 posts views Thread by OldProgrammer | last post: by
1 post views Thread by Steve | last post: by
7 posts views Thread by denuk2003 | last post: by
1 post views Thread by Dennis | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.