Error 3020 using fso.FileExists

[usmcgrunt]

I have what seems to be a very simple block of code on a form event that verifies if a students picture exists and if it doesn't exist, the code places a generic 'no photo on file' image in place of the actual picture. I am not actually storing the image in the database; rather I am storing a link to the image in the underlying table. Now when I select a student in the listbox, PersDirectory, I get the error

3020 - Update or CancelUpdate without AddNew or Edit.

So the end result is that I am not able to change the image file associated with each record.

Here is the code in question.



txtPhotoLink = a text control on the form that stores the location of the photo. When a new student is added to the database a txt string is automatically created for the filename of his/her photo. For example if I add a new student and her last name is Jones and the last four of her SSN is 1234, then a text string of CurrentProject.Path & "\personnelPhotos\JO1234.jpg" is created and stored in txtPhotoLink. However, if we have not received her photo and stored it in CurrentProject.Path & "\personnelPhotos\" prior to adding her to the database, then CurrentProject.Path & "\personnelPhotos\noPhoto.jpg should replace CurrentProject.Path & "\personnelPhotos\JO1234.jpg in txtPhotoLink. Subsequently, when we receive her photo, there is a button on the form to open and link her photo to her record. The code behind this button then replaces CurrentProject.Path & "\personnelPhotos\noPhoto.jpg" with CurrentProject.Path & "\personnelPhotos\JO1234.jpg" in txtPhotoLink.

Private Sub PersDirectory_AfterUpdate()

On Error GoTo ErrorHandler

     Dim fso As New FileSystemObject
     Dim strNoPhoto As String

     strNoPhoto = CurrentProject.Path & "\personnelPhotos\noPhoto.jpg"

     If fso.FileExists(Me.txtPhotoLink) Then
          Me.txtPhotoLink.ControlSource = "PhotoLink"
     Else
          Me.txtPhotoLink.Value = strNoPhoto
     End If

Exit_ErrorHandler:
     Exit_Sub

ErrorHandler:
     Select Case Err.Number
          Case 94
               'Do nothing; there are no records in the underlying table 'tblBasic'.
          Case Else
               MsgBox Err.Number & " - " & Err.Description
     End Select
     Resume Exit_ErrorHandler

End Sub

Here are some of the things I have already tried that have not remedied the problem:

I replaced Dim fso As New FileSystemObject with Dim fso As FileSystemObject. This resulted in a different error message but the same result.

91 - Object variable or With block variable not set

I have tried add code to .edit and .update the recordsource but I still receive the 3020 error. Here is my code with the .edit and .update parameters. New code highlighted in red. WARNING, first time using this code so I am not completely familiar with all of the arguments and appropriate uses.

Private Sub PersDirectory_AfterUpdate()

On Error GoTo ErrorHandler

     Dim fso As New FileSystemObject
     Dim strNoPhoto As String
     Dim db As Database
     Dim rs As Recordset
     Dim sql As String

     set db = currentDb()
     sql = "SELECT * FROM tblBasic"
     set rs = CurrentDb.OpenRecordset(sql)

     strNoPhoto = CurrentProject.Path & "\personnelPhotos\noPhoto.jpg"

     with rs
     .edit
     If fso.FileExists(Me.txtPhotoLink) Then
          Me.txtPhotoLink.ControlSource = "PhotoLink"
     Else
          Me.txtPhotoLink.Value = strNoPhoto
     End If
     .update
     End With

Exit_ErrorHandler:
     Exit_Sub

ErrorHandler:
     Select Case Err.Number
          Case 94
               'Do nothing; there are no records in the underlying table 'tblBasic'.
          Case Else
               MsgBox Err.Number & " - " & Err.Description
     End Select
     Resume Exit_ErrorHandler

End Sub

Lastly, I have tried moving both versions of the code to the BeforeUpdate event of the list box. Same 3020 and 91 error message.

For such a small amount of code I must be doing something wrong that is very simple but a result of looking at the same error message for going on two weeks. Looking for some brilliant suggestions.

Thanks, Sean

[usmcgrunt]

Ok, issue solved. I do not really understand how it was solved but it was. The fix was the Recordset.Clone property. I need to do some more research to figure out why it worked. Here is the full updated and working code:

Private Sub PersDirectory_AfterUpdate() 

On Error GoTo ErrorHandler       

     Dim fos As New FileSystemObject
     Dim strNoPhoto As String
     Dim rs As Object

     strNoPhoto = CurrentProject.Path & "\personnelPhotos\noPhoto.jpg"       
     set rs = Me.Recordset.Clone

     DoCmd.SearchForRecord, "", acFirst, "[SID] = """ & Screen.ActiveControl & """"

     With rs
          If fso.FileExists(Me.txtPhotoLink) Then
          Else
               rs.edit
                Me.txtPhotoLink.Value = strNoPhoto
               rs.update
          End If
     End With
 
Exit_ErrorHandler:
      Exit_Sub

 ErrorHandler:
      Select Case Err.Number
           Case 94
                'Do nothing; there are no records in the underlying table 'tblBasic'.
           Case Else
                MsgBox Err.Number & " - " & Err.Description
      End Select
      Resume Exit_ErrorHandler

 End Sub