Attachments not saving (1 Viewer)

Rdcarlson65

New member
Local time
Yesterday, 22:11
Joined
May 16, 2021
Messages
4
Okay, not sure where to post this, under forms, tables, general... So just going with Tables. Anyway...

I am writing an access database for my job at a Condo Complex. I am learning as I go, so I am sure there is a lot of sloppy non-standard programming. Please forgive.

My problem is that suddenly my attachments aren't saving. If I change an attachment in a form, it shows in the form. I can move forward and backward in records, and it still shows. Problem is, if I pull up the table it is supposed to be saving to, it shows "0" next to the paperclip. But I CAN change the attachment directly in the table itself. I have different forms with their own various attachments, and the problem seems to exist in all forms. Other form elements appear to work and save properly (Text Boxes, Combo Boxes, etc...)

As I said before, I am sure I have a lot of sloppy and questionable programming. But I don't think that applies here because if I just load up my database and go straight to the attachment and try to edit it, I have this problem. None of my other vba or macros should have any affect because they are never called.

Now, I am assuming that when I change the attachment, Access is probably saving it in a temporary file or something. If that is the case, how do I force it to update the table from the temporary file? I have tried changing the attachment and changing focus to another text box, exiting the form, clicking save... Nothing is seeming to work.

Does anyone have any suggestions as to what I can do to get this working? Could it somehow be a problem with my computer, and not Access?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:11
Joined
Oct 29, 2018
Messages
15,548
Hi. Welcome to AWF!

Can you post a sample copy of your db with test data?
 

Mike Krailo

Active member
Local time
Today, 00:11
Joined
Mar 28, 2020
Messages
298
Suddenly they aren't saving? Something must have changed if they were saving before. Is there any code on the form that manipulates the allow edits property or cancel the save like in a before update? Did you try doing a decompile/recompile on the database? That fixes a lot of strange problems that crop up.

Do all other aspects of the form data save OK?
 

arnelgp

once i caught a fish alive...
Local time
Today, 12:11
Joined
May 7, 2009
Messages
13,205
You can check of the form is AllowAdditions/AllowEdits is set to No then toggle it.
 

GinaWhipp

AWF VIP
Local time
Today, 00:11
Joined
Jun 21, 2011
Messages
5,800
Hmm, what size is the database? When was the last time you ran Compact and Repair?
 

Rdcarlson65

New member
Local time
Yesterday, 22:11
Joined
May 16, 2021
Messages
4
UPDATE: Thank you all for your questions. Each of your responses (and a good night's sleep to think on it) added a little bit in bringing the problem into focus. Although the problem isn't solved yet, your questions have given me guidance in troubleshooting and you have helped point me in what I think is in the right direction. If I load the page, and scroll to a file with the bottom scroll arrows, I am able to update and change information. Which tells me that the problem is somewhere in my search function. I don't think I have changed anything in it recently, but I will go through it with a fine toothed comb to see if I can find the problem. When I do, I will let you guys know.

Again, thank you so very much for pointing me in the right direction!
 

Rdcarlson65

New member
Local time
Yesterday, 22:11
Joined
May 16, 2021
Messages
4
Okay, just so I don't leave everyone hanging, here's the code and a brief description of where I think the problem lies.

With Me.Recordset

rstOwnerAttachment.Edit
rstSecond.AddNew

x = rstVehicle.RecordCount
If x > 1 Then
rstSecond.Fields("FileName") = rstVehicle.Fields("FileName")
rstSecond.Fields("FileData") = rstVehicle.Fields("FileData")
rstOwnerAttachment!PMarker.Value = -1
End If

End With

x = rstSecond.RecordCount
Debug.Print "rstsecond recordcount - " & x

If rstSecond.RecordCount > 0 Then
name = rstSecond.Fields("FileName")
rstSecond.Update
'rstOwnerAttachment.Update
End If
rstOwnerAttachment.Update

Okay then, rstOwnerAttachment is the recordset for the main Owner's table. rstSecond is the recordset for the attachment which is a photo of a vehicle in the Owner's Table. and rstVehicle is the recordset for the vehicles page which shows several vehicles to choose from to associate with the owner. I have a combobox next to the owner where I can select the vehicle they own and what this snippet is supposed to do is to copy the selected photo from the vehicle page and post it into the owner's table as the owner's vehicle.

Where I think the problem is is with rstOwnerAttachment.Update. If it is placed BEFORE the ending if, it locks up the table, and therefore I cannot make any other changes. If I put it AFTER the ending if, almost everything works again, but the photo isn't copying from the vehicle page to the owner's page. So I the problem actually lies in TWO places. Obviously in this snippet of code, but also in my lack of knowledge of VBA. I will study, tinker, and learn, and eventually correct BOTH problems.

Again, thanks much!
 

arnelgp

once i caught a fish alive...
Local time
Today, 12:11
Joined
May 7, 2009
Messages
13,205
firstly you need to use DAO.Recordset2 for both Owner table and its Attachment field (as recordset).
secondly, your code should first Position the Owner table to the Correct record (by searching the PK field).
Code:
Public Sub SaveAnyToAttachment( _
                                ByVal strTable As String, _
                                ByVal strAttachmentFieldName As String, _
                                ByVal pkFieldName As String, _
                                ByVal pkValue As Variant, _
                                ByVal strFilename As String)
    
    ' strTable                  Table name with attachment field
    ' strAttachmentFieldName    the fieldname of attachment
    ' pkFieldName               Primary key of table, or autonumber field
    ' pkValue                   the value that will be searched from strTable
    ' strFileName               the complete path and filename to be saved in
    '                           attachment field.

    Dim rsParent As DAO.Recordset2
    Dim rsChild As DAO.Recordset2
    Dim db As DAO.Database
    
    Set db = CurrentDb
    
    ' open table with attachment field
    Set rsParent = db.OpenRecordset(strTable, dbOpenDynaset)
    With rsParent
        '// add new record to the table
        If IsNumeric(pkValue) Then
            .FindFirst "[" & pkFieldName & "] = " & pkValue
        Else
            .FindFirst "[" & pkFieldName & "] = '" & Replace$(pkValue, "'", "''") & "'"
        End If
        If Not .NoMatch Then
            '// if we find the record save the attachment
            '// edit this record, then we can add the attachment
            .Edit
            '// set recordset to our attachment field
            Set rsChild = rsParent.fields(strAttachmentFieldName).value
            With rsChild
                '// add new record to attachment
                .AddNew
                '// save report to attachment field
                ''Dim fld2 As DAO.field2
                ''Set fld2 = .Fields("FileData")
                ''fld2.LoadFromFile
                ''fld2.SaveToFile
                .fields("FileData").LoadFromFile (strFilename)
                .Update
                .Close
            End With
            .Update
        End If
        .Close
    End With
    '// release all instance of our recordset object
    Set rsChild = Nothing
    Set rsParent = Nothing
    Set db = Nothing
End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:11
Joined
Oct 29, 2018
Messages
15,548
Not sure if this will help, but see if the demo on my site works on your machine. If it does, you can compare the code with yours and see if there's any difference.

 

Rdcarlson65

New member
Local time
Yesterday, 22:11
Joined
May 16, 2021
Messages
4
I looked at your code snippets. Some good stuff I will likely use in the future, but nothing that I can see that applies to my immediate issue. Thanks much for the help!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:11
Joined
Oct 29, 2018
Messages
15,548
I looked at your code snippets. Some good stuff I will likely use in the future, but nothing that I can see that applies to my immediate issue. Thanks much for the help!
Hi. You're welcome. The link I posted demonstrates how to copy an Attachment from one table to another. I thought that's what you were trying to do, isn't it?
 

Users who are viewing this thread

Top Bottom