Import-export permit tracking system

[yrstruly]

Hi everyone,


I'm working on optimizing an import-export permit tracking system and need some suggestions on improving document storage, data linking, and retrieval.
Files: https://drive.google.com/file/d/1I5F...ew?usp=sharing
�� Current Setup:

• tblPermitInformation – Stores both import & export permits (permit number, authorization, shipment details, quarantine status).
• tblDocumentStorage – Holds related permit documents (PDFs, invoices, phytosanitary certificates), linked by PermitID.
• Forms & Queries – Used for permit tracking and data entry (frmImportRecords, frmTblPermitInformation).

✅ What I’ve Done So Far:

• Core Tables Created:
  
  • tblPermitInformation (for all permits).
  • tblDocumentStorage (for storing related files).

�� What’s Needed:

1️⃣ Differentiate Import vs. Export Permits – Add a PermitType field.
2️⃣ Link Imports to Exports – Create a tblImportExportLink table for batch tracking.
3️⃣ Improve Document Retrieval – Use a VBA button to open files or a subform for multiple docs.
4️⃣ Optimize Queries & Alerts – Ensure missing documents are flagged & track upcoming permit expirations.
�� Export Process Issues:

• No centralized system for storing export data.
• No linkage between export documentation and import records.
• No tracking of essential export details.

�� Proposed Solutions:

• Create a dedicated export management section in the database.
• Make fields mandatory (Variety, Clone, Requestor, Permit No, Validity Date, Date Shipped, Destination Country, Material Type, Quantity).
• Implement a document repository similar to import storage.

�� New Feature: Import-Export Traceability

• Automatically link records based on batch numbers or permit references.

�� Import Document Storage Issues:

• Lack of structured document categorization.
• No link between import docs & other database records.

Proposed Fix:

• Implement structured document storage with:
  
  • Dedicated fields for permit documents (PDF/Word).
  • Dropdown selection for document types (DHL docs, Phytosanitary, Import Authorization, etc.).
  • Bulk document upload for multiple related permits.

Would love to hear any insights on best practices for linking imports & exports, automating document retrieval, and optimizing tracking! Thanks in advance. ��

[jojowhite]

cross posted:
Import-export permit tracking system | Access World Forums

[Welshgasman]

Now crossposted at https://www.access-programmers.co.uk.../#post-1954607

Appears to be a serial crosposter?

[madpiet]

My two cents is that Access is the wrong back end database for this. Use SQL Server or some other data engine that can handle file storage. (basically takes over a chunk of the hard drive from the OS and manages it for itself). That way the only way you can add/remove/change files is through the database. Then just use Access as a front end.

[yrstruly]

Only the file details and location will be stored in a table. The file will be stored in Sharepoint.

My two cents is that Access is the wrong back end database for this. Use SQL Server or some other data engine that can handle file storage. (basically takes over a chunk of the hard drive from the OS and manages it for itself). That way the only way you can add/remove/change files is through the database. Then just use Access as a front end.

[Micron]

I don't think it's enough to just report a cross post. If you want to have any chance that the OP will do it right next time, it needs to be explained.
Cross Posting Message

[yrstruly]

Steps I took to Link Document Storage to Permit Information in MS Access

1. Created tblDocumentStorage Table

• Since document details were missing, I created a new table tblDocumentStorage with columns:
  
  • DocumentID (AutoNumber, Primary Key)
  • PermitID (Number, Foreign Key)
  • FileName, FilePath, DocumentType
  • UploadDate, UploadedBy, Comments
  
  
• Ensured PermitID references tblPermitInformation.PermitID for linking.

2. Modified qryPermitInformation Query to Include Documents

SELECT tblPermitInformation.PlantID, Plants.Cultivar AS Variety, Plants.Clone, tblPermitInformation.PermitID, tblPermitInformation.Import_Requestor, tblPermitInformation.Permit_No, tblPermitInformation.Permit_appl_date, tblPermitInformation.Permit_valid_until, tblPermitInformation.Authorization_application_date, tblPermitInformation.Authorization_no, tblPermitInformation.Authorization_valid_until, tblPermitInformation.Date_shipped, tblPermitInformation.Arrival_date, tblPermitInformation.Export_country, tblPermitInformation.Material_type, tblPermitInformation.Quantity_of_material, tblPermitInformation.Import_comments, tblPermitInformation.FumigationStatus, tblPermitInformation.PermitAuthApprovedDate, tblPermitInformation.ExpectedReleaseDate, tblDocumentStorage.FileName, tblDocumentStorage.FilePath, tblDocumentStorage.DocumentType, tblDocumentStorage.UploadDate, tblDocumentStorage.UploadedBy, tblDocumentStorage.CommentsFROM (tblPermitInformation INNER JOIN Plants ON tblPermitInformation.PlantID = Plants.PlantID) LEFT JOIN tblDocumentStorage ON tblPermitInformation.PermitID = tblDocumentStorage.PermitID
ORDER BY Nz(Plants.Cultivar, ''), Nz(Plants.Clone, '');

• Previously, qryPermitInformation joined tblPermitInformation and Plants.
• I updated the query to include tblDocumentStorage using a LEFT JOIN on PermitID,
• Form frmCultivarPermitFileStore was never linked to a data source. It only imports files.
• I connected qryPermitInformation as its data source and got the following message, which i clicked No:

�� To complete this operation, Access must modify the RecordSource property of the current form or report.
"Access will create a new query and embed it directly into the form’s or report’s RecordSource property. The form or report will no longer be based on the qryPermitInformation query."
Do you want to accept this change?
✅ Yes | ❌ No

• 
  I could not load newly created document tables column on the form mentioned. Only when i unlinked the data source, i could.
  
• What am i suppose to do in this regard?
  

[moke123]

Are you using a named query and trying to modify it in the form or in the named query itself?

[mike60smart]

You should have a Main Form based on tblPermitInformation with a
Subform based on tblDocumentStorage

Linked on PermitID

[yrstruly]

frmCultivarePermiteFilestore is link by a button on frmPermitInformation which is linked to source query

SELECT tblPermitInformation.PlantID, Plants.Cultivar AS Variety, Plants.Clone, tblPermitInformation.PermitID, tblPermitInformation.Import_Requestor, tblPermitInformation.Permit_No, tblPermitInformation.Permit_appl_date, tblPermitInformation.Permit_valid_until, tblPermitInformation.Authorization_application_date, tblPermitInformation.Authorization_no, tblPermitInformation.Authorization_valid_until, tblPermitInformation.Date_shipped, tblPermitInformation.Arrival_date, tblPermitInformation.Export_country, tblPermitInformation.Material_type, tblPermitInformation.Quantity_of_material, tblPermitInformation.Import_commentsFROM (tblPermitInformation INNER JOIN Plants ON tblPermitInformation.PlantID = Plants.PlantID) LEFT JOIN tblDocumentStorage ON tblPermitInformation.PermitID = tblDocumentStorage.PermitID
ORDER BY Plants.Cultivar, Plants.Clone;


 tblPermitInformation
Plants
tblDocumentStorage

I brought in tblDocumentStorage because the documents for imports has no table to store its data and no linkage/id's.
When i try to connect the tblDocumentStorage to the same query above i get the message as seen.
frmCultivarepermit is holding location data or files and is not linked to a table yet. Because i made amendments, and would like to bring in new table with its columns, i can only do that from the Doument table and not with the amended query. Please see: https://drive.google.com/file/d/1hK9...ew?usp=sharing

You can use Fn + F11 to open the objects and to view the forms, open in design view then in form view.. You'll see i made a copy of the original frmCultivarePermit and see the changes i would like to bring in.

[yrstruly]

I do, please see my ERD and current format in files.https://drive.google.com/file/d/1hK9...ew?usp=sharing