OnChange event delete records

[Skunk]

I have an [Invoice] form with [Service] combo box. Depending on the service
selected one of six [Details] forms display for user input.

I would like the [Service] OnChange event to wipe out the [Details] of the
existing service. Each service's details are in a separate table, as is the
invoice. The tables have enforced referential integrity. I want to ensure I
delete only the displayed invoice's details records -- so the user starts all
over at the details form.

Invoice 123, service A has been previously stored in tables.

Now comes change, and user selects service B for Invoice 123.

At the OnChange event I would like to delete the Service A record for only
invoice 123.

I am confused how to do this and the best approach. Macro? VBA code?
What's the best method? This should all be transparent to the user.

Will some kind soul please advise me? I am learning (casually) and need
help.

Thanks.

 

[John W. Vinson]

I have an [Invoice] form with [Service] combo box. Depending on the service
selected one of six [Details] forms display for user input.

I would like the [Service] OnChange event to wipe out the [Details] of the
existing service. Each service's details are in a separate table, as is the
invoice. The tables have enforced referential integrity. I want to ensure I
delete only the displayed invoice's details records -- so the user starts all
over at the details form.

Invoice 123, service A has been previously stored in tables.

Now comes change, and user selects service B for Invoice 123.

At the OnChange event I would like to delete the Service A record for only
invoice 123.

I am confused how to do this and the best approach. Macro? VBA code?
What's the best method? This should all be transparent to the user.

Will some kind soul please advise me? I am learning (casually) and need
help.

Thanks.

The Change event is inappropriate: it fires *at every keystroke* in the
control. I think you want the AfterUpdate event instead, which fires when the
user has made a selection.

And I really, really DON'T think you want to permanently and irrevokably
delete all of the data about an invoice from your tables, just to move to a
different service!!!!

What is the structure of your tables? I sure hope you don't have six different
tables for the invoice details!

I *think* the solution is to have a Form based on the Services table, with a
Subform based on the Details table, using the Service as the master link
field... but without knowing more about your structure I can't be sure.

 

[Skunk]

Thanks for the response, John.

Yes, I do have 6 service details tables each with unique and numerous
service-specific items. There may have been (in the beginning) a better
approach, but I am way past that now.

My bottom line is:

From a form (based on a query using my invoice table) --
how do I delete a single record from another table (any table, actually) --
where the records have a common invoice number?

Using the AfterUpdate event, I see the difference.

Thanks,

I have an [Invoice] form with [Service] combo box. Depending on the service
selected one of six [Details] forms display for user input.

I would like the [Service] OnChange event to wipe out the [Details] of the
existing service. Each service's details are in a separate table, as is the
invoice. The tables have enforced referential integrity. I want to ensure I
delete only the displayed invoice's details records -- so the user starts all
over at the details form.

Invoice 123, service A has been previously stored in tables.

Now comes change, and user selects service B for Invoice 123.

At the OnChange event I would like to delete the Service A record for only
invoice 123.

I am confused how to do this and the best approach. Macro? VBA code?
What's the best method? This should all be transparent to the user.

Will some kind soul please advise me? I am learning (casually) and need
help.

Thanks.

The Change event is inappropriate: it fires *at every keystroke* in the
control. I think you want the AfterUpdate event instead, which fires when the
user has made a selection.

And I really, really DON'T think you want to permanently and irrevokably
delete all of the data about an invoice from your tables, just to move to a
different service!!!!

What is the structure of your tables? I sure hope you don't have six different
tables for the invoice details!

I *think* the solution is to have a Form based on the Services table, with a
Subform based on the Details table, using the Service as the master link
field... but without knowing more about your structure I can't be sure.

 

[John W. Vinson]

Thanks for the response, John.

Yes, I do have 6 service details tables each with unique and numerous
service-specific items. There may have been (in the beginning) a better
approach, but I am way past that now.

My bottom line is:

From a form (based on a query using my invoice table) --
how do I delete a single record from another table (any table, actually) --
where the records have a common invoice number?

Again:

You want to *permanently and irrevokably* destroy any business history of this
item? Many accountants would have your head in a basket for doing so.

If so, execute a Delete query in the AfterUpdate event of the combo box, using
the invoice number in the combo box as a criterion.

 

[Skunk]

True enough. Many would, but this accountant says that it ain't business
history yet. No need for us to discuss GAAP, however, I need to delete a
record. I understand full-well the record is trashed. Keeping every
keystroke for all time is not a requirement of mine.

