Sabtu, 29 Desember 2018

Re: [MS_AccessPros] Some tables are not deleted

 

Zhao LiQing,
You should be more descriptive than "won't work".

Are you deleting from the lowest level of the relationships first and then stepping up to higher levels?

Regards,
Duane


From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Qingqing qingqinga@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 29, 2018 9:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Some tables are not deleted
 
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/

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

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.


SPONSORED LINKS
.

__,_._,___

Tidak ada komentar:

Posting Komentar