[MS_AccessPros] Re: Some tables are not deleted

 

Sorry all - I forgot to "Show message history". Mea maxima culpa :)

---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :

Hi Kevin

The biggest problem with your code is in your error handler...

ErrHandle:
Resume ErrExit

If any error occurs, your procedure will just silently exit and you will have no indication that anything has gone wrong, much less what the error was, or where it occurred.

At the very least, you should have your error handler display a message:

ErrHandle:

MsgBox Err.Description
Resume ErrExit

Also, instead of declaring a different string variable for every SQL statement, I would reuse the same variable, and include that in your error message so that you can see exactly which SQL statement is failing, and why:

Dim strSQL As String

strSQL = "DELETE * FROM tblGroupRoom" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPart" & _
" WHERE GroupID = " & Me.GroupID
db.Execute strSQL, dbFailOnError

strSQL = "DELETE * FROM tblPartBooking" & _
" WHERE PBGroupID = " & Me.GroupID

db.Execute strSQL, dbFailOnError

... etc - then in your error handler:

ErrHandle:

MsgBox Err.Description & vbCrLf & strSQL
Resume ErrExit

Best wishes,

Graham [Access MVP 1996-2016]

---In MS_Access_Professionals@yahoogroups.com, <qingqinga@...> wrote :

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: graham@mandeno.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2
• New Photos 1

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___