By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

which event? - for changes in existing records but not new records

P: 52
Hi there,

I have a message box that I want to pop up when a user changes data in an existing record. BUT I don't want it to pop up when a new record is being started, Which event should I put this in?

I am in Access 2000, and I have set up my message box in the properties table, not in Visual Basic.

Can I sort this in the Properties table? When I go into Visual Basic - I can't find the code that matches my message box. Is everything in the database in code - I thought it was????

Thanks if you can help,

Nov 14 '08 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,532
No, what's in the Properties Sheet is in the Properties Sheet, not in code, and this kind of thing will need to be done thru code.

You don't say if you want a warning if a certain piece of data is changed or if any change is made to the existing record. Assuming the latter, I think this, placed in the code module of your form, will do what you need done:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Dirty(Cancel As Integer)
  2.  If Not Me.NewRecord Then
  3.   Response = MsgBox("You are about to change data in an existing record! Would you like to proceed?", vbYesNo)
  4.    If Response = vbNo Then
  5.     Cancel = True
  6.     Me.Undo
  7.    End If
  8.  End If
  9. End Sub
When the user first attempts to change data a message pops up, asking him if this is his/her intention. If the user responds Yes, they are allowed to proceed. If they respond No, the change that has already been made (which is, in fact, only the entering or deletion of a single character) is undone.

This can be modified if it doesn't meet your exact needs.

Welcome to Bytes!

Linq ;0)>
Nov 14 '08 #2

P: 52
Thanks for the feedback.

What I wanted to set up was a warning that alerts the user that they have changed data in a saved record with a prompt to undo if the change is not needed.

I like what you have proposed - but can you please customise it to apply to only two fields - called [School2] and [Student Name].

In our experience, it is too easy for these fields to be inadvertently changed by a user and we need to protect that data.

I know I need to work on the naming of my database - I have been directed to the naming conventions by another expert on this forum. Can you tell me if you have any experience using the ACC Technology Renaming Wizard?


Nov 15 '08 #3

Expert 2.5K+
P: 3,532
That's an entirely different can of worms, but doable, of course.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Not Me.NewRecord Then
  4.  If Me.School2.Value <> Me.School2.OldValue Then
  5.   Response = MsgBox("You are about to change data in the School2 Field! Do you wish to proceed?", vbYesNo)
  6.   If Response = vbNo Then
  7.    Me.School2.Value = Me.School2.OldValue
  8.   End If
  9.  End If
  11.  If Me.Student_Name.Value <> Me.Student_Name.OldValue Then
  12.   Response = MsgBox("You are about to change data in the Student Name Field! Do you wish to proceed?", vbYesNo)
  13.   If Response = vbNo Then
  14.    Me.Student_Name.Value = Me.Student_Name.OldValue
  15.   End If
  16.  End If
  18. End If
  19. End Sub
Note that when a control's name has a space in it, like "Student Name", Access VBA places an underscore in it to replace the space. So in code, it has to be referred to as Student_Name.

Sorry, I've never even heard of the ACC Technology Renaming Wizard.

Linq ;0)>
Nov 15 '08 #4

P: 52
Thank you! That has worked beautifully just the way I wanted it to!

Thank you for all your help.
Nov 15 '08 #5

Post your reply

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