Delete Command Button not working on form

[dirtrhoads]

I have a Delete Command Button on a form which does not appear to be working
properly.

I originally had a delete button macro created by the command button wizard
and it seemed to be functioning properly... from the form it would ask if you
want to delete the record permanently. If I chose yes, it would appear to
delete it, meaning that if I were to search for the record to verify it would
say that the record does not exist. Upon closing the form and viewing the
table, the record would not actually be deleted and would then be back on the
form when I would reopen it.

So, I browsed through the discussion board and found a suggestion on a
couple of posts and tried that as well creating the delete button in the code
builder using the following code:

Private Sub Command233_Click()
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If
End Sub

But, this does the same thing. It does not delete the record from the
primary table.

Can someone please provide a suggestion to how I can get this to work
properly?

Thank you,
Amy

 

[Dirk Goldgar]

I have a Delete Command Button on a form which does not appear to be
working
properly.

I originally had a delete button macro created by the command button
wizard
and it seemed to be functioning properly... from the form it would ask if
you
want to delete the record permanently. If I chose yes, it would appear to
delete it, meaning that if I were to search for the record to verify it
would
say that the record does not exist. Upon closing the form and viewing the
table, the record would not actually be deleted and would then be back on
the
form when I would reopen it.

So, I browsed through the discussion board and found a suggestion on a
couple of posts and tried that as well creating the delete button in the
code
builder using the following code:

Private Sub Command233_Click()
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If
End Sub

But, this does the same thing. It does not delete the record from the
primary table.

Can someone please provide a suggestion to how I can get this to work
properly?

Thank you,
Amy

Hi, Amy -

Is the recordsource of your form, by any chance, a query that joins two
tables that have a one-to-many relationship? If the recordsource is a
query, could you post the SQL of the query?

 

[dirtrhoads]

I tried the above suggestion and it did not work... but I might have pasted
it into the wrong place... should go in the code builder? between private sub
and end sub?

And no, the form is not based only on a query, it actually combines many
fields from many tables. The Record source is the following:

