Is there a way to override automatic updating of Access 2007 form entries created using the Form Wizard?

Question

I am new to Access VBA and created a form using the Form Wizard that displays records in a table. This is a piece of cake.

The behavior that I get in the form, however, is that updates to records occur automatically when moving records.

I want updates to occur only when I click the Refresh button that I paste into the form.

It looks like I can build the form from scratch, programmatically update all the (unlimited) controls, and then programmatically update the record from the controls, but this seems like a lot of work.

Is there a way to "disable" the automatic update behavior from Access or using VBA code?

Thanks!

Answer 1

As Robert suggested, you can avoid saving the modified record by canceling the event before the update. The code will look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer) Me.Undo Cancel = True End Sub 

However, this approach requires that you cancel any changes you make to the record before you can leave it. Without Me.Undo you can cancel the update, but Access will not allow you to switch to another entry. You must either save or discard changes in the current record before moving on to another.

If you want to switch to another record, but first do not discard the changes, I think you need to try a disabled record set. This is a set of ADO records that you create in memory that is not tied to any data source. You can add a command button to save changes to the command. If this sounds helpful, see this Danny Lesandrini article in the Database Log: Create ADO Recordsets

Answer 2

If users accidentally change data and transfer the record to the record that causes updates, you may need to have an “Edit” button so that you can only start editing if necessary. You can use another suggested code to discard changes if they switch to another record or cannot move at all if they do not save or cancel.

Answer 3

Is there a way to "turn off" automatic updating inside Access or using VBA code?

You can try to cancel the Form.BeforeUpdate event if the flag is not set. When the button is clicked, set the flag, set Form.Dirty to false (to save the data), and then clear the flag in the Form.BeforeUpdate event Form.BeforeUpdate .

Answer 4

Why do you need this? Access works by record based on records. Not like Excel, which saves a table only when choosing save or exit.

It looks like you have a continuous form with multiple entries on the screen. The only way to do this is to use a "temporary" table and save the contents of the "temporary" table to a permanent table as soon as you are ready. However, then you lose the ability to determine if someone else has changed the records of the record if you are not doing much more work.