please what is wrong with this code

[nutstretch]

I have some code which is supposed to delete a customer all related pets and thier related infomation and delete all future booking which belong to the customer about to be deleted.

It is deleting most of the records but does not delete the booking details records. The booking is deleted but not the records which are related to the booking.

Please look at the code and see if you can find something wrong with it


Dim nReply As Integer ' declare a reply to a message box
'show a message box checking that these details should be deleted
nReply = MsgBox("Are you sure you want to delete this customer?", vbYesNo + vbQuestion, "Confirm delete")
If nReply = vbYes Then 'if the yes button is pressed then continue
Set db = OpenDatabase(App.Path & "\kennel.mdb")
db.Execute ("delete * from tblCustomer where cust_no =" & cust_no) ' delete all from that row where that is the index no
'frmFamily.Data1.Refresh ' refresh the form and data control
Set rs = db.OpenRecordset("select * from tblpet_details where cust_no = " & cust_no)
If Not rs.EOF Then
Do While Not rs.EOF
pet_no = rs("pet_no")
db.Execute ("delete * from tblDiet where pet_no = " & pet_no)
db.Execute ("delete * from tblmedication where pet_no = " & pet_no)
db.Execute ("delete * from tblAttention where pet_no = " & pet_no)
rs.MoveNext
Loop
End If

Set rs = db.OpenRecordset("select * from qbooking where cust_no = " & cust_no)
If Not rs.EOF Then
Do While Not rs.EOF
bookNo = rs("booking_no")
db.Execute ("delete * from tblBooking_details where booking_no = " & bookNo)
db.Execute ("delete * from tblpet_addons where booking_no = " & bookNo)
rs.MoveNext
Loop
End If
db.Execute ("Delete * from tblbooking where booking_no = " & bookNo)


db.Execute ("delete * from tblpet_details where cust_no = " & cust_no)

[nutstretch]

stupid me how can you find a record in a query if you delete the custome form the table first!!!!!!

[veryjonny]

So is it solved?

[Hack]

Dim Db As Database
Dim nReply As Integer ' declare a reply to a message box
'show a message box checking that these details should be deleted
nReply = MsgBox("Are you sure you want to delete this customer?", vbYesNo + vbQuestion, "Confirm delete")
If nReply = vbYes Then 'if the yes button is pressed then continue
        Set Db = OpenDatabase(App.Path & "\kennel.mdb")
        Db.Execute ("delete * from tblCustomer where cust_no =" & cust_no) ' delete all from that row where that is the index no
        'frmFamily.Data1.Refresh ' refresh the form and data control
        Set rs = Db.OpenRecordset("select * from tblpet_details where cust_no = " & cust_no)
    If Not rs.EOF Then
        Do While Not rs.EOF
            pet_no = rs("pet_no")
            Db.Execute ("delete * from tblDiet where pet_no = " & pet_no)
            Db.Execute ("delete * from tblmedication where pet_no = " & pet_no)
            Db.Execute ("delete * from tblAttention where pet_no = " & pet_no)
            rs.MoveNext
        Loop
    End If
    
    Set rs = Db.OpenRecordset("select * from qbooking where cust_no = " & cust_no)
    If Not rs.EOF Then
        Do While Not rs.EOF
            bookNo = rs("booking_no")
            Db.Execute ("delete * from tblBooking_details where booking_no = " & bookNo)
            Db.Execute ("delete * from tblpet_addons where booking_no = " & bookNo)
        rs.MoveNext
        Loop
    End If
    Db.Execute ("Delete * from tblbooking where booking_no = " & bookNo)
    Db.Execute ("delete * from tblpet_details where cust_no = " & cust_no)

I don't see an End If for the If nReply