Prevent datasheet selection change from saving the current insertion

[amd711]

In a splitted form, editing through datasheet can be prevented by changing the datasheet properties to read only but changing the selected record by clicking on the side of the datasheet will cause the current insertion -either new record or edited record- to be saved befor moving to the selected record.

how can i prevent that?

[Missinglinq]

The short answer is that you can't! In Access, when you move from one Record to another Record, the Current Record is Saved. In a Split Form, when you click on one of the Records, in the Split View portion of the Form, you're moving to another Record, and so the Current Record, shown in the Single View Record portion of the Form, is Saved.

The only way around this behavior, in Access, is rather convoluted and requires using an Unbound Form, and Unbound Forms can only display one Record at a time, making the Split Form basically useless. Using an Unbound Form, like this, you have to use a Temporary Table to write data to, then write code to use after you've done whatever it is you want to do to Append the Records from the Temp Table to your regular Table. Way more work, in my opinion, to avoid a major default behavior in Access.

Linq ;0)>

[amd711]

so is it possible to:

1- capture this event. (moving between records using the datasheet). or

2- make the datasheet completely unabled - just for view.

[Missinglinq]

so is it possible to:

...capture this event. (moving between records using the datasheet)...

When moving from Record to Record, the Form_Current event fires. But in a Split Form, any code you might use, in the Form_Current event, to try to work around your problem, will be executed whether you move Record-to-Record in the Datasheet View portion or the Form View portion of the Form.

so is it possible to:

...make the datasheet completely unabled - just for view.

No. You can set

1. The size of the Datasheet portion, in relation to the entire Form
2. Where it appears, in relation to the Form View portion
3. Whether or not there is a Separator Bar between the Form View and Datasheet View portions of the Form
4. Whether or not the user can move the Separator Bar, at runtime, to adjust the size of the Datasheet portion of the Form
5. Whether or not the Datasheet portion or the Form View portion, of the Form can be printed

But you cannot 'disable' the Datasheet portion of the Form. If you click on a Record, in the Datasheet View portion of the Form, Access moves to that Record, and the previously selected Record will be Saved. The whole idea behind the development of the Split Form was to allow the user to easily view all Records and quickly select one and move to it.

About the only thing you can do, here, is to pop a Messagebox asking the user whether or not they want to Save the Current Record, and if they respond 'No,' dump the New Record or dump changes made to an Existing Record.

Here’s some boilerplate code to do that:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not (Me.NewRecord) Then
  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
Else
  If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
   Me.Undo
  End If
End If
End Sub

Linq ;0)>

[amd711]

thanks alot Missinglinq

i'v already used the "beforeupdate" event, but instead of showing a message, i used a boolean that is set to True if the update process is executed by the form view controls and False otherwise which will result in "me.undo"

looks like its the only solution