Dear All,
I was trying to delete all the data from different tables with link to "GroupID". Sometimes it worked when all tables contain data, other times won't work when there's no data in some of the tables. Here's the code. Please help. Thanks.
------------------------------------------------------------------
On Error GoTo ErrHandle
Dim strPasswd
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLGroupRoom As String
Dim strSQLPart As String
Dim strSQLPartBooking As String
Dim strSQLPartItinerary As String
Dim strSQLDayCity As String
Dim strSQLCityBooking As String
Dim strSQLCityPlaceToVisit As String
Dim strSQLCityBookingRoom As String
Dim strSQLHotelRoom As String
Dim strSQLRoomingList As String
Dim strSQLSubRoomingList As String
Dim strSQLClientPayment As String
Dim strSQLCoachPayment As String
Dim strSQLHotelPayment As String
Dim strSQLHotelBooking As String
Dim strSQLCoachBooking As String
strSQLGroupRoom = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID
strSQLPart = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID
strSQLPartBooking = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID
strSQLPartItinerary = "DELETE * FROM tblPartItinerary" & _
" WHERE PIGroupID = " & Me.GroupID
strSQLDayCity = "DELETE * FROM tblDayCity" & _
" WHERE DCGroupID = " & Me.GroupID
strSQLCityBooking = "DELETE * FROM tblCityBooking" & _
" WHERE CBGroupID = " & Me.GroupID
strSQLCityPlaceToVisit = "DELETE * FROM tblCityPlaceToVisit" & _
" WHERE CPTVGroupID = " & Me.GroupID
strSQLCityBookingRoom = "DELETE * FROM tblCityBookingRoom" & _
" WHERE CBRGroupID = " & Me.GroupID
strSQLHotelRoom = "DELETE * FROM tblHotelRoom" & _
" WHERE HRGroupID = " & Me.GroupID
strSQLRoomingList = "DELETE * FROM tblRoomingList" & _
" WHERE GroupID = " & Me.GroupID
strSQLSubRoomingList = "DELETE * FROM tblSubRoomingList" & _
" WHERE SRLGroupID = " & Me.GroupID
strSQLClientPayment = "DELETE * FROM tblClientPayment" & _
" WHERE ClientPaymentGroupID = " & Me.GroupID
strSQLCoachPayment = "DELETE * FROM tblCoachPayment" & _
" WHERE CPaymentGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblHotelPayment" & _
" WHERE HPaymentGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblHotelBooking" & _
" WHERE HBGroupID = " & Me.GroupID
strSQLHotelPayment = "DELETE * FROM tblCoachBooking" & _
" WHERE CBookingGroupID = " & Me.GroupID
Set db = CurrentDb
strPasswd = InputBox("Enter Temporary Password", "Restricted Form")
If strPasswd = "" Or strPasswd = Empty Then
MsgBox "No Input Provided", vbInformation, "Required Temporary Password"
Exit Sub
End If
If strPasswd = ELookup("TemporaryPass", "tblSettings", "SettingsID=" & 1) Then
If vbYes = MsgBox("Are you sure you want to delete this Group?", vbQuestion + vbYesNo + vbDefaultButton2) Then
db.Execute strSQLGroupRoom, dbFailOnError
db.Execute strSQLPart, dbFailOnError
db.Execute strSQLPartBooking, dbFailOnError
db.Execute strSQLPartItinerary, dbFailOnError
db.Execute strSQLDayCity, dbFailOnError
db.Execute strSQLCityBooking, dbFailOnError
db.Execute strSQLCityPlaceToVisit, dbFailOnError
db.Execute strSQLCityBookingRoom, dbFailOnError
db.Execute strSQLHotelRoom, dbFailOnError
db.Execute strSQLRoomingList, dbFailOnError
db.Execute strSQLSubRoomingList, dbFailOnError
db.Execute strSQLClientPayment, dbFailOnError
db.Execute strSQLCoachPayment, dbFailOnError
db.Execute strSQLHotelPayment, dbFailOnError
db.Execute strSQLHotelBooking, dbFailOnError
db.Execute strSQLCoachBooking, dbFailOnError
End If
Me.Requery
If Not (fDelCurrentRec(Me)) Then
MsgBox "An Error occurred!"
End If
Else
MsgBox "Sorry, you do not have access to this information", vbOKOnly, "Important Information"
Exit Sub
End If
ErrExit:
Exit Sub
ErrHandle:
Resume ErrExit
--------------------------------------------------------------------------------------
Zhao LiQing
Be adventurous, be bold, be careful, be a star !
------------------------------------
Posted by: Qingqing
<qingqinga@yahoo.com> ------------------------------------
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/ <*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join (Yahoo! ID required)
<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com MS_Access_Professionals-fullfeatured@yahoogroups.com <*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com <*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/ 
