If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Delete Command Button not working on form
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 |
#2
|
|||
|
|||
Delete Command Button not working on form
Try this. It seems to be working for what I need it for.
This will delete the currant record and take you back to the beginning of your table. 20 DoCmd.SetWarnings False 30 DoCmd.RunCommand acCmdDeleteRecord 40 Me.Refresh 50 Me.Requery 60 DoCmd.SetWarnings True This will take you back to the point where you left off. Dim lngPos As Integer 10 lngPos = Me.Recordset.AbsolutePosition 20 DoCmd.SetWarnings False 30 DoCmd.RunCommand acCmdDeleteRecord 40 DoCmd.SetWarnings True 50 Refresh 60 Me.Requery 70 Me.Recordset.AbsolutePosition = lngPos dirtrhoads wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ccess/201005/1 |
#3
|
|||
|
|||
Delete Command Button not working on form
"dirtrhoads" wrote in message
... 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? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) |
#4
|
|||
|
|||
Delete Command Button not working on form
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" wrote: "dirtrhoads" wrote in message ... 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? -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup) . |
#5
|
|||
|
|||
Delete Command Button not working on form
"dirtrhoads" wrote in message
... 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) |
#6
|
|||
|
|||
Delete Command Button not working on form
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. "Dirk Goldgar" wrote: "dirtrhoads" wrote in message ... 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) |
Thread Tools | |
Display Modes | |
|
|