Link to home
Start Free TrialLog in
Avatar of andrewpiconnect
andrewpiconnectFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access 2010 Delete a record

Hi,

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
Else

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
    Else
        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?

Thanks
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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
    else
       ' delete the record

end if
Avatar of andrewpiconnect

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.