Multiple Primary Key ID Numbers in Form with Subform ???

[robbfamily]

I have a document management db with forms/subforms. I want a list of unique documents with sub forms showing what language that document has been translated into and it's corresponding attached document.



I've apparently made a change that has caused the "tblDocuments" form (default form on open) to show more than one copy of a record. It's showing multiple copies of each document including the same Primary Key ID & corresponding Document Name in the form.

Can anyone tell me how to get back to only one copy of my documents (Primary key) in the Documents form?


Any other suggestions on how to improve the efficiency of this database is also welcome.

Thanks

David

ps. The file, zipped is 4.1 MB the dropbox public link to this file is: https://www.dropbox.com/s/7vfasel8g2...nagement_1.zip

[June7]

I can't download file right now, maybe tomorrow.

Did you change the RecordSource of the form to include another table?

[robbfamily]

June7, Not that I know of. I added some yes/no check boxes to the form for query purposes, but no other tables. That's what's got me confused. I'm not sure what I did to cause the duplication. There are a lot of relationships with this db. I'm sure it's there somewhere.

thanks for any help, much appreciated.

David

[June7]

The reason for 'duplicate' document ID is inclusion of tblAttachments in the form RecordSource. There are multiple attachment records for each document ID. The attachments table should be in a subform.

If each document can be only one type (Personal, Financial, Pre, etc) then instead of multiple checkboxes should be a single field with a combobox. If each document can be multiple types, possibly should be a junction table with a record for each type/documentID combination. Multiple checkboxes for what is essentially the same data element will be problematic in searching and filtering records.

Are you aware that Access has a 2GB file size limit? Embedded attachments can use up that limit fast.

[robbfamily]

Thanks, June7 for the advice. I thought the attachments were already in a subform (tab) along with all of the other subforms in the Documents form. What am I missing?

So, I should get my I've embedded the attachments because I'm not confident that the source file will remain 'in-place' on the network. I don't currently have exclusive control over those folders. Though, the limitation may force a 'link' rather than embedding. Is that the only other option?

[June7]

frmDocument has a RecordSource that is an SQL statement which includes tblDocument and tblAttachment. Remove tblAttachment from that RecordSource.

There are subforms on the tab control and one of them is bound to tblAttachment. I did not look for it before, I went as far as looking at frmDocument RecordSource.

Alternative to embedded attachments is saving document filename (with or without complete folder path) in a text field. Review http://allenbrowne.com/func-GoHyperlink.html

[robbfamily]

Thank you, Thank you. perfect solution, June7. I appreciate it greatly!

David