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

Getting error when using Me.Dirty = False

Seth Schrock
Expert 2.5K+
P: 2,931
I have some code on a form that asks you if you want to save the changes to the record before moving on to the next record or closing the form. The code is in the form's BeforeUpdate event. This is what I have currently:

Expand|Select|Wrap|Line Numbers
  1. If intResponse = vbCancel Then
  2.      DoCmd.RunCommand acCmdUndo
  3. Else
  4.      Docmd.RunCommand acCmdSave
  5. End If
This works. However, the acCmdSave is very slow; usually about 2 - 3 seconds. However, I also have a Save button that uses:

Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty = True Then Me.Dirty = False
This works in about .25 - .5 seconds. My idea was to use this same code in the form's BeforeUpdate event in place of the DoCmd.RunCommand acCmdSave. When I do, I get the following error message:

Run-time error '2115':
The macro of function set to the BeforeUpdate or ValidationRule property for this field is preventing IT Inventory from saving the data in the field.

I checked and there is no validation rule on the field that was changed. Here is the entire BeforeUpdate event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strMsg As String
  3. Dim intResponse As Integer
  4.  
  5. If intSaved = 0 Then
  6.     strMsg = "you have made one or more changes to this Record.  Do you wish to save this record" _
  7.              & "with those changes?" & vbCrLf & vbCrLf & "Click OK to save changes, or Cancel to" _
  8.              & "Undo these changes."
  9.  
  10.     intResponse = MsgBox(strMsg, vbQuestion + vbOKCancel + vbDefaultButton1, "Prompt to Save Record")
  11.  
  12.     If intResponse = vbCancel Then
  13.         DoCmd.RunCommand acCmdUndo
  14.     Else
  15.         'DoCmd.RunCommand acCmdSave
  16.         Me.Dirty = False
  17.  
  18.     End If
  19.  
  20. Else
  21.     intSaved = 0
  22. End If
  23.  
  24. End Sub
Just to let you know, intSaved is publicly declared at the top of the form's VBA page. I use it to know if the Save button has been clicked (which sets the variable to 1).

What is wrong with my code?
Nov 14 '12 #1

✓ answered by zmbd

Seth,
Without knowing your form it's hard to give you too much...

1) acSave - yes, technically, it is for saving the form, table, etc... it will most often force a save on a dirty record; however, not always.

2) acSaveRecord - yes, technically, it is the one you more than likely should be using. This will cause the same error as you've already seen for the reason given in #2

3) If you are in the before update event, the record is already attempting to be saved... trying to resave it in this event seems, well, odd. However, I don't know the design of your form...

4) Code to check if the form is dirty before close is a good idea. I usually work around that by locking all of my record forms and using the footer as a "new" record or "Edit" current record zone that has a "save" button (you've seen that I think in one of the other threads yes?), which leads to...

4a) You can place your command button to save the record and in the onclick event use the If Me.Dirty = True Then Me.Dirty = False You can have the button show up in the on-dirty event and hide itself when clicked or in the after update event. Nice thing here is that if the record isn't dirty (new or old) usually no errors. It will cause the before-insert, after-insert, before-update events to fire (all if new record just update if old) so that you can use validation code. I will do this in my "management" level forms as a visual... the caption reads "SAVE Changes?" and is usually Bright-Red letters on Light-Blue background.... hurts the eyes due to colour contrast!

Share this Question
Share on Google+
9 Replies


zmbd
Expert Mod 5K+
P: 5,287
Think of it as a record locking conflict.
The Before Update event has fired.
Locking the record for validation.
Then If Me.Dirty = True Then Me.Dirty = False line executes in your event...
Because there is a difference between the old and new values this line of code causes the Before Update Event to fire again and when the second itteration attempts to lock the record it finds the record is already locked and thus you get the error message.
:)
(you can see the order of trip by using a debug.print "eventname" in the before insert, after insert, before update, after update etc... there's also a list of event order: Form Objects
Nov 14 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,931
Then why does it work with the save button? Because the BeforeUpdate event is bypassed with the intSaved variable set to 1? Does acCmdSave not lock the recordset? Or is that why the difference in time?

A lot of questions :) I just want to understand how this works so that I can use each way in the most advantageous way.
Nov 14 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,931
In looking for other methods of saving a record using VBA, I found an article that says that acCmdSave doesn't save the record, "acCmdSave saves *design changes to the structure of the object*" Alternative to acCmdSave. It also says that I should use acCmdSaveRecord instead. Is this correct? If so, then I would expect that acCmdSaveRecord would have the same issue as Me.Dirty = False. I will test this out shortly. At least I know what the issue is and what I need to work around.
Nov 14 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,931
Just tested acCmdSaveRecord. I got the same error message.
Nov 14 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
Seth,
Without knowing your form it's hard to give you too much...

1) acSave - yes, technically, it is for saving the form, table, etc... it will most often force a save on a dirty record; however, not always.

2) acSaveRecord - yes, technically, it is the one you more than likely should be using. This will cause the same error as you've already seen for the reason given in #2

3) If you are in the before update event, the record is already attempting to be saved... trying to resave it in this event seems, well, odd. However, I don't know the design of your form...

4) Code to check if the form is dirty before close is a good idea. I usually work around that by locking all of my record forms and using the footer as a "new" record or "Edit" current record zone that has a "save" button (you've seen that I think in one of the other threads yes?), which leads to...

4a) You can place your command button to save the record and in the onclick event use the If Me.Dirty = True Then Me.Dirty = False You can have the button show up in the on-dirty event and hide itself when clicked or in the after update event. Nice thing here is that if the record isn't dirty (new or old) usually no errors. It will cause the before-insert, after-insert, before-update events to fire (all if new record just update if old) so that you can use validation code. I will do this in my "management" level forms as a visual... the caption reads "SAVE Changes?" and is usually Bright-Red letters on Light-Blue background.... hurts the eyes due to colour contrast!
Nov 14 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,931
So basically, all I need to do is if the answer to the messages box is not vbYes (the else portion) then just exit sub and the database will save the record for me.

And yes, I have seen your New and Edit portion in the form footer.

Edit: I just tried taking out the Else portion of my code and it works just fine. The records are saved just like I need them to be and very quickly too.
Nov 14 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
Yep... you got it then.
One thing I would do is just before the undo command (between lines 12 and 13 in OP is insert Cancel=True by setting this true, you're telling Access to abort the changes and handle everything else related there to; however, make sure you leave the undo command in there to revert the record.
If you set it false, then you need to make sure that the control handles all of the changes.
Nov 14 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,931
Setting Cancel=True would stop the move to another record or closing of the form, correct?
Nov 14 '12 #9

zmbd
Expert Mod 5K+
P: 5,287
Yes/No... yes if the event is triggered from a record move then setting the cancel to true should prevent the record movement (notice I used the word "should").
If the event was triggered do to a form close then it will prevent the record update however I the form will close; however, access should (notice I used the word "should" again) warn the user that edits will be lost.
Nov 14 '12 #10

Post your reply

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