Delete record and relationship records using kill

[oxicottin]

Hello I wanted to have a "Delete Record" button on the frmVWI that would delete its main record I was on and images and records from ists subform associated with that main record using VBA but I have no clue in how to write the VBA to do such thing. Would you have to loop and use a kill to get the images accociated with the record? The subform delete image button works and it uses a kill. Thanks!

Ms Access 2007

 

[SteveSchapel]

Oxicottin,

DoCmd.RunCommand acCmdDeleteRecord

If you have a Relationship established, between the table the main form is based on and the table the subform is based on, with Cascade Delete enabled, then you need do nothing more... the related subform records will automatically be trashed.

 

[SteveSchapel]

P.S. ... The Kill method is normally used to delete files, not database records. I don't think it is applicable in this case.

 

[boblarson]

P.S. ... The Kill method is normally used to delete files, not database records. I don't think it is applicable in this case.

Steve:

Actually it is applicable. He has an actual image file in the location where he has stored the link in the table. And so he wants to delete the main record but also the child records and the actual image files for each of those child records which were in the location the text string stored.

 

[oxicottin]

Yes Bob that is exactly right! I have to use the kill to delete the images stored in my folder outside the database. Can anyone help me with this?

Thanks,
Chad

 

[boblarson]

Yes Bob that is exactly right! I have to use the kill to delete the images stored in my folder outside the database. Can anyone help me with this?

Thanks,
Chad

Chad -

I've been pondering this for some time and I think you will need to use a combination of a recordset and the kill command to do it.

So, instead of relying on the Cascade deletes, you have the delete button on your main form do this:

Private Sub CommandButtonNameHere_Click()
Dim rst As DAO.Recordset
Dim strSQL As String

If MsgBox("Are you sure you want to delete this record?" & _
"There is no way to recover this if you say 'Yes'.", vbQuestion + vbYesNo, "Confirm Delete Record") = vbYes Then

strSQL = "Select * From YourChildTable Where TheIDNameHere = " & Me!RecordIDNameHere

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF 
    If Dir(rst!YourImagePathNameHere) <> "" Then
       Kill(rst!YourImagePathNameHere)
    End If
    rst.MoveNext
Loop

strSQL = "Delete * From YourChildTable Where TheIDNameHere = " & Me!RecordIDNameHere

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.RunCommand acCmdDeleteRecord

MsgBox "Deletion Complete", vbInformation, "Delete Confirmed"

rst.Close
Set rst = Nothing

End If

End Sub

Hopefully the field names I used are explanatory enough for you to be able to substitute your real information for them.

 

[SteveSchapel]

Aha, thanks Bob. I missed that.

 

[oxicottin]

Bob thanks.... Ok I entered the info into the "hinted spots" (Thanks ) and tried it and it deleted the record and records from the sub BUT left the images still in the folder? Here is what I used:

Private Sub cmdKillDelete_Click()

Dim rst As DAO.Recordset
Dim strSQL As String

If MsgBox("Are you sure you want to delete this record?" & _
"There is no way to recover this if you say 'Yes'.", vbQuestion + vbYesNo, "Confirm Delete Record") = vbYes Then

strSQL = "Select * From tblJobSteps Where JobStepID = " & Me!VWIID

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
    If Dir(rst!ImagePath) <> "" Then
       Kill (rst!ImagePath)
    End If
    rst.MoveNext
Loop

strSQL = "Delete * From tblJobSteps Where JobStepID = " & Me!VWIID

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.RunCommand acCmdDeleteRecord

MsgBox "Deletion Complete", vbInformation, "Delete Confirmed"

rst.Close
Set rst = Nothing

End If
    
End Sub

 

[boblarson]

Put a breakpoint on the place where the Kill command is and see if it hits it and what the value of rst!ImagePath is at that point.

 

[oxicottin]

Bob im sorry but i have no clue as to what your saying....

