andrewpiconnect
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_Datashe et].Form![ ViewBookin g]) 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
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_Datashe
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
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_Datashe et].Form![ txtBooking Status]) Then ' control in subform
Thanks all the same
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_Datashe
Thanks all the same
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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","Pas
msgbox "Record exists in Booking, Can Not delete record"
exit sub
else
' delete the record
end if