Delay Record Updates

[charles.kendricks]

I would like to delay records from being updated from a form until I
click a certain button. I have a form which serves as a sales form and
I don't want the sales information to be updated until and only if I
click the button to generate a receipt. In other words, if the sales
person should close the form without generating a receipt, the sales
information is not updated in the underlying tables.

 

[SusanV]

Hi Charles,

Create your form using unbound controls, then use a SQL statement to insert
or update the record on the button's On Click Event.

 

[SusanV]

Hi Charles,

Create your form using unbound controls, then use a SQL statement to insert
or update the record on the button's On Click Event.

 

[BruceM]

A record is saved when you navigate away from it, close the database, or
explicitly save it.

One way to do this is to use the form's Before Update event to verify that
the receipt has been printed. Here is a way to do this. I don't know if it
is the best way, but it is fairly compact, and seems to work.

In the form's General declarations (VBA editor):

' blnReceipt is set to False in the form's Current event.
' Clicking the button to print the receipt sets blnReceipt to True.
' The form's Before Update event checks blnReceipt. If it is False,
' the user is given the choice of returning to the record to print the
receipt.
' If it is True, the receipt has been printed, and the record is saved.
Dim blnReceipt as Boolean

In the form's Current event

' Sets a Boolean to False. The condition of blnReceipt
' is tested in the form's Before Update event to determine
' if the record is to be saved
blnReceipt = False

In the receipt command button (cmdReceipt) Click event:

' If blnReceipt is true the receipt has been printed, and the record may
be saved
blnReceipt = True
(Code to print the receipt)

In the form's Before Update event:

' blnReceipt is False unless a receipt has been printed
If Me.NewRecord And blnReceipt = False Then
If msgbox("Print a receipt?",vbYesNo,"Receipt Warning") = vbYes Then
Me.cmdReceipt.SetFocus
Cancel = True
Else
Me.Undo
End If
End If

This code applies only to new records, otherwise any change to an existing
record would generate the message box.

By way of a little more detail, a Boolean is a Yes/No variable, in the same
way as a check box is a Yes/No field. It can be set to either True or
False. Other events (such as Before Update) can test the Boolean, and run
code accordingly.

 

[BruceM]

A record is saved when you navigate away from it, close the database, or
explicitly save it.

One way to do this is to use the form's Before Update event to verify that
the receipt has been printed. Here is a way to do this. I don't know if it
is the best way, but it is fairly compact, and seems to work.

In the form's General declarations (VBA editor):

' blnReceipt is set to False in the form's Current event.
' Clicking the button to print the receipt sets blnReceipt to True.
' The form's Before Update event checks blnReceipt. If it is False,
' the user is given the choice of returning to the record to print the
receipt.
' If it is True, the receipt has been printed, and the record is saved.
Dim blnReceipt as Boolean

In the form's Current event

' Sets a Boolean to False. The condition of blnReceipt
' is tested in the form's Before Update event to determine
' if the record is to be saved
blnReceipt = False

In the receipt command button (cmdReceipt) Click event:

' If blnReceipt is true the receipt has been printed, and the record may
be saved
blnReceipt = True
(Code to print the receipt)

In the form's Before Update event:

' blnReceipt is False unless a receipt has been printed
If Me.NewRecord And blnReceipt = False Then
If msgbox("Print a receipt?",vbYesNo,"Receipt Warning") = vbYes Then
Me.cmdReceipt.SetFocus
Cancel = True
Else
Me.Undo
End If
End If

This code applies only to new records, otherwise any change to an existing
record would generate the message box.

By way of a little more detail, a Boolean is a Yes/No variable, in the same
way as a check box is a Yes/No field. It can be set to either True or
False. Other events (such as Before Update) can test the Boolean, and run
code accordingly.

 

[Svetlana]

Or if you wanna avoid saving data if user closes the form then try this
on form's close event.

If Me.Dirty = False Then
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

You may want turn back warnings on to the current event maybe.

 

[Svetlana]

Or if you wanna avoid saving data if user closes the form then try this
on form's close event.

If Me.Dirty = False Then
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
End If

You may want turn back warnings on to the current event maybe.