primary key creating folder

[brsawvel]

Hello,

Is there a way I can have new primary keys (entered by the user) generate a folder by the same name as the PK onto a specific location on the local drive so that attachments associated with the PK are automatically inserted into that folder for future review?

 

[HiTechCoach]

Hello,

Is there a way I can have new primary keys (entered by the user) generate a folder by the same name as the PK onto a specific location on the local drive so that attachments associated with the PK are automatically inserted into that folder for future review?

Yes you could do that, but I would urge you to not use a primary key entered bu the user. I would recommend using a autonumber as the primary key. The user will not need to see it. The value entered by the user would be in a field that is index with No Dups. To the user, it will appear as if they entered the priamry key when in fact it is really assigned by the system.

This code will create the folder:

Function CreateFolder(MyPath As String)
  
'Created by Dane A. Miller aka dallr
'create recursive folders on a path eg. "c:\dane\fred\harry" 
Dim c As Integer
On Error Resume Next

If InStr(4, MyPath, "\") = 0 Then
    MkDir (MyPath)
Else
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
        For c = 4 To Len(MyPath)
            If Mid(MyPath, c, 1) = "\" Then
                MkDir Left(MyPath, c)
            End If
        Next c
End If
End Function

Hope this helps ...

 

[brsawvel]

Hey Thanks!

I have one (maybe two, or three) more question, though. Is there a way for the code above to add predesignated sub-folders with specific names within that folder?

 

[HiTechCoach]

Hey Thanks!

I have one (maybe two, or three) more question, though. Is there a way for the code above to add predesignated sub-folders with specific names within that folder?

Yes. It does create all the folder in the path if they do not exist.

This comment:

'create recursive folders on a path eg. "c:\dane\fred\harry"

may not be clear. It is trying to say that is will create each folder in the path if needed.

 

[brsawvel]

Sorry, I'm a little confused...

If I wanted to have the form generate a folder each time a new entry is made, can I also have that new folder have static subfolders?...

i.e.

entry 1 --> c:/program files/access database/folder 1/subfolder A
c:/program files/access database/folder 1/subfolder B
c:/program files/access database/folder 1/subfolder C

entry 2 --> c:/program files/access database/folder 2/subfolder A
c:/program files/access database/folder 2/subfolder B
c:/program files/access database/folder 2/subfolder C

entry 3 --> c:/program files/access database/folder 3/subfolder A
c:/program files/access database/folder 3/subfolder B
c:/program files/access database/folder 3/subfolder C

 

[jibbajabba]

Perfectly Possible...

Just coded something similar, I use the 'Microsoft scripting runtime' object to manage interaction with the file system - it's very simple to use.

Couple of observations - Don't create the folders in the Access program folder, it's much cleaner to give the database its own discrete folder.

Also, If users are going to be using files in these folders independently of Access (via excel or something) then you should think about how they are going to be able the correctly identify the folder.

Code that creates...

  Dim f As FileSystemObject, sStorage As String
  
  If IsNull(Me.Incident_Number) Then Exit Sub
  Set f = New FileSystemObject
  
  sStorage = [B]GetPath[/B](GSINCIDENT, Incident_Number) ' get the path
  If Not f.FolderExists(sStorage) Then ' if the folder exists don't create it
    f.CreateFolder (sStorage)
  End If

  sStorage = GetPath(GSIMAGES, Incident_Number)  'create sub directory
  If Not f.FolderExists(sStorage) Then 
    f.CreateFolder (sStorage)
  End If  

  sStorage = GetPath(GSEMAIL, Incident_Number)
  If Not f.FolderExists(sStorage) Then
    f.CreateFolder (sStorage)
  End If

GetPath - Use a function to build the folder path, I store the root folder for database files and names of subdirectory in a configuration table in the database - Hard Coding paths etc. into code will only cause headaches further down the line.

 

[brsawvel]

I'm a real newbie and ignoramus at Access...

I created a test table and form with button that has the code you provided in the "on-click". But after that I'm completely lost. I kind of understand your last quote..

GetPath - Use a function to build the folder path, I store the root folder for database files and names of subdirectory in a configuration table in the database - Hard Coding paths etc. into code will only cause headaches further down the line.

But not enough to know how to accomplish the task.

Sorry if I'm being a bug.

 

[jibbajabba]

Create a directory called C:\testfolder\

create a table called

dbconfig with two text fields "entry" and "dbvalue"

insert two records

entry = "root" dbvalue = "C:\testfolder\"
entry = "subdir1" dbvalue = "firstfolder"

Create a new query - choose the sql view and paste in

"Select dbValue From dbconfig"

save it and call it qryConfigValue

in the form code module paste the following

Function GetPath(FolderValue as String, sID as string) as string

  Dim rs as recordset, sRoot as string
	
  set rs = currentdb.openrecordset( "Select dbValue From qryConfigValue WHERE entry = 'root'"
  
  'test rs is open etc
  sRoot = rs(0)
  'Close rs etc

  Select Case FolderValue

    Case "Root"
      GetPath = sRoot & sID
    
    Case Else
      
       set rs = currentdb.openrecordset("Select dbValue From qryConfigValue WHERE entry = '" & FolderValue & "'"

     'test rs is open etc
     GetPath = sRoot & sID & "\" & rs(0)
     'Close rs etc

  End Select

End function

Behind your Button you want

 If IsNull(Me.txtID) Then Exit Sub
  Set f = New FileSystemObject
  
  sStorage = GetPath("Root",cstr(me.txtID)) ' get the path
  If Not f.FolderExists(sStorage) Then ' if the folder exists don't create it
    f.CreateFolder (sStorage)
  End If

  sStorage = GetPath("subdir1",cstr(me.txtID))  'create sub directory
  If Not f.FolderExists(sStorage) Then 
    f.CreateFolder (sStorage)
  End If

You'll need a text control on your form called txtID that has the unique Value you wish to use as your foldername.

 

[brsawvel]

I created the database, but I am obviously doing something wrong. I attached my creation if you wouldn't mind telling me what I did wrong.