Message Box popping up twice

G

Guest

I've been dealing with an issue for quite some time now that I can't resolve.
I have a form that calculates 'Quantity On Hand' based on 'Quantity
Dispersed' and 'Original Quantity'. I also have a function that asks the
user if they want to save changes before an update. In order to get the
'Quantity On Hand' to update properly, I perform a Me.Refresh when the field
loses focus. At this point, the message box pops up asking if the user wants
to save changes. If you then proceed to the next record or close the form
without making any other changes, the message box pops up again. Here is the
relevant code, please advise how I may resolve this issue...

Private Sub Form_BeforeUpdate(Cancel As Integer)
[TxtInitials] = CurrentUser()
[TxtLastUpdated] = Now()

On Error GoTo ErrHandler

Dim answer As Integer

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_BeforeUpdate( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Private Sub TxtQtyDispersed_LostFocus()
Me.Refresh
Me.TxtQtyOnHand = TxtOriginalQty - DSum("[QtyDispersed]", "API_SUBLOT",
"[TxtLotNumber]=[LotNumber]")
End Sub
 
G

Guest

It sounds like the form is still being perceived as dirty.
Before exiting the Form_BeforeUpdate subroutine, perhaps inserting

Me.Dirty=False

will solve your problem.

e.g.

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
Me.Dirty = False
End If

Hope that works.

Marvin
 
G

Guest

I tried this solution and I get an error stating that the function set
prevents the data from being saved. I also tried the Me.Dirty=False in a
couple other places, but to no avail. Any other ideas?

Marvin said:
It sounds like the form is still being perceived as dirty.
Before exiting the Form_BeforeUpdate subroutine, perhaps inserting

Me.Dirty=False

will solve your problem.

e.g.

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
Me.Dirty = False
End If

Hope that works.

Marvin

Zaz said:
I've been dealing with an issue for quite some time now that I can't resolve.
I have a form that calculates 'Quantity On Hand' based on 'Quantity
Dispersed' and 'Original Quantity'. I also have a function that asks the
user if they want to save changes before an update. In order to get the
'Quantity On Hand' to update properly, I perform a Me.Refresh when the field
loses focus. At this point, the message box pops up asking if the user wants
to save changes. If you then proceed to the next record or close the form
without making any other changes, the message box pops up again. Here is the
relevant code, please advise how I may resolve this issue...

Private Sub Form_BeforeUpdate(Cancel As Integer)
[TxtInitials] = CurrentUser()
[TxtLastUpdated] = Now()

On Error GoTo ErrHandler

Dim answer As Integer

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_BeforeUpdate( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Private Sub TxtQtyDispersed_LostFocus()
Me.Refresh
Me.TxtQtyOnHand = TxtOriginalQty - DSum("[QtyDispersed]", "API_SUBLOT",
"[TxtLotNumber]=[LotNumber]")
End Sub
Click to expand...
Click to expand...
 
G

Guest

Zaz,

My approach at this point would be to break it down as much as possible.
This advice is possibly too basic, but this is what I would do:

1. Make a copy of the form or reproduce a similar form
2. Remove all except the minimal controls and events
3. Ensure you have a form that is at it's simplest design and doesn't
produce the problem
4. Re-incorporate the processes into the events for the test form (maybe one
at a time)
5. If you can determine from this test form, where the problem exists then,
hopefully, you can conclude what you can do to the production form.

I hope that helps,

~Marvin

Zaz said:
I tried this solution and I get an error stating that the function set
prevents the data from being saved. I also tried the Me.Dirty=False in a
couple other places, but to no avail. Any other ideas?

Marvin said:
It sounds like the form is still being perceived as dirty.
Before exiting the Form_BeforeUpdate subroutine, perhaps inserting

Me.Dirty=False

will solve your problem.

e.g.

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
Me.Dirty = False
End If

Hope that works.

Marvin

Zaz said:
I've been dealing with an issue for quite some time now that I can't resolve.
I have a form that calculates 'Quantity On Hand' based on 'Quantity
Dispersed' and 'Original Quantity'. I also have a function that asks the
user if they want to save changes before an update. In order to get the
'Quantity On Hand' to update properly, I perform a Me.Refresh when the field
loses focus. At this point, the message box pops up asking if the user wants
to save changes. If you then proceed to the next record or close the form
without making any other changes, the message box pops up again. Here is the
relevant code, please advise how I may resolve this issue...

Private Sub Form_BeforeUpdate(Cancel As Integer)
[TxtInitials] = CurrentUser()
[TxtLastUpdated] = Now()

On Error GoTo ErrHandler

Dim answer As Integer

If (Me.Dirty) Then
answer = MsgBox("The record has been modified." & vbCrLf & _
"Do you wish to save changes?", vbYesNo, "Save Changes?")

If (answer = vbNo) Then
Me.Undo
End If
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_BeforeUpdate( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub


Private Sub TxtQtyDispersed_LostFocus()
Me.Refresh
Me.TxtQtyOnHand = TxtOriginalQty - DSum("[QtyDispersed]", "API_SUBLOT",
"[TxtLotNumber]=[LotNumber]")
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Form not updating immediately 5
dirty (stinking) changes 6
Weird message box question 1
Problem with bound combo box 3
Modify Message Box Cancel Button 6
Required fields at form level 1
Unwanted Message Box 2
Form Question "Continued" 2

Top