Thanks,
Chad

 

[boblarson]

Put a breakpoint here:

    If Dir(rst!ImagePath) <> "" Then
       Kill (rst!ImagePath)
    End If

On the If Dir(rst!ImagePath) <> "" Then line and then when it stops there, just hit F8 to step through.

That way you can look to see if it is actually making it to the Kill Command.

 

[boblarson]

And just in case you aren't sure how to set a breakpoint -

http://www.btabdevelopment.com/ts/bps

 

[oxicottin]

Wow I learn someting new every day! Ok, I put the breaking point on the line you said and it never stoped it ran right threw like it would have if I didnt put a breaking point in. I wanted to see if i was doing it right so I put it on the last msgbox and it stoped there so I did do it right. So what do I do now?

Thanks,
Chad

 

[boblarson]

Well, it would appear that it is saying that the image path does not exist, so I would look at the values being returned by rst!ImagePath and see what they are saying. Do they have the full path and name (including extension)? Something is causing it to see it as not existing so that would be the place I'd be checking.

 

[oxicottin]

Bob im going to have to read about looping and rst values before i would even possibly understand what your wanting me to do. I can do basic things on Access but not to much VBA.

Thanks,
Chad

 

[boblarson]

Bob im going to have to read about looping and rst values before i would even possibly understand what your wanting me to do. I can do basic things on Access but not to much VBA.

Thanks,
Chad

Chad - did my breakpoint tutorial not help? All you need to do is to set the breakpoint like I said. Then while you are watching the VBA window with the yellow stripe where it is paused, you hit the F8 key. That advances it one step. You then, can place your cursor over the item in question to look at the value OR you can go down to the bottom of the VBA Window to the IMMEDIATE WINDOW and type in:

?rst!ImagePath

and hit enter. It will then show you the value of that just below that line. So, if rstImagePath has a value of "C:\Temp\myPic.jpg, using ?rst!ImagePath will then show

?rst!ImagePath
C:\Temp\myPic.jpg


just like that. That way you can see what the values are and then you hit F8 and again and again until the code gets to the .MoveNext part and then you can type in ?rst!ImagePath and hit enter again to see the new value.

I hope that helps. (and if you want to just let it run after that you can go hit the RUN button to let it move on.

 

[oxicottin]

Ok, Bob your explamation helped me out... Sorry im still learning! I found it wasnt giving the full path so I added the code in green and it shows the full path now BUT it only deletes the first image and doesnt loop threw to delete the other ones. Any sugestions?

Trimed code till I get this to work then I will put msgBox back in.

Private Sub cmdKillDelete_Click()

Dim rst As DAO.Recordset
Dim strTableName As String
Dim strSQL As String
[COLOR="DarkGreen"]Dim dbPath[/COLOR]
    
[COLOR="darkgreen"]dbPath = Application.CurrentProject.Path[/COLOR]
 strSQL = "Select * From tblJobSteps Where JobStepID = " & Me!VWIID
  Set rst = CurrentDb.OpenRecordset(strSQL)
    
Do Until rst.EOF
    If Dir([COLOR="darkgreen"]dbPath[/COLOR] & rst!ImagePath) <> "" Then
       Kill ([COLOR="darkgreen"]dbPath [/COLOR]& rst!ImagePath)
    End If
    
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing

End Sub

 

[boblarson]

Well, I would think it is because your SQL is only pulling back one record - the one for that particular job step, when it needs to pull all job steps for the particular MAIN ID.

 

[oxicottin]

I keep looking it over and why wouldnt it delete each image in a loop? If its hitting the first one then why wouldnt it loop and get the next? What would I change in the sql? Thanks!

 

[boblarson]

It is because you are using:

strSQL = "Select * From tblJobSteps Where JobStepID = " & Me!VWIID

I'd have to go redownload your database to see what it should be. Not sure I'll get to that anytime soon though, so you might try looking a little more deeply.