The Data gets saved, But It should not.

[sanan]

Hi There
I have an application of Access as Fe and SQL as BE,
And I have a Form called “sales” in it with a subform in “sales” which is called
“Account Details” The account details is made of a comboBox and 3 TxtBoxes
2 of the txt boxes are called “Sc10” and “Sd110”.
The subForm Default View is Datasheet.
The subform has 2 other TxtBoxes, which are called “Sc” and “Sd”. In it’s Footer section. The Control Source of Sc, and Sd are “=Sum([Sc10])” and “=Sum([Sd10])” Respectively. Therefore this 2 TxtBoxes show the sum of entered values is TxtBox Sc and Sd.
The “=Sum([Sc10])” must be equal at all time to “=Sum([Sd10])”.
Inorder to sort of Automatically Update the value of “Sc”, And “Sd” I have the following Codes;

Private Sub Sc10_AfterUpdate()
Me.Recalc
End Sub

Private Sub Sd10_afterUpdate()
Me.Recalc
End Su

Now here is the problem, In order to control the entered values of the users I have the following codes in the “sales” AddNew Record Button event;

Private Sub Command155_Click()
Dim strMsg As String
Dim strTitle As String
Dim intStyle As Integer
Dim MyResp As Integer
If Me!Sd - Me!SC <> 0 Then
strMsg = "Entries are out of Balance!"
strMsg = strMsg & vbCrLf & "Press Yes, to correct it!"
strMsg = strMsg & vbCrLf & "Press No, And Lose All Data !"
strTitle = "Information"
MyResp = MsgBox(strMsg, vbYesNo + vbQuestion, strTitle)
If (MyResp = vbNo) Then
Me.Undo
DoCmd.Close
Else
Cancel = True
Me.SC.SetFocus
End If
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.ComboCustomer.SetFocus
End If
End Sub

Now here is the Problem;
After the User presses the No Button of the above MsgBox, the Data should be lost and No Records at all to be saved, But as you know because of The “Me.Recalc” in “sc10”
AfterUpdate Event.
Even if I used “me.Undo” in After VbNo is pressed, The Data does not get lost and basically it gets saved.

What is the solution for this scenario?

Best Regards
Sanan

 

[CautionMP]

First thought, remove the data binding from SD10 and SC10, this way you can change the values as much as you want and not touch the underlying data (leaving the record, uh, er, un-Dirty?), then modify your confirmation message code to move the values from the unbound controls SD10 and SC10 to the record if the user selects yes.

Hope this helps.

P.S. the DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 is redundant, the database record should save the record automatically when it looses focus.