How to stop a form from auto updating table

[pardhu81]

Hi Friends,





Please check image.
I have created a form with the fields which are bound to table fields. I have used vba to insert record and update record. But what i realized during the process for every small change, table is automatically getting updated. I have also referred internet and read about from before update property.
But even that is not helping me when i retrieve data from table.. doing some changes.. and canceling it (lets say i dont want to save changes). but form is automatically taking the changes and it is not even getting into form before update event.

Do you think i should deal with unbound fields instead of bound fields. What is advantage and disadvantage of using unbound fields??

[RuralGuy]

I'm not sure I understand your issue. A bound form will NOT update its record source UNLESS you change something in one of the bound controls. Are you saying the form is changing the record source without you changing anything?

[pardhu81]

No. I am actually changing Bound Controls.. But at times i want to withdraw changes (See my Cancel Button). Even before i press cancel button the data is already updated in table.

[Micron]

I have used vba to.... and update record.

If this happens during the edit process, or after you think you're finished editing, then too late. For a bound form, the only way you can make changes and still cancel them is to NOT do anything that commits those changes. The commit can be caused by moving off the record in any way, or by using code to commit the changes.

What part of the process isn't understood by the user such that a bunch of changes are made but all of a sudden, they're not wanted? If you can't get around this by understanding how the bound form works and adapting user use of the form, or by opening it so that changes can't be made (maybe that is the underlying issue?), then you may have to switch to an unbound form, or bind the form to a temp table and don't write the changes to the main table until you're sure it's warranted.

EDIT:

Even before i press cancel button the data is already updated in table.

If you're in the edit process and take a peek at the table, you may see these changes. However, there should be a lock indicator at the left side of the table row, indicating the record is being edited. If on the form you cancel the edits (subject to the conditions I mentioned above) and refresh the table datasheet view, you'll find that the changes are gone. So I don't know how you're determining that these changes have been committed.

[Missinglinq]

Rather than having a Cancel 'Button,' you can simply ask the user, before the Record is committed, whether they want to save it or not. The message presented, with the below code, adjusts itself, depending on if this is a New Record, or an existing one being edited.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Me.NewRecord Then
   If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save New Record ???") = vbNo Then
     Me.Undo
   End If
 Else
   If MsgBox("Would You Like To Save The Changes to This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then
    Me.Undo
  End If
End If
End Sub

As Micron stated, when looking at the Table while still on a Record in a Form, it may look as if the change has been committed, but it really hasn't, until you do one of the following:

• Explicitly Save the Record, thru code
• Move off of the Record
• Close the Form
• Close Access itself

There are a few specialized situations where Unbound Forms are necessary, but this certainly isn't one of them. The disadvantage of using Unbound Forms is that you have to write code for virtually everything that needs to be done, even the simplest of tasks...whereas with Bound Forms, the Access Gnomes do most of the heavy lifting.

Linq ;0)>

[pardhu81]

Thanks for all the responses.
I have settled with unbound fields. though the coding part increases. i have lot of comfort and good command on table.

[RuralGuy]

It is a shame you have settled for unbound controls. You still have a couple of things to learn about bound forms that will make your life much easier. Good luck with the project.