Passing current path to code modules

[riggsd]

I split my database and so far, it is working well, i.e., no more conflict messages, however, I do have one issue I need assistance with.

There are some MSWord templates that I insert text into using bookmarks and the code finds the files by getting the CurrentProject.Path. This was fine when the database was all one file or if the user is using the front end file that is in the same folder as the back end file, but when the user makes a copy of the front end file to place on their desktop, the code can't find the file because it's not on the user's desktop. Now, I don't want to have the users make copies of all the templates and put them on their desktop but this is what we're having to do for now.

I found some code to put into my main form when it loads but the path isn't getting passed to the code modules. So, my question is, how do I pass in the updated path to my code?

Currently, it looks for the file using "If FileExists(TrailingSlash(CurrentProject.Path) & "CDRL_Template_Single_DM.dotx") = False Then"

Code for getting the current path was found here, last post - https://stackoverflow.com/questions...ve-path-specify-a-linked-table-in-access-2007

Thanks in advance.

 

[Micron]

how do I pass in the updated path to my code?

Depends on how you want to do it.
One way is to use the msoFileDialogFolderPicker to locate the folder holding the templates (like navigation via Windows File Explorer)
Or hard code the path.
Or put the path in a table and use DLookup when needed (this way, you don't have to modify code if the path changes)
Regardless of which method, you can immediately utilize the path string, or pass it to another form. For this, I'd set the OpenArgs of the opening form to be the path string value. See Open form method of the Docmd object.

All assuming that each user is using their own copy of the FE. If the templates are in the same folder as the BE, I don't see what the location of the FE has to do with the issue.

 

[riggsd]

I don't want to hard code the path as they want to use this database on other programs. I had thought of the path in the tables option so may try to use that.

I'd set the OpenArgs of the opening form to be the path string value. See Open form method of the Docmd object.

The code getting the current path isn't in a form, it's in a module so setting it in the form that calls the module would require passing it to the function, correct?

All assuming that each user is using their own copy of the FE. If the templates are in the same folder as the BE, I don't see what the location of the FE has to do with the issue.

Because the VBA code is in the FE so it considers the FE to be the current project which means it looks for the path where the FE is located.

 

[welshgasman]

Here are two functions that I found that will get BE path.
For the first, pass a linked table name.

HTH

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace


   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
   
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
   
   GetAccessBE_PathFilename = ""
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
   
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
   
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
    
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"


   Resume Proc_Exit
   Resume
             
End Function


Public Function GetBackEndPath() As String
On Error GoTo Err_Handler
    Const Attached = dbAttachedTable Or dbAttachedODBC
    Dim dbs As Database
    Dim tbl As TableDef
    Set dbs = DBEngine(0)(0)
    For Each tbl In dbs.TableDefs
        ' Gets the back end full path
        If (tbl.Attributes And Attached) <> 0 And Left(tbl.Connect, 10) = ";DATABASE=" Then
            GetBackEndPath = Mid(tbl.Connect, 11, Len(tbl.Connect) - 10)
            Exit For
        End If
    Next


    Set dbs = Nothing
    Set tbl = Nothing
    
Exit_Handler:
    Exit Function


Err_Handler:
    MsgBox (Err.Number & " " & Err.Description & " " & "SelectAll()")
    Resume Exit_Handler
    
End Function

 

[Micron]

Sorry, am on vacation and can't always answer same day.

setting it in the form that calls the module would require passing it to the function, correct?

I suspect you didn't research the OpenArgs parameter, otherwise I think you wouldn't have asked this. If the function in question determines the path, it could return the path to whatever calls it. If that procedure opens the form, you can pass the path to the form being opened. That path function could also open the form. Or the form Open Event could call the function and do whatever with it.

Obviously, there should only be one BE in use. Putting that path in a FE table has one advantage to retrieving it on the fly IMHO, and that is, if someone copies the BE, the retrieval method will still pass, whereas a DLookup on the table path value won't. Not that I think the last code posted is of no use. Rather, it or anything like it, can validate that the BE path stored in a table can be compared to the DLookup value.

There is also a function GetDataPath from Allen Browne's site.