[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access 2010 Delete a record

Posted on 2012-09-05
Medium Priority
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
  • 3
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38368979
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

ID: 38369043
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

ID: 38369119
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

Jeffrey Coachman earned 2000 total points
ID: 38369088
<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

ID: 38369118
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

825 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