deleting record help

[Mary]

I have an invoice creation form [form] with a subform
[subform]. I have set my invoice number to increment on
opening the form. [form] and [subform] are separate
tables where the invoice number is key field in each.

My issue is when the user wishes to abandon the parent
form (and thus the child form) and does not wish to "use
up" the number. Could get very messy with invoice number
gaps.

Sometimes the user abandons the form when only [form]
data is entered; sometimes the user abandons when both
[form] and [subform] data is entered.

When only [form] data is entered, the me.undo command
clears the record for the invoice number just fine.
However, when both [form] and [subform] data have been
entered (or maybe the focus to [subform]?), the undo
command does not work for the [form] data, much less the
[subform] data.

What I ask is: given that I require continuous valid
invoice numbers, how do I "undo" BOTH the [form] and
[suborm] records? This then would clear both tables for
a valid use of that invoice number.

In hopes of some help, I thank you.
Mary

 

[stefan hoffmann]

hi Mary,

What I ask is: given that I require continuous valid
invoice numbers, how do I "undo" BOTH the [form] and
[suborm] records? This then would clear both tables for
a valid use of that invoice number.

I always calculate the continuous invoice numbers in seperate field in
the BeforeUpdate event:

Me![InvoiceNumber] = Nz(DMax("InvoiceNumber", "InvoiceTable")+1,
InitialInvoiceNumber)

--> stefan <--

 

[Mary]

I'm sorry, I don't see how that helps my situation. Its
not the incrementing that is my problem, its clearing a
record out of each of two tables.

Are you saying that by using the form's BeforeUpdate
event to increment, that the record(s) are not created?
Is that because it is the form's BeforeUpdate event?

I am using the form's BeforeInsert event. )Me!InvNum =
DMax("InvNum", "tbl 2 Invoices") + 1).

I tested and get no difference in the results: the two
tables still have the abandoned records. The only
difference is that the number is with BeforeInsert the
number is assigned and visible (I display it) on opening;
using the BeforeUpdate event its done at the change of
focus to the subform.

Appreciate the help.
Mary

-----Original Message-----
hi Mary,

What I ask is: given that I require continuous valid
invoice numbers, how do I "undo" BOTH the [form] and
[suborm] records? This then would clear both tables for
a valid use of that invoice number.

I always calculate the continuous invoice numbers in seperate field in
the BeforeUpdate event:

Me![InvoiceNumber] = Nz(DMax

("InvoiceNumber", "InvoiceTable")+1,

 

[stefan hoffmann]

hi Mary,

I'm sorry, I don't see how that helps my situation. Its
not the incrementing that is my problem, its clearing a
record out of each of two tables.

Seems that i don't understand your "undo" functionality:

What I ask is: given that I require continuous valid
invoice numbers, how do I "undo" BOTH the [form] and
[suborm] records?

The parent data is already saved, if you are entering data in the
subform. You like to undo the changes in the subform with the result,
that no child data is saved and the parent data is deleted?

I tested and get no difference in the results: the two
tables still have the abandoned records.

This definition in mind would indicate a data consistency problem.
If a valid parent dataset must have children datasets, you must check
the data consistency manually. E.g. delete abandoned parent datasets in
the OnCurrent and/or on OnClose event of your (parent) form.

--> stefan <--