If not saved, do not create new record?

[CW]

I have a cmd button that opens an invoice form in add mode, and allocates an
invoice number via autonumbering.
If the invoice creation is aborted for some reason (i.e. if the form is
closed without the Save button having been clicked) the skeleton of the
invoice is still added and the number is used and I have a meaningless record
in tblInvoices.
Is there some way I can prevent this?
If it means that I have to add another cmd button "Cancel" that undoes
things, rather than users just hitting the close button, that's fine...
Many thanks
CW

 

[John W. Vinson]

I have a cmd button that opens an invoice form in add mode, and allocates an
invoice number via autonumbering.

Autonumbers are NOT suitable for this purpose. The very instant you dirty the
record, a new autonumber is assigned and permanently "used up". You'll be left
with gaps in the numbering system; and for some reason auditors tend to get
freaked out by such things!

You're really better off using a Long Integer and assigning the value by VBA
code in your Form. This can be easy or moderately complicated depending on
whether it's a single user system or one with multiple users generating new
invoices concurrently.

If the invoice creation is aborted for some reason (i.e. if the form is
closed without the Save button having been clicked) the skeleton of the
invoice is still added and the number is used and I have a meaningless record
in tblInvoices.

You added the save button yourself I take it?? That's not builtin.

Is there some way I can prevent this?

Use the Form's BeforeUpdate event, which can be cancelled if some required
fields are null:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns as Integer
If IsNull(Me!CustomerID) Then
Cancel = True
iAns = MsgBox("No Customer selected; cancel invoice?", vbYesNo)
If iAns = vbYes Then
Me.Undo ' erase the record
Else
MsgBox "Fill in all required fields before closing the form", vbOKOnly
End IF
End If
End Sub

If it means that I have to add another cmd button "Cancel" that undoes
things, rather than users just hitting the close button, that's fine...

A Cancel button can just run Me.Undo, but as noted above that will NOT rescue
your autonumber value. It's too late.

John W. Vinson [MVP]

 

[CW]

John - I appreciate your detailed advice and words of warning. I will try to
redesign this as you have suggested.
Many thanks
CW