Access 2010 Delete a record

Posted on 2012-09-05
Last Modified: 2012-09-05

I am trying to use vba code (behind a button) to delete a passenger record shown in a form. However, there is a subform in the main form that contains "bookings" associated with this passenger and i do not wish the delete command to run if there are any bookings associated. I have tried the below code and it seems to partly work, however, it will not allow me to delete a record even if it has no booking.

If IsNull(mfrmPassenger_ID) Then ' checks to see whether form has a record at all
    Exit Sub

If MsgBox("Are you sure you wish to Delete this Passenger? You will not be able to view this record afterwards!", vbYesNo) = vbYes Then
    If Not IsNull([NewBooking_Datasheet].Form![ViewBooking]) Then ' control in subform
        MsgBox "Cannot Delete"
        'Cancel = True
        Exit Sub
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
        'DoCmd.RunCommand acCmdClose
        DoCmd.GoToRecord , , acNewRec
    End If
End If

End If

Any ideas would be welcome?

Question by:andrewpiconnect
    LVL 119

    Expert Comment

    by:Rey Obrero
    what is the record source of the subform?
    which field relates the main form and the subform?

    you can check if any records exists in booking with (assuming the field that connects the the two tables is Passenger_ID)

    if dcount("*","Bookings","Passenger_ID=" & Me.Passenger_ID)>0 then
        msgbox "Record exists in Booking, Can Not delete record"
        exit sub
           ' delete the record

    end if

    Author Comment

    I've fixed it.

    I changed the control source in the subform as the one i was previously referencing was showing (new), so the Not IsNull was always going to read this as Not Empty.

    If Not IsNull([NewBooking_Datasheet].Form![txtBookingStatus]) Then ' control in subform

    Thanks all the same

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for andrewpiconnect's comment #a38369043

    for the following reason:

    I answered the question myself and provided my answer in the thread
    LVL 74

    Accepted Solution

    <delete a passenger record shown in a form. However, there is a subform in the main form that contains "bookings" associated with this passenger and i do not wish the delete command to run if there are any bookings associated. >

    Also note that this should all be handled by Referential Integrity...
    Referential Integrity states that in a Parent/Child (one to many) relationship, you cannot delete the Parent (Customer) if a child record (a booking) exists.

    Hence, no code would be needed.

    Are you tables related properly, ...and do you have Referential Integrity enforced?
    If so then this should work automatically.
    Referential Intergrity

    Author Comment

    Actually, I had forgotten that i had amended the table relationships the other day and was wondering why the database wasnt taking care of this by itslef.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now