Using VB to save data in a form

[blueraincoat]

Hi All,
I have a form(frmInventoryDecrease) based on a table, the table is for recording every inventory decrement. And on the form I have a command button, in the command click event, I made a update query using vb.(the idea comes for our forums too, thanks again...)By this time it all works fine.



Then come the problem. I add a BeforeUpdate event(using msgbox) to prevent user's misoperation like changed the decrement history, so every time after I update the inventory and go to the new record, the beforupdate event is triggered and the msgbox show up......I don't think there is a need to confirm in this condition, I tried to make the form dirty property to false right after the update query but the beforeupdate msgbox still shows up.

Any comment is appreciated!

For detail information, see the codes below.


Private Sub Command32_Click()
Dim mySQL As String
mySQL = "UPDATE tblInventoryInfo SET tblInventoryInfo.Qty= tblInventoryInfo.Qty-" & Me.Qty
mySQL = mySQL & " Where tblInventoryInfo.PartNumber='" & Me.PartNumber & "' AND tblInventoryInfo.LotNumber='" & Me.LotNumber & "'"
Select Case MsgBox("You are going to decrease the Stock Level of " & Me.PartNumber & " by " & Me.Qty _
& "." & vbCrLf & "Are you sure?" & vbCrLf & "Yes for sure,No for cancel.", vbYesNo, "Inventory Update Confirmation")
Case vbYes
CurrentDb.Execute mySQL, dbFailOnError
MsgBox ("Inventory Update Successfully Done!")
Case vbNo
MsgBox ("You have canceled the inventory update.")
End Select
If Me.Dirty Then
Me.Dirty = False
End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Error

If Me.Dirty Then
If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo
End If
End If
BeforeUpdate_Exit:
Exit Sub
BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
End Sub

[pbaldy]

The code you have probably doesn't do what you think it does. This:

Me.Dirty = False

forces a save of the record, it doesn't undo it. Typically you would not have a bound form plus code to save, unless you're updating a second table. If you need the form bound, it sounds like you want Undo there.

[blueraincoat]

Hi Paul, the second code was to save records in the a bound form, and the first is to update a filed in another tbl I think what I need is take off the second code and let the user be careful when changing from record to record.Thanks anyway.

[pbaldy]

I must have misunderstood. You're looking to avoid the before update code/message box if the user clicked on the button? One way would be to set a form level variable in the button's code. In the before update code, test that variable and only execute the before update code if it doesn't have the value set in the button.

[blueraincoat]

Thank you Paul, it's a clear idea.
I'll work on it and let you know the result.

[pbaldy]

No problem, sorry I misunderstood the question to begin with.

[blueraincoat]

Hey Paul, your idea works excellent!
I give the variable a value when the user run the update query, and the before update code and MsgBox do not popup any more as I wanted.

[pbaldy]

Excellent, glad we found a solution for you.