Prevent sequential number being used if record not saved

[CW]

I have an invoice form that when opened, uses the following code to allocate
the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if I
click on the Close button rightaway without having entered anything anywhere,
the invoice number is still used and appears in the Invoices table (without
any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at any
point before hitting the Save button, even if I have populated a few
controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

 

[Jeff Boyce]

What data type is on that [InvNo] field in the underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CW]

Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but
John Vinson recently pointed out to me how unreliable that would be, so I
have changed it
Thanks
CW

What data type is on that [InvNo] field in the underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice form that when opened, uses the following code to
allocate
the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if I
click on the Close button rightaway without having entered anything
anywhere,
the invoice number is still used and appears in the Invoices table
(without
any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at any
point before hitting the Save button, even if I have populated a few
controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

 

[Jeff Boyce]

Perhaps what you are seeing is because you are using the BeforeInsert event
rather than the BeforeUpdate event?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but
John Vinson recently pointed out to me how unreliable that would be, so I
have changed it
Thanks
CW

What data type is on that [InvNo] field in the underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice form that when opened, uses the following code to
allocate
the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if I
click on the Close button rightaway without having entered anything
anywhere,
the invoice number is still used and appears in the Invoices table
(without
any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at
any
point before hitting the Save button, even if I have populated a few
controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

 

[Bob Quintal]

I have an invoice form that when opened, uses the following code
to allocate the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even
if I click on the Close button rightaway without having entered
anything anywhere, the invoice number is still used and appears in
the Invoices table (without any values in any of the fields, of
course).

How can I prevent this? Ideally I would like to be able to cancel
at any point before hitting the Save button, even if I have
populated a few controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

me.dirty = False forces a save to the record, remove the statement.
It may also be appearing elsewhere and causing the problem so check
other sub procedures in the form module..

 

[Harvey Thompson]

I have an invoice form that when opened, uses the following code to
allocate the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if
I click on the Close button rightaway without having entered anything
anywhere, the invoice number is still used and appears in the
Invoices table (without any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at
any point before hitting the Save button, even if I have populated a
few controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

CW,
"Me.Dirty = False" saves the new record

 

[CW]

Many thanks to all for your assistance.
I resolved it by moving the code to the InvNo control, rather than the form,
and by changing the event to BeforeUpdate.
Working nicely now
Thanks again!
CW

Perhaps what you are seeing is because you are using the BeforeInsert event
rather than the BeforeUpdate event?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but
John Vinson recently pointed out to me how unreliable that would be, so I
have changed it
Thanks
CW

What data type is on that [InvNo] field in the underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice form that when opened, uses the following code to
allocate
the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if I
click on the Close button rightaway without having entered anything
anywhere,
the invoice number is still used and appears in the Invoices table
(without
any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at
any
point before hitting the Save button, even if I have populated a few
controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW

 

[david]

Me.Dirty = False

That line saves it to the table.

I suggest that you bind the form to a local table, and
only copy the record to the database when you are
finished.

Alternatively, you can use an unbound form, and only
copy the record to the database when you are finished.

Alternatively, take out the Me.Dirty = false line, and
leave the record unsaved as long as you can.

(david)