Multiple File Upoad in Form

[yrstruly]

Request for Assistance: Seeking guidance on ensuring the subform correctly displays and manages multiple documents associated with each permit.

https://drive.google.com/file/d/1PDP...ew?usp=sharing



Form Views https://drive.google.com/file/d/1KeO...ew?usp=sharing
Current Setup:

• Form: frmCultivarPermitFileStore
  
  • Purpose: Manages file storage related to cultivar permits.
  • Existing Functionality: Currently allows uploading documents but doesn't associate them with specific permits.
  
  
• Table: tblDocumentStorage
  
  • Purpose: Created to store multiple documents per permit, establishing a one-to-many relationship.
  • Fields Include: DocumentID, PermitID, FileName, FilePath, DocumentType, UploadDate, UploadedBy, Comments.

Objective:Enhance frmCultivarPermitFileStore to link uploaded documents to their unique PermitID, enabling management of multiple documents per permit.
Steps Taken:

1. Backup: Duplicated frmCultivarPermitFileStore to preserve the original.
2. Form Layout Adjustment: Reorganized existing fields to accommodate a subform.
3. Subform Integration:
   
   • Inserted a subform into frmCultivarPermitFileStore.
   • Set Source Object to tblDocumentStorage.
   • Configured Link Master Fields and Link Child Fields to PermitID to establish the relationship.
   
   
4. Testing: In Form View, verified that the subform displays and allows management of multiple documents per permit.

Issue Encountered:The subform doesn't display related documents as expected.

[Welshgasman]

How about just giving us what we need to see the problem?
Not everything. The objects do not even appear to be in order?
DB does not compile. ?

Nothing in the link fields in the version I downloaded?
Nothing in the tables to link to as far as I can see?

[yrstruly]

I hope this is better https://drive.google.com/file/d/1I5F...ew?usp=sharing

The DB is empty for security reasons, the forms should be in tact

[Welshgasman]

Why can't you just upload to here? Is the zip too big?

[madpiet]

Request for Assistance: Seeking guidance on ensuring the subform correctly displays and manages multiple documents associated with each permit.

How are you selecting the documents? I'm not sure there's enough information here to answer your question. If you can select the documents through the subform, the LinkChild and LinkMaster properties will basically force the document to be related to the current parent record. "Manages multiple documents"... how do you mean?

If you have a main form that is bound to the Permit table, and then you have a child table "Document" and it already has a foreign key to the Permit table, then this should work fine. you just do something like use the FileOpenAPI (how 1990s!) to get the file name, you can just grab the PermitID or whatever from the main form and write both of the values into the subform's table.

[yrstruly]

Im having great trouble trying to upload attachedments on this forum, thats why im posting links instead.

[jojowhite]

you put all files (FE/BE) in a .Zip file and attach it on your post.

[yrstruly]

It does not have to be a subform. To simplify it. I can just amend the current form for uploading documents to get the desired results. Please see the form how i currently is in the files i provided. The functionality is made possible with a VBA code. Which you also have access to.

[yrstruly]

Most of the time the upload function for files on this forum does not work.

[Welshgasman]

Most of the time the upload function for files on this forum does not work.

I had that problem with chrome a while back and had to use Firefox, then it was working again with chrome.

We need data to work with.
Use this to santize your data, and only supply what we need to see the problem.
Randomize Data

[yrstruly]

I tried the randomizer, im getting an error. I have attached the file.

[ArviLaanemets]

Based on Post# 1, needed are tables like:
tblPermits: PermitID, ...;
tblDocuments: DocumentID, ...;
tblPermitDocuments: PermitDocumentID, PermitID, DocumentID, ...

Forms needed for managing permit for documents:
based on post# 1 documents must be linked to permirts, so either
a) You have a single form based on tblPermits, where a specific permit is selected.
This form has contionous subform based on tblPermitDocuments. Main form and subform are linked by PermitID.
b) You have an unbound form with combo to select Permit (linked with PermitID of rowsource query).
This form has contionous subform based on tblPermitDocuments. Main form and subform are linked by PermitID of subform, and combo in Main form (i.e. fills the text field linked to tblPermitDocuments.DocumentID with DocumentID active in Main form).

With either of designs, adding a document into subform (adding a row, and selecting the document from combo linked to tblPermitDocuments.DocumentID) automatically links it with PermitID active in Main form

[yrstruly]

It does not have to be a sub form, it can be the form that is already created just amended to resolve what is needed.

[moke123]

I tried the randomizer, im getting an error. I have attached the file.

Thanks. You've found a flaw in my code but its due to a flaw in yours.

You have spaces in table and field names which sql doesn't quite like. I never have spaces in any object names and neither should you.
In code you need to wrap object names with spaces in brackets [ ]

The error occurs in this line

strSql = "select " & fld & " from " & tbl & " where " & fld & " is not null"

I'll have to go back and re-post with the following change:

strSql = "select [" & fld & "] from [" & tbl & "] where [" & fld & "] is not null"

here's an updated copy
DataRandomizer2025.accdb

[yrstruly]

Here is the FE n BE with masked data. https://drive.google.com/file/d/14S2...ew?usp=sharing

How about just giving us what we need to see the problem?
Not everything. The objects do not even appear to be in order?
DB does not compile. ?

Nothing in the link fields in the version I downloaded?
Nothing in the tables to link to as far as I can see?