Remove only All the attachments from Table Attacment field

[ItsMe]

Let's take care of one issue that is not associated with the error you are getting.



The following has a .MoveFirst method inside of a loop. Let's fix that now.

     '=========================
    Do While Not rst2.EOF
    '===============================
    If rst2.EOF = True Then GoTo exit_proc
    
    rst2.MoveFirst
    'If intPic > 0 Then rst2.Move intPic
    
    rst2.Delete
    'Update the parent record
    
    rst1.Update
    '====================
   rst1.MoveNext
        Loop
   '======================

We need to move it outside the loop, before the loop. So let's do the recordset validation and the MoveFirst before the loop. Need to validate that the recordset holds actual records before we tell it to .MoveFirst. It ca not .MoveFirst without first retrieveing some records.


You will still get an error with the following but we need to take small steps.

     '=========================
    rst2.OpenRecordset
    If rst2.EOF = True Then GoTo exit_proc
    rst2.MoveFirst
     '=========================
    'If intPic > 0 Then rst2.Move intPic
    Do While Not rst2.EOF
    '===============================
    rst2.Delete
    
    'Update the parent record
    rst1.Update
    '====================
   rst1.MoveNext
        Loop
   '======================

So let's comment out the loop and see if this is the differnce between getting an error or not, for now.

     '=========================
    rst2.OpenRecordset
    If rst2.EOF = True Then GoTo exit_proc
    rst2.MoveFirst
     '=========================
    'If intPic > 0 Then rst2.Move intPic
'    Do While Not rst2.EOF
    '===============================
    rst2.Delete
    
    'Update the parent record
    rst1.Update
    '====================
   rst1.MoveNext
'        Loop
   '======================

[aspen]

Well as for the function as june7 said I found out that I had a function with the same name. so I changed the name and It works for one record If I type the name Call RemoveAttachment6(Filename.mp4). But when I use it like RemoveAttachment6(me.File). where file is the field where the file name is in "Filename.mp4". it doesn't work. How to give the function the fields value.

[ItsMe]

You spend a lot of energy trying to paste code into your procedures that you find here and there. I believe that if you used that same time and energy in trying to understand the code, even a little, you will be far better off in the long run. Perhaps it is boring to you. I can tell you it is definitely tedious. Either you have the patience or you do not. There is not any instant gratification in VBA.

You are still far from a solution here.

[aspen]

Hi Its me yes We are getting there. Now I get the error for object variable or block variable being not set. thanks

[aspen]

Yes you are right its me. Other codes I slightly understand. But this one is difficult cause we have to think about two rcordsets. But with your help I am picking up slowly. thanks for that. Now whats object variable and block variable?

[ItsMe]

I would not expect that (error). We still need to address the issue of rst2 becoming invalid but removing the loop should have avoided the problem. Part of the reason why I am dragging you through this process is so you can see how to isolate errors, address the error, and fix it.

Can you post the code you used last?

[aspen]

On Error GoTo err_proc
Dim strSQL As String
Dim intPic As Integer
    DoCmd.RunCommand acCmdSaveRecord
    Me.Refresh        'New line
    Me.Files.Requery        'New line
    intPic = Me.Files.CurrentAttachment
'  Instantiate the parent recordset.
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef, rst1 As DAO.Recordset, rst2 As DAO.Recordset
    strSQL = "SELECT Files FROM Table1 WHERE ID=" & Me.ID
    
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", strSQL)
    Set rst1 = qdf.OpenRecordset
    If rst1.EOF = True Then GoTo exit_proc
    rst2.MoveFirst
    rst2.Edit
   ' Instantiate the child recordset.
   
    Set rst2 = rst1.Fields("Files").Value
   
    rst2.OpenRecordset
     
    If rst2.EOF = True Then GoTo exit_proc
    
    rst2.MoveFirst
    
    'If intPic > 0 Then rst2.Move intPic
    '=========================
    Do While Not rst2.EOF
    '===============================
    
    rst2.Delete
    'Update the parent record
    
    rst1.Update
    '====================
   rst1.MoveNext
        Loop
   '======================
  
    Me.Files.Requery
    DoCmd.RunCommand acCmdSaveRecord
exit_proc:
On Error Resume Next
    rst2.Close
    rst1.Close
    qdf.Close
    Set db = Nothing
    Exit Sub
err_proc:
    MsgBox Err.Description
    Resume exit_proc
    '===============================
    DoCmd.RunCommand acCmdSaveRecord
    Me.Refresh        'New line
    Me.Files.Requery        'New line
    '=======================================

Thanks here it is

[ItsMe]

It is good practice to not delete code and only comment it out. If you feel comfortable with it, go ahead and delete the If Then line ('If intPic > 0 Then rst2.Move intPic). THis is in an effort to have code that is a little more easy to read.

As I mentioned, I believe we can avoid the error by commenting out the loop. Grab the third example in post#16 to comment out the loop. The difference between the second code example and the third code example should be the difference in getting the error.

[aspen]

Hi Its me thanks. I tried But Not yet working for me. I gotta go for now. I 'll come back tomorrow. thanks for your cooperation

[ItsMe]

OK, meanwhile I will take a look and see why the code may be giving that error.

[ItsMe]

When you get back tomorrow, comment out this line
'On Error GoTo err_proc

Then run the debugger and see where it errors.

[aspen]

Hi june as you pointed out in post#7 I had two functions with the same name and it works fine now. Thank you all for your help.

[June7]

Glad you have a solution. I tested the function in the first post. Minor edit so it deleted every attachment and it worked perfectly.

[aspen]

DoCmd.GoToRecord , "", acFirst
Dim rs As DAO.Recordset
Dim rsCount As Integer
Dim i As Integer
'replace "Subform" with the name of your subform
Set rs = Me.FilePhotoSearcgFr.Form.RecordsetClone
'Set rs = Me.RecordsetClone

rsCount = rs.RecordCount - 1
For i = 1 To rsCount
    'execute code for each record in the subform
    'MsgBox rsCount
    'MsgBox "hi"
    DoCmd.GoToRecord , "", acNext
    
    Call RemoveAttachment6(Me.File.Value)
Next

Yeah thanks. it Did. But I didn't want to delete the first record so I changed the function name and use it like this. I deletes all attachments accept the first. Here is the code I used.