SELECT Orders.*, Distributors.Price_Per_Box,
Distributors.Company_Shipping_Address, Distributors.Company_City,
Distributors.Company_State, Distributors.Company_Zip,
Distributors.Company_Billing_Address, Distributors.Company_Billing_City,
Distributors.Company_Billing_State, Distributors.Company_Billing_Zip,
Distributors.Payment_Terms, Invoice.[Payment Type], Invoice.[Date Paid],
Invoice.[AAE Invoice#], Invoice.[Invoice Attachment], Invoice.[Invoice Date],
Distributors.[Billing Contact], Distributors.Contact_Phone_Number FROM
(Distributors RIGHT JOIN Orders ON Distributors.ID=Orders.Distributor) LEFT
JOIN Invoice ON Orders.Order_ID=Invoice.[Order ID];

There are also subforms, but I have ensured that the relationships are
correct and that "cascade delete related records" has been checked on all
related tables. when I delete the order form the orders table it deletes all
related records as it should. I just can't get the delete command button to
work on the form.

Thank you,
Amy

 

[Dirk Goldgar]

I tried the above suggestion and it did not work... but I might have pasted
it into the wrong place... should go in the code builder? between private
sub
and end sub?

And no, the form is not based only on a query, it actually combines many
fields from many tables. The Record source is the following:

SELECT Orders.*, Distributors.Price_Per_Box,
Distributors.Company_Shipping_Address, Distributors.Company_City,
Distributors.Company_State, Distributors.Company_Zip,
Distributors.Company_Billing_Address, Distributors.Company_Billing_City,
Distributors.Company_Billing_State, Distributors.Company_Billing_Zip,
Distributors.Payment_Terms, Invoice.[Payment Type], Invoice.[Date Paid],
Invoice.[AAE Invoice#], Invoice.[Invoice Attachment], Invoice.[Invoice
Date],
Distributors.[Billing Contact], Distributors.Contact_Phone_Number FROM
(Distributors RIGHT JOIN Orders ON Distributors.ID=Orders.Distributor)
LEFT
JOIN Invoice ON Orders.Order_ID=Invoice.[Order ID];

That *is* a query. It is a query that is implemented as a SQL statement in
the form's recordsource property, as opposed to a stored query that is
accessed by name, but it's a query nonetheless.

It looks to me like the tables involved here have a 1-to-many-to-many
relationship: one distributor to (potentially) many orders for that
distributor to (potentially) many invoices for that order. With a form
based on a query like the above, the only record that the form will really
delete will be the invoice record. The Invoice table is the only table in
the query whose records are known by Access to be unique in the query
results.

If your intention is to delete the Orders record from the form, you can go
about either of two ways:

1. Remove the Invoice table from the recordsource query, along with any
controls on the main form that are bound to fields from that table. Then
show the invoice information on a subform. If you need to show line item
records from an invoice details table, have them as a subform on the invoice
subform.

-- OR --

2. Set the form's AllowDeletions property to No, but put a command button on
the form that runs a delete query to delete the Orders record whose [Order
ID] is current on the main form, and then requeries the form. The code for
such a button would be something like this:

'------ start of example code ------
Private Sub cmdDelete_Click()

If Me.Dirty Then
Me.Undo
End If

If Not IsNull(Me![Order ID]) Then

CurrentDb.Execute _
"DELETE FROM Orders WHERE [Order ID] = & Me![Order ID], _
dbFailOnError

Me.Requery

End If

End Sub
'------ end of example code ------

Since your relationships have cascading deletes specified, that ought to
take care of related invoice or invoice-detail records.

Either of those two alternatives ought to solve the problem.

 

[dirtrhoads]

I had to create the invoice subform. I tried your second option first but I
could not get that one to work. It took me a little while to do the other,
but it is working now.

Thank you very much for your assistance.

I tried the above suggestion and it did not work... but I might have pasted
it into the wrong place... should go in the code builder? between private
sub
and end sub?

And no, the form is not based only on a query, it actually combines many
fields from many tables. The Record source is the following:

SELECT Orders.*, Distributors.Price_Per_Box,
Distributors.Company_Shipping_Address, Distributors.Company_City,
Distributors.Company_State, Distributors.Company_Zip,
Distributors.Company_Billing_Address, Distributors.Company_Billing_City,
Distributors.Company_Billing_State, Distributors.Company_Billing_Zip,
Distributors.Payment_Terms, Invoice.[Payment Type], Invoice.[Date Paid],
Invoice.[AAE Invoice#], Invoice.[Invoice Attachment], Invoice.[Invoice
Date],
Distributors.[Billing Contact], Distributors.Contact_Phone_Number FROM
(Distributors RIGHT JOIN Orders ON Distributors.ID=Orders.Distributor)
LEFT
JOIN Invoice ON Orders.Order_ID=Invoice.[Order ID];

That *is* a query. It is a query that is implemented as a SQL statement in
the form's recordsource property, as opposed to a stored query that is
accessed by name, but it's a query nonetheless.

It looks to me like the tables involved here have a 1-to-many-to-many
relationship: one distributor to (potentially) many orders for that
distributor to (potentially) many invoices for that order. With a form
based on a query like the above, the only record that the form will really
delete will be the invoice record. The Invoice table is the only table in
the query whose records are known by Access to be unique in the query
results.

If your intention is to delete the Orders record from the form, you can go
about either of two ways:

1. Remove the Invoice table from the recordsource query, along with any
controls on the main form that are bound to fields from that table. Then
show the invoice information on a subform. If you need to show line item
records from an invoice details table, have them as a subform on the invoice
subform.

-- OR --

2. Set the form's AllowDeletions property to No, but put a command button on
the form that runs a delete query to delete the Orders record whose [Order
ID] is current on the main form, and then requeries the form. The code for
such a button would be something like this:

'------ start of example code ------
Private Sub cmdDelete_Click()

If Me.Dirty Then
Me.Undo
End If

If Not IsNull(Me![Order ID]) Then

CurrentDb.Execute _
"DELETE FROM Orders WHERE [Order ID] = & Me![Order ID], _
dbFailOnError

Me.Requery

End If

End Sub
'------ end of example code ------

Since your relationships have cascading deletes specified, that ought to
take care of related invoice or invoice-detail records.

Either of those two alternatives ought to solve the problem.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)