I think I didn't make myself clear, earlier.

The invoice number is a text control on the invoice form, not in a combo box.

The service is selected from a combo box on the invoice form.

The service details form opens from a button on the invoice form and is
service-specific (1 of 6 forms will open.).

The service type may be changed anytime before the service commencement date.

The prior-to-service-change details record must be eliminated to avoid issue
downstream.
A new details record will be created from input on the new/changed service
details form.

My problem is I don't know how to code "delete the record from the service
details table for the invoice number of this invoice form."

How do I?

Thank you for the caveats and help.

 

[John W. Vinson]

True enough. Many would, but this accountant says that it ain't business
history yet. No need for us to discuss GAAP, however, I need to delete a
record. I understand full-well the record is trashed. Keeping every
keystroke for all time is not a requirement of mine.

I think I didn't make myself clear, earlier.

The invoice number is a text control on the invoice form, not in a combo box.

Irrelevant - it's in a control, doesn't much matter what kind.

The service is selected from a combo box on the invoice form.

The service details form opens from a button on the invoice form and is
service-specific (1 of 6 forms will open.).
Ok...

The service type may be changed anytime before the service commencement date.

The prior-to-service-change details record must be eliminated to avoid issue
downstream.
A new details record will be created from input on the new/changed service
details form.

My problem is I don't know how to code "delete the record from the service
details table for the invoice number of this invoice form."

Dim strSQL As String
strSQL = "DELETE * FROM " & Me!cboService & _
" WHERE InvoiceNo = " & Me!txtInvoiceNo
Currentdb.Execute strSQL, dbFailOnError

would be a start; this assumes that the code is running on a form which has a
combo box cboService for which the Value is the name of the service table, and
a textbox txtInvoiceNo containing the (numeric) invoice number.

 

[Skunk]

What ever am I missing here? That looks like it will delete the invoice
record. I want to lose the details record.

It probably is my inexperience with Access, but how does that accomplishes
this:

DELETE the matching record FROM the service details table WHERE [InvNum] in
that table matches the invoice number in [InvNum] on this form from the
invoice table.

I suffer from a little knowledge and no training.
Thanks.

 

[John W. Vinson]

What ever am I missing here? That looks like it will delete the invoice
record. I want to lose the details record.

It probably is my inexperience with Access, but how does that accomplishes
this:

DELETE the matching record FROM the service details table WHERE [InvNum] in
that table matches the invoice number in [InvNum] on this form from the
invoice table.

Well, let's break this down. Suppose that you have a service details table
named Electric (I don't know your table names of course), and that the combo
box on the form has Electric currently selected. You want to delete all
details for InvNum 123 from the service table Electric, so you've put 123 in
InvNum.

The code

Dim strSQL As String
strSQL = "DELETE * FROM " & Me!cboService & _
" WHERE InvoiceNo = " & Me!txtInvoiceNo
Currentdb.Execute strSQL, dbFailOnError

will construct a string

DELETE * FROM Electric WHERE InvoiceNo = 123

by concatenating the value of the combo box cboService - "Electric" I'm
assuming - and the textbox txtInvoiceNo - 123 - into a string named strSQL.

That DELETE query will then be executed, deleting all InvoiceNo 123 records
from the table named Electric.

 

[Skunk]

Thanks!

Using your information I now have this issue working. I do appreciate all
the help.

Skunk

What ever am I missing here? That looks like it will delete the invoice
record. I want to lose the details record.

It probably is my inexperience with Access, but how does that accomplishes
this:

DELETE the matching record FROM the service details table WHERE [InvNum] in
that table matches the invoice number in [InvNum] on this form from the
invoice table.

Well, let's break this down. Suppose that you have a service details table
named Electric (I don't know your table names of course), and that the combo
box on the form has Electric currently selected. You want to delete all
details for InvNum 123 from the service table Electric, so you've put 123 in
InvNum.

The code

Dim strSQL As String
strSQL = "DELETE * FROM " & Me!cboService & _
" WHERE InvoiceNo = " & Me!txtInvoiceNo
Currentdb.Execute strSQL, dbFailOnError

will construct a string

DELETE * FROM Electric WHERE InvoiceNo = 123

by concatenating the value of the combo box cboService - "Electric" I'm
assuming - and the textbox txtInvoiceNo - 123 - into a string named strSQL.

That DELETE query will then be executed, deleting all InvoiceNo 123 records
from the table named Electric.