Consulting

Results 1 to 4 of 4

Thread: How to delete selected record in MS Access (VBA) table within a form

  1. #1

    How to delete selected record in MS Access (VBA) table within a form

    I have a view bookings page where once customers make a booking it shows their list of all bookings they have made.


    viewbookings.jpg

    The issue that I am getting is it doesn't allow me to delete a selected record and only keeps deleting the record which is on the top despite selecting another record. I inserted an error message which doesn't delete past booking dates because of keeping them as historical records. I only want to be able to delete future tense bookings. How do i delete selected records?

    This is my current coding to delete a booking

    Private Sub btnDelete_Click()
    
    
    If Day <= Now Then
    MsgBox "Past booking dates are for historical records and cannot be deleted"
    Else
    Msg = "Are you sure you want to cancel booking"
       Style = vbYesNo
       Response = MsgBox(Msg, Style)
       If Response = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        Exit Sub
       End If
       If Response = vbNo Then Me.Undo
          Exit Sub
        End If
    Exit_ErrHandler:
    Exit Sub
    End If
    End Sub

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I don't code Access, but logic tells me that you just need one more field: Status, which can have three values: Null, Kept, and Canceled.

    Instead of deleting future bookings, set them to Canceled and exclude all but Nulls from your booking page.

    Some day you might want to Query Client > Bookings > Canceled. It's good to have a permanent record.

    nb: you might want to use "Future" vice "Null."
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I agree with SamT on this about the extra field.
    The Form's underlying Query can then be used to filter out any records set to "cancelled".
    If you must delete them then use the field as a tick box to set to "Delete" and then run a delete query to remove any ticked records.

    Your code looks as if it should delete the currently selected record.

  4. #4
    To delete the data that is currently in a table, you use the DELETE statement, which is commonly referred to as a delete query. This is also known as truncating a table. The DELETE statement can remove one or more records from a table and generally takes this form:
    DELETE FROM table list
    Recently I caught into a duplicate record issue in my access 2016 database. So, if you have any such problem then do try out this fixes:

Tags for this Thread

Posting Permissions

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