msg box YesNoCancel - pops up twice

[Kan09]

I introduced a YesNoCancel button on the before update event of the form. So now the user gets prompted if he wants to save the record or not.
The form has a subform so if the user enters data in the main form and then clicks the subform it gets the YesNoCancel msg and decides what he wants to do.
The problem lies in the save button on the main form. When he clicks it the msg box pops up twice. Why is that?

code for save button:

Private Sub SaverecordOtherOperations_Click()
On Error GoTo Err_SaverecordOtherOperations_Click


   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   [Stop time] = Time
   [TotalTime] = Diff2Dates("hns", [Start time], [Stop time], 0)
    DoCmd.GoToRecord , , acNext
Exit_SaverecordOtherOperations_Click:
   Exit Sub

Err_SaverecordOtherOperations_Click:
  MsgBox Err.Description
  Resume Exit_SaverecordOtherOperations_Click
    
End Sub

Code for the msgbox:

rivate Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Salvati Inregistrarea?", vbYesNoCancel, "Atentie!!")
Case vbYes
  'code
  Save = True
  [Stop time] = Time
  [TotalTime] = Diff2Dates("hns", [Start time], [Stop time], 0)
Case vbNo
  'code
  Me.Undo
Case vbCancel
  'code
Cancel = True
  End Select
End Sub

[MikeTheBike]

Hi

In the 'save' button code save the record with the DoCmd, which fires the BeforeUpdate event

Private Sub SaverecordOtherOperations_Click() 
On Error GoTo Err_SaverecordOtherOperations_Click 
  
  
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 
   [Stop time] = Time 
   [TotalTime] = Diff2Dates("hns", [Start time], [Stop time], 0) 
    DoCmd.GoToRecord , , acNext 
Exit_SaverecordOtherOperations_Click: 
   Exit Sub 
  
Err_SaverecordOtherOperations_Click: 
  MsgBox Err.Description 
  Resume Exit_SaverecordOtherOperations_Click 
  
End Sub

Then update (change) two fields before moving the the next record.

Because the record has been changed the BeforeUpdate event then fires again before the move to the next record can be completed (or Cancelled!).

At least that seems the logical explanation to me !


MTB

[NeoPa]

You don't explain what [Stop time] or [TotalTime] are. If they are controls on your form then this behaviour would be expected.

On line #5 you save the data.
On lines #6 & #7 you update the form again.
On line #8 you move records, which triggers a further update, thereby triggering your Yes/No code again.

[Kan09]

[start time] when the user starts entering data into the form the time() function inserts the data into the [start time] field
[stop time] then the user cliks save the stop time uses the time() function to insert the prezent hour.
[total time] - calculates the [stop time] - [start time] trough the date2diff function and inserts it into the table.
...the stop time and the total time update the tables.
i diddn't know that the move to new record does that..dough it is kinda logic.


i'm learning vba as i go...so i don't quite know how to fix this. Help please?

[Kan09]

problem solved:

i've done a little permutation in the code:

   [Stop time] = Time
   [TotalTime] = Diff2Dates("hns", [Start time], [Stop time], 0)
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   DoCmd.GoToRecord , , acNext

I'm not prompted twice for the save button. Now i recive a ca't go to the specified record if i clik no on the prompt button...but that's normal because of the me.undo. I'll see to solving this a bit later.

Thanks for the help.

[NeoPa]

[start time] when the user starts entering data into the form the time() function inserts the data into the [start time] field

This still doesn't explain what they are (It only explain how you use them). It indicates they are fields (elements found within tables and queries) yet I'm almost convinced they are controls (objects found on forms and reports).

If so then I have already explained the problem. I would guess these are not bound controls... Actually, let's avoid guessing any further here. Please explain the situation so we know what we're dealing with, then we can more easily help find a solution that fits your requirement. Remember, the better you explain the situation, the better we are able to find a solution that matches.

[Kan09]

oohh..sorry.
They are text boxes used on the subform to display the data enterd into the table behind the main form.

[NeoPa]

That's quite straightforward then :)

Move the acSaveRecord line (#5) so that it follows the lines (#6 & #7) which update those controls. This will ensure the control updates are done to the record before it's saved, thus ensuring the prompt only occurs the once.

Let us know how you get on with this.