Form Counts Single Record with Multiple Attachments as Multiple Records

[MatthewR]

Hi, everyone. I have a table with an attachment field, and I have a form based off of that table.



The thing is, Access allows users to attach multiple files within a single record. That's okay with me, I guess.

What's not okay, though, is that if a single record has 3 attachments, it shows up as 3 separate records on the form.

Which I do not want at all!

I have tried very hard to fix this, and I have failed. Any suggestions???

Thanks very much, y'all.

Matt

[ItsMe]

I do not have a lot of experience with this, but I thought if you used an Attachment control to represent the attachment field, the main record will be represented as a single record within the form and the Attachment Control would allow you to navigate through multiple attachments.

Are you using Form View and an Attachment Control?

[MatthewR]

Hi. Thanks very much for the reply. I really appreciate the help!

Yes, I am using form view and an attachment control.

And yes, the form is represented as a single record - it has a main record and an attachment control which allows me to navigate through multiple attachments.

The problem is, this single record appears multiple times in the recordset if there are multiple attachments.

In other words, the same record - with the same main record and the same attachment control- is included and counted multiple times within the recordset. If a single record has 3 attachments, the form with show 3 identical records in its recordset- each with an identical main form and an identical attachment control.

Do you have any idea why this happens?

[ItsMe]

I just did a quick test and was unable to replicate the issue. I created a new DB and added a table to it. I created a new Attachment Field within the table that has existing records.

I created a new form and bound the form to the table object. I added two controls to the form. I added a textbox bound to the Primary Key and an Attachment Control bound to the Attachment field. The form had 186 records within its recordset. I used the intrinsic Navigation Buttons of the form to navigate to a few random records. I added multiple photos to different records, using the Navigation control. I closed and reopened the form.

After adding multiple attachments, the form still had 186 records within its recordset. After I navigated to a record with attachments, I did not have to navigate through multiple records to move passed the record with multiple attachments.

Perhaps you can create a new form and bind the form to your table, adding the PK and the Attachment field. Binding the new form directly to the table and testing the new form might isolate an issue with a Query Object or your existing form.

[MatthewR]

Thanks so much for trying that out, and for the suggestions.

So... this is pretty embarrassing, but I think I figured out the problem. The form's Record Source property, for some unknown reason, was this:


SELECT tblDocuments.*, Document.FileName, Document.FileData, Document.FileType FROM tblDocuments;


So of course, that makes a separate record for each attachment file. So, I just changed it to:


tblDocuments


...and now it's fine.

Oh, lordy. A big facepalm is in order.

Thanks again for your help, and sorry for being such a dum dum!

[ItsMe]

That's cool. I suspected something like this was the issue, but I am not a pro with the Attachment thing. Glad you were able to sort it out.