form/subform deleting records

[Mary]

Repost from FORMS newsgroup. No rusults there; can anyone
help me?

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

 

[PC Datasheet]

Mary,

Change the primary key in your iIvoice table to InvoiceID, make it autonumber
and make it the primary key. Change the primary key in your InvoiceDetails table
to InvoiceDetailID, make it autonumber and make it the primary key. Add a
InvoiceID field to your InvoiceDetails table and delete the InvoiceNum field you
have. Go to the table relationships screen and create a relationship between
InvoiceID in both tables. Check referential integrity and cascade delete. Keep
your code to set your invoice number to increment on
opening the form (presume you are doing it with the DMax function + 1). You are
now set up via cascade delete so when you delete a record in the main form, the
corresponding records in the subform will also be deleted. If you are using the
DMax function + 1method of assigning invoice#s, your invoice numbers will always
be sequential.

 

[Mary]

Looks like a rewrite might be in order. Thanks for the
advice, I will give it a try.

Mary

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

Change the primary key in your iIvoice table to InvoiceID, make it autonumber
and make it the primary key. Change the primary key in your InvoiceDetails table
to InvoiceDetailID, make it autonumber and make it the primary key. Add a
InvoiceID field to your InvoiceDetails table and delete the InvoiceNum field you
have. Go to the table relationships screen and create a relationship between
InvoiceID in both tables. Check referential integrity and cascade delete. Keep
your code to set your invoice number to increment on
opening the form (presume you are doing it with the DMax function + 1). You are
now set up via cascade delete so when you delete a record in the main form, the
corresponding records in the subform will also be deleted. If you are using the
DMax function + 1method of assigning invoice#s, your invoice numbers will always
be sequential.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Repost from FORMS newsgroup. No rusults there; can anyone
help me?

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

.