views:

42

answers:

4

I'm new to Access VBA, and have created a form using the Form Wizard that displays records in a table. That's a piece of cake.

The behavior that I get with the form, though, is that updates to the records occur automatically when I move around records.

What I'd like is to have the updates occur only when I click an "Update" button that I put in the form.

It seems like I can construct the form from scratch, update all of the (unbounded) controls programmatically, and then update the record from the controls programmatically, but this seems like too much work.

Is there a way to "turn off" the automatic updating behavior from within Access, or using VBA code?

Thanks!

+1  A: 

Is there a way to "turn off" the automatic updating behavior from within Access, or using VBA code?

You can try cancelling the Form.BeforeUpdate event, unless a flag is 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 handler.

Robert Harvey
That sounds easy enough. I'll give it a try. Thanks!
John at CashCommons
+1  A: 

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

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

However, that approach requires you discard any changes you've made to the record before you can move off it. Without Me.Undo, you can cancel the update, but Access won't allow you to move to a different record. You must either save or discard the changes to the current record before moving to another.

If you want to move to another record but not discard changes first, I think you need to try a disconnected recordset. It's an ADO recordset you create in memory, not bound to any data source. You can add a command button to save your changes on command. If it sounds useful, see this article by Danny Lesandrini at Database Journal: Create In-Memory ADO Recordsets

HansUp
Thanks. I ran into the problem of not being able to move to the next record (by not including `Me.Undo`). As for discarding changes, that's probably all right. I'll just alert the user that changes will be discarded if the form is dirty. In any case, I'll try this now.
John at CashCommons
+1  A: 

Why would you want to? Access works on a record by record basis. Not like Excel which only saves the spreadsheet when you choose to save or exit.

It sounds like you have a continuous form with multiple records on the screen. The only way to do this would be to use a "temporary" table and the save the contents of the "temporary" table to the permanent table once you're ready. However then you will lose the ability to determine if someone else has changed the records unless you do a lot more work.

Tony Toews
The reason I want to is for UI reasons. The person I'm designing the form for likes paper far better than she likes computers, and having stuff update automatically would frustrate her to no end. The more explicit and deliberate I can make the editing, the better. Thanks for your suggestions!
John at CashCommons
+1  A: 

If users are accidentally changing data and moving record to record causing updates, maybe you should have an Edit button to only start editing when needed. You can use other suggested code to either undo the changes if they move to another record or prevent them from moving at all unless they save or cancel.

Jeff O
The easiest way to accomplish this would be to have AllowEdits turned off, and have an EDIT button that turns it on (and toggles its caption to SAVE). You would also want to prevent navigation to other records while you're in EDIT mode. How that its done depends on a number of factors. You could turn off the Navigation Buttons and trap for PageUp/PageDown and Ctrl-Home/Ctrl-End, but you'd also need to have the Cycle property set to Current Record. Not sure if that covers it all, but it does seem to me to be the simplest approach.
David-W-Fenton