Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete Problem (Access XP)

    I have a form that acts like an invoice that contains a subform which contains the items for the invoice. I have a command button on the form that I want to use to cancel the order. The effect of clicking on this button should either Undo the record or Delete the record. I have not been able to get the button to delete the record once it is written to the table (once the user utilizes the subform, the Order_ID autonumbers to the next number and the record is written. I want to be able to utilize the button to either undo the record or delete it based on whether or not txtOrder_ID is Null or has an order number. The control source for txtOrder_ID is tblOrders.Order_ID. I currently only have the code for the Undo function in the Click event:

    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    DoCmd.Close

    Exit_cmdCancel_Click:
    Exit Sub

    Err_cmdCancel_Click:
    MsgBox Err.Description
    Resume Exit_cmdCancel_Click
    End Sub

    I have tried creating a Delete Query which runs based on [forms]![frmMain]![txtOrder_ID] as the criteria, but this did not work either when I utilized the docmd.openQuery and referenced the action query. It told me I was deleting 0 rows.

    I tried using docmd.runSql and used "DELETE FROM tblOrders WHERE tblOrders.Order_ID = [Forms]![frmMain]![txtOrder_ID];"

    This did not work either.

    Any assistance is appreciated.

    Thanks.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Delete Problem (Access XP)

    There are some challenges in doing this sort of thing - any time you go into a subform if the main form is dirty, it will save the main record. So you have to be very cognizant of where focus on the form is. In general the UnDo command will only undo the last typing in a field, or if you do two, it will undo all changes to the current record. In effect, the Undo command is the same as hitting the ESC key twice. You should also be concerned about referential integrity here - you don't want people deleting a record which has records in the items table. Otherwise you end up with orphaned records in the items table.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Arkansas, USA
    Posts
    163
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete Problem (Access XP)

    Thanks for the advice.

    I have decided to add another column to tblOrders called OrderStatus. I will use this to track Open, Closed, or Cancelled. I will resort to utilizing the Cancel Order cmd button to mark this column as Cancelled and have changed the query that populates a list box with the open orders to query for Open in the OrderStatus column. In retrospect it may be a good idea to keep track of the cancelled orders as well. I can then later delete them with a delete query or just keep them around for a specified period of time.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •