Form holding documents

[yrstruly]

I have a form that is linked to a query, which retrieves data from two tables. However, the subform is only retrieving document records and is not correctly linked to the main (parent) form. Please see link, i could not get my full text in here.https://drive.google.com/file/d/11Es...ew?usp=sharing

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

Table relationship for tblPermitInformation and tblDocumentStorage does not make sense. Both tables have Permit and DocumentType information.

[yrstruly]

Your post is irrelevant. I am talking about a form which holds documents.

[June7]

What do you mean "our post is irrelevant"? Why would post be irrelevant - it has your question.
I know you are talking about a form and I am suggesting you provide database by following instructions provided at bottom of my post.

Root of your issue is table relationships, which don't make sense.

[yrstruly]

What do you propose then? The existing form that retrieves documents lacks this:

Dedicated database fields for permit documents (PDF/Word)o Dropdown selection for document type (e.g., DHL docs, Phytosanitary, Import Authorization)o Bulk document upload feature for multiple related permits. Because this subform is not linked to its parent form in a table structure, identifying the forms are difficult. Example each pdf should be located to a client id ect.

See full details with ERD http://drive.google.com/file/d/11Esb...ew?usp=sharing

[madpiet]

Wait, are you trying to store documents inside Access memo fields or something? I wouldn't do that. If you have to do that, I'd use an industrial strength database.

Here's the query from the PDF (but I aliased the table, because I hate reading crazy long table names):

SELECT 
    i.PlantID, 
    i.Variety,
    i.Clone, 
    i.PermitID,
    i.Import_Requestor, 
    i.Permit_No,
    i.Permit_appl_date, 
    i.Permit_valid_until,
    i.Authorization_application_date,
    i.Authorization_no, 
    i.Authorization_valid_until,
    i.Date_shipped, 
    i.Arrival_date,
    i.Export_country, 
    i.Material_type,
    i.Quantity_of_material, 
    i.Import_comments
FROM qryPermitInformation i;

This part gives me pause, though...
o Bulk document upload feature for multiple related permits.

If storing files inside your database, then you need something like SQL Server's Filestream feature https://learn.microsoft.com/en-us/sq...l-server-ver16 because Access just plain does not offer that feature. The best you can do in Access is to create hyperlinks to files somewhere on your network or hard drive. But because the database doesn't manage the files, it does not track deletions etc or the fact that someone moved the files (by updating the location). Filestream, on the other hand, basically blocks off a chunk of space on the hard drive and "tells" the operating system that it will manage the space. If that's the feature you need, then your best bet is to have a SQL Server back end and (maybe) an Access front end. But "I'm sorry, [Dave], I'm afraid [Access] can't do that."

[yrstruly]

No, only the log, file link and details of the files uploaded. Files will stored on Sharepoint.

[madpiet]

I have a form that is linked to a query, which retrieves data from two tables. However, the subform is only retrieving document records and is not correctly linked to the main (parent) form.

query? Subform? Parent form? what are the RowSources for each? There's not nearly enough information provided here for us to figure out what's going on. Are the LinkChild and LinkMaster properties of each subform set? I think June's right. There's waaaay too much you're leaving out. The only way to really understand what's going on is to see the database. Except since it's linked to your sharepoint, we won't be able to see any data.

"Situation no win! Rush for a change of atmosphere..."

[yrstruly]

Will te SQL scripts do or do you want to see the DB itself?

[yrstruly]

Th Sub Forms VBA code

Option Compare Database

Option Explicit
 
Private Sub cmdOpenPlantFile_Click()
'7 Select Licensor or Cultivar. Automatic read from Mother Form for Plant information andPermit Information.
' Show File Picker to select a file path.
' http://accessjitsu.com/2015/09/13/code-listing-using-the-microsoft-office-file-dialog-in-ms-access/
' https://www.youtube.com/watch?v=DTtDchA4XQ4
On Error GoTo SubError
    'Add "Microsoft Office 16.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim Varfile As Variant
    Dim SourceFile As String
    Dim Formname As String
    Dim Filterstring As String
    Formname = Me.Name
    Filterstring = Me.[cmbSelectDirectory] & Me.[txtLicensor] & "*"
    'https://chandoo.org/forum/threads/filter-file-name-in-application-filedialog-msofiledialogfilepicker.43634/
    txtOpenedFile = ""
   
    ' Set up the File Dialog
'
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Pick a file.When entering the formula you enter
'                                                                  the left"(" you get four options.
'                                                                  Set fDialog = Application.FileDialog(msoFileDialogOpen)
   
    With fDialog
        .Title = "Choose the file you would like to open"
        .AllowMultiSelect = False


        .InitialFileName = Filterstring
        


        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
            For Each Varfile In .SelectedItems
'               txtSelectedName = txtSelectedName & Varfile & vbCrLf ' & vbCrLf sit 'n linefeed in wat ons ni ewil hê nie.
                txtOpenedFile = Varfile
            Next
    
           
        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
    End With
    


If Formname = "frmCultivarPropFileStore" Then
SourceFile = [Forms]![frmCultivarPropFileStore]![txtOpenedFile]
ElseIf Formname = "frmCultivarPermitFileStore" Then
SourceFile = [Forms]![frmCultivarPermitFileStore]![txtOpenedFile]
End If


Application.FollowHyperlink SourceFile


SubExit:
On Error Resume Next
    Set fDialog = Nothing
    Exit Sub
   
SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
       
End Sub








Private Sub cmdSelectOrigenDirectory_Click()
'2 Select Origen Folder if Different
' https://www.youtube.com/watch?v=WmRhSDcwf1k
' http://accessjitsu.com/2015/09/13/code-listing-using-the-microsoft-office-file-dialog-in-ms-access/
' https://www.youtube.com/watch?v=DTtDchA4XQ4


    Dim Formname As String
    
    Formname = Me.Name


On Error GoTo SubError
    'Add "Microsoft Office 14.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim Varfile As Variant
   
    txtBeginningDirectory = ""
   
'    Set up the File Dialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)


   
    With fDialog
        .Title = "Choose the directory you would like to import from"
        .AllowMultiSelect = False


        .InitialFileName = Me.txtBeginningDirectory.Value  'Folder picker needs trailing slash
       
 
        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
           
            For Each Varfile In .SelectedItems
'                txtBeginningDirectory = txtBeginningDirectory & Varfile & vbCrLf & "\" 'vbCrLf = Linefeed. Wil dit nie hê nie.
            txtBeginningDirectory = txtBeginningDirectory & Varfile & "\"
            DoCmd.SetWarnings False '-------------------------
         
                If Formname = "frmClientFileStore" Then
                    DoCmd.OpenQuery "qryUpdatetblLastFolder"
                ElseIf Formname = "frmCultivarPropFileStore" Then
                    DoCmd.OpenQuery "qryUpdatetblLastFolderOrigenProp"
                ElseIf Formname = "frmCultivarPemitFileStore" Then
                    DoCmd.OpenQuery "qryUpdatetblLastFolderOrigenPermit"
'
                End If
'            DoCmd.OpenQuery "qryUpdatetblLastFolder"


            DoCmd.SetWarnings True '---------------------


            Next


        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
        
    End With
   
SubExit:
On Error Resume Next
    Set fDialog = Nothing
    Exit Sub
   
SubError:
            DoCmd.SetWarnings True


    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
       
End Sub


Private Sub cmdShow_Click()
' Show File Picker to select a file path.
' http://accessjitsu.com/2015/09/13/code-listing-using-the-microsoft-office-file-dialog-in-ms-access/
' https://www.youtube.com/watch?v=DTtDchA4XQ4
On Error GoTo SubError
    'Add "Microsoft Office 16.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim Varfile As Variant
   
    txtSelectedName = ""
   
    ' Set up the File Dialog
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker) 'Pick a file.When entering the formula you enter the left"(" you get four options.
'    Set fDialog = Application.FileDialog(msoFileDialogOpen)
   
    With fDialog
        .Title = "Choose the file you would like to import"
        .AllowMultiSelect = False
        .InitialFileName = Me.txtBeginningDirectory.Value  'Folder picker needs trailing slash
       
        .Filters.Clear
        .Filters.Add "All files", "*.*", 1
        
        .Filters.Add "Excel files", "*.xls*", 2
        .Filters.Add "Excel files", "*.doc*", 3
        .Filters.Add "Various files", "*.doc* ;*.xls*", 4
        .Filters.Add "Images", "*.gif; *.jpg; *.jpeg", 5
'        .Filters.Add "Excel files", "*.xls"
'        .Filters.Add "Excel files", "*.xlsx"
'        .Filters.Add "Excel macro-enabled", "*.xlsm"
        .FilterIndex = 1 ' Or 2 for Excel files and 3 for Word Files etc.
 
        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
           
            'An option for MultiSelect = False
            'varFile = .SelectedItems(1)
            'txtSelectedName = varFile
           
            'Needed when MultiSelect = True
            For Each Varfile In .SelectedItems
'               txtSelectedName = txtSelectedName & Varfile & vbCrLf ' & vbCrLf sit 'n linefeed in wat ons ni ewil hê nie.
                txtSelectedName = txtSelectedName & Varfile
            Next
        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
    End With
    
'xxxxxxx    ' Determine Name from txtSelectedName and enter into txtNewFileName
' GetFilenameFromPath(txtSelectedName)
' Varfile = GetFilenameFromPath(SourceFile)
'SourceFile = [Forms]![frmCultivarPropFileStore]![txtSelectedName]
Varfile = GetFilenameFromPath([Forms]![frmCultivarPermitFileStore]![txtSelectedName])


 txtNewFileName = GetFileNamepart(Varfile)
   
SubExit:
On Error Resume Next
    Set fDialog = Nothing
    Exit Sub
   
SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
       
End Sub
Private Sub cmdSaveFile_Click()                 'xxxxxxxxxxx
' See https://howtoexcelatexcel.com/vba/copy-a-file-from-one-folder-to-another-macro/
Dim SourceFile As String
Dim DestFile As String
Dim DestDir As String
Dim DestDir1 As String
Dim DestDir2 As String
Dim DestDir3 As String
Dim Varfile As String
Dim FolderExists As String
    txtSavedPath = ""
    
    DestDir = cmbSelectDirectory & "\"
'    DestDir1 = "J:\IP Clients\"
'    DestDir2 = "C:\SAPO\PlantPropertiesDocuments\"
'    DestDir3 = "\\Sapofs01\ip\IP Clients\"
'    DestDir3 = "C:\ip\IP Clients\"


GoTo SubFolderExistsforLicensor 'Cut tests for directories out


TestForDestDir1:
If Len(Dir(DestDir1, vbDirectory)) > 0 Then
'   MsgBox "This directory does exist." & DestDir1
   DestDir = DestDir1
   GoTo ProceedDestDir
Else
   MsgBox "This directory does NOT exist. " & DestDir1 & "This one is the normal one for SAPO users. Click OK to try the next one."
End If


If Len(Dir(DestDir2, vbDirectory)) > 0 Then
'   MsgBox "This directory does exist." & DestDir2
   DestDir = DestDir2
   GoTo ProceedDestDir
Else
   MsgBox "This directory does NOT exist. " & DestDir2 & "This is the normal one on Fritz's Laptop. Try the next one."
End If


If Len(Dir(DestDir3, vbDirectory)) > 0 Then
'   MsgBox "This file does exist." & DestDir3
   DestDir = DestDir3
   GoTo ProceedDestDir
Else
   MsgBox "None of the directories exist. Not " & DestDir1 & " NOR " & DestDir2 & " NOR " & DestDir3 & "Please ensure one exist. The first one is the normal for SAPO users."
   GoTo ExitSub
End If


ProceedDestDir:


    DestDir = DestDir & Me.txtLicensor.Value & "\"
If Len(Dir(DestDir, vbDirectory)) > 0 Then
'    MsgBox "This file does exist." & DestDir
Else
    MsgBox "None of the directories exist. Not " & DestDir1 & " NOR " & DestDir2 & " NOR " & DestDir3
    GoTo ExitSub
End If
       
FolderExistsforLicensor:


    DestDir = DestDir & cmbSelectDirectory & "\"
If Len(Dir(DestDir, vbDirectory)) > 0 Then
'    MsgBox "This file does exist." & DestDir
Else
    MsgBox "The sub directory for Plant Properties does not exist" & DestDir
    GoTo ExitSub
End If


SubFolderExistsforLicensor:
    
    SourceFile = [Forms]![frmCultivarPermitFileStore]![txtSelectedName] 'Korrek


    Varfile = GetFilenameFromPath(SourceFile)


    DestFile = DestDir & [Forms]![frmCultivarPermitFileStore]![txtLicensor] & ";" & [Forms]![frmCultivarPermitFileStore]![txtNewFileName] & "." & GetFileExt(Varfile)


    If SourceFile = DestFile Then
    MsgBox "Source and Destination is the same. Please change."
    GoTo ExitSub
    End If


    txtSavedPath = DestFile


FileCopy SourceFile, DestFile




    MsgBox "File successfully saved to path shown on the right. " & txtSavedPath
ExitSub:


End Sub


Sub SelectDestinationDirectory()
'Not used. May expand to this later.
'OPEN FILE PICKER TO SELECT DESTINATION DIRECTORY AND SAVE FILE
'#####################
'1. OPEN FILE PICKER TO SELECT DESTINATION DIRECTORY
' Show File Picker to select a file path.
' http://accessjitsu.com/2015/09/13/code-listing-using-the-microsoft-office-file-dialog-in-ms-access/
' https://www.youtube.com/watch?v=DTtDchA4XQ4
On Error GoTo SubError
    'Add "Microsoft Office 16.0 Object Library" in references
    Dim fDialog As Office.FileDialog
    Dim Varfile As Variant
    Dim TargetFolder As String
'Stop '1
    TargetFolder = "C:\SAPO\IP Database Module\" & Me.txtLicensor.Value & "\" & cmbSelectDirectory & "\"
    
    txtSelectedName = ""  'This is to clearfile picker file name.
   
    ' Set up the File Dialog
    Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
Stop '2
'    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    'Pick a file.When entering the formula you enter the left"(" you get four options.


    With fDialog
        .Title = "Choose the folder where you want to save the file"
        .AllowMultiSelect = False
        .InitialFileName = TargetFolder  'Folder picker needs trailing slash
       
        .Filters.Clear ' No filters for save.
Stop '3
' Moet hier 'n directory select
        If .Show = True Then
            If .SelectedItems.Count = 0 Then
                'User clicked open but didn't select a file
                GoTo SubExit
            End If
           
            'An option for MultiSelect = False
            TargetFolder = .SelectedItems(1) & "\"
            'txtSelectedName = varFile
Stop '4
            'Needed when MultiSelect = True
            For Each Varfile In .SelectedItems
'               txtSelectedName = txtSelectedName & Varfile & vbCrLf ' & vbCrLf sit 'n linefeed in wat ons ni ewil hê nie.
                txtSelectedName = txtSelectedName & Varfile
            Next
        Else
            'user cancelled dialog without choosing!
            'Do you need to react?
        End If
   
    End With
Stop '5
'xxxxxxx    ' Determine Name from txtSelectedName and enter into txtNewFileName
' GetFilenameFromPath(txtSelectedName)
' Varfile = GetFilenameFromPath(SourceFile)
'SourceFile = [Forms]![frmClientFileStore]![txtSelectedName]
Varfile = GetFilenameFromPath([Forms]![frmClientFileStore]![txtSelectedName])


 txtNewFileName = GetFileNamepart(Varfile)
   
SubExit:
On Error Resume Next
Stop '6
    Set fDialog = Nothing
    Exit Sub
   
SubError:
Stop '7
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit
       
End Sub
'^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV




Function GetFilenameFromPath(ByVal strPath As String) As String
' https://stackoverflow.com/questions/1743328/how-to-extract-file-name-from-path
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'


    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function


Function GetFileExt(ByVal strFileWPath As String)
'See https://www.devhut.net/2013/02/26/ms-access-vba-determine-a-files-extension/
On Error GoTo Error_Handler
 
    GetFileExt = Right(strFileWPath, Len(strFileWPath) - InStrRev(strFileWPath, "."))
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: GetFileExt" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function


Function GetFileNamepart(ByVal myPath As String)
'See https://www.devhut.net/2013/02/26/ms-access-vba-determine-a-files-extension/
On Error GoTo Error_Handler
'GetFilenameFromPath(ByVal strPath As String)
'Left(myPath, InStrRev(myPath, ".") - 1)
'https://www.thespreadsheetguru.com/the-code-vault/2014/3/2/retrieving-the-file-name-extension-from-a-file-path-string
    
    GetFileNamepart = Left(myPath, InStrRev(myPath, ".") - 1)
    GetFileNamepart = GetFileNamepart
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: GetFileExt" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function


Private Sub Form_Close()
    txtSavedPath = ""
    txtNewFileName = ""
    txtSelectedName = ""
'    txtLicensor = ""
    txtOpenedFile = ""
End Sub


Private Sub Form_Current()
    DoCmd.MoveSize 1200, 400, 16500, 11500
    txtBeginningDirectory = DLast("LastOrigenFolderPermit", "tblLastFolder", "ID = 1")
End Sub

[mike60smart]

This example may be what you are after.

[June7]

Can always make a copy of frontend and import data to provide db for analysis.

What I don't understand is why tblPermitInformation and tblDocumentStorage both have fields for PermitID and DocumentType.

PermitID in tblPermitInformation is autonumber and PermitID in tblDocumentStorage is short text. This means the fields cannot be linked as primary and foreign keys. Is PermitID in tblPermitInformation defined is primary key? What exactly are you saving into PermitID of tblDocumentStorage? This is possibly why your form/subform arrangement is not working.

So again, I say relationship for these two tables doesn't make sense.

[yrstruly]

You are correct. A Foreign key must be a primary key in another table when doing dimensional modeling. Forget about the tblDocumentStorag(it should be amended to accomodate need), for the the time being.
1. The Subform already contains the link to files and can hold file stored on local or Sharepoint.
2. The Issue is because the subform has no table yet, it i not part of the data model.
3. The Subform needs to be amended to meet the needs as required:
Implement structured document storage.
Dedicated database fields for permit documents (PDF/Word)o Dropdown selection for document type (e.g., DHL docs, Phytosanitary, Import Authorization)o Bulk document upload feature for multiple related permits.

Business Rule/Sample. Pemrmit ID should be linked to a document with that exact Permit ID. Currently the Data Model does not contain a table so that a particular document when uploaded (using existing form), needs t0 be linked to a spefic Permit id.

[yrstruly]

Form and Subform:

[yrstruly]

See DB: https://drive.google.com/file/d/1I5F...ew?usp=sharing