Hi Art
I'm sorry, but I'm still not fully clear about your wishes. What you say below suggests that you want to build the folder hierarchy in Access and use Access to create and maintain the folder structure on disk. However, Crystal is helping you with code to read the structure from disk and populate the Access tables, which is the other way around! Which is it that you want?
Going back to your original question, you said:
> 1. the root of the folder hierarchy is always c:\work but it could be mapped out to a server drive
So, the actual folders in the "root folder" correspond to the records in tblCabinet, right? And the subfolders are "drawers", and so on?
> 2. I have a frmCabinets which the user use to create the folder hierarchy. At this point I need to the mechanics to create the actual folder structure based on the combination of combos and list boxes. ( I have uploaded a few snapshots of what frmMain and frmCabinets look like to the Needs Assistance folder.
There are a number of different ways to create folder hierarchies. VBA has a MkDir statement, but it fails if the folder already exists, or if the parent folder does not exist. The FileSystemObject.CreateFolder method suffers the same weakness.
So, creating "C:\work\Cabinet 1\Drawer 33\Folder 4\SubFolder16" can be a pain – you need to split that string up into segments, one for each folder level, then check that each folder level exists and, if not, create it.
Another way is to use an API function called MakeSureDirectoryPathExists, which does all this for you:
Private Declare Function MakeSureDirectoryPathExists _
Lib "IMAGEHLP.DLL" ( _
ByVal DirPath As String) _
As Long
I wrap this in a function that returns True if successful:
Public Function CreatePath(NewPath As String) As Boolean
CreatePath = MakeSureDirectoryPathExists(PathAddBackslash(NewPath)) <> 0
End Function
Note that this calls another useful function that adds a backslash to a path if there is not one there already:
Public Function PathAddBackslash(ByVal Path As String) As String
' Adds a final backslash to the path is there none already
If Len(Path) <> 0 Then
If Right(Path, 1) = "\" Then
PathAddBackslash = Path
Else
PathAddBackslash = Path & "\"
End If
End If
End Function
Another function you might find useful is this one:
Public Function BuildPath(ByVal Path As String, _
ParamArray Suffixes()) As String
' Combines two or more path components,
' inserting backslashes if needed.
Dim i As Integer
For i = 0 To UBound(Suffixes)
Path = PathAddBackslash(Path) & Suffixes(i)
Next
BuildPath = Path
End Function
> 3. Once the user sets the working folder, then I need to display a list of files in that folder. The user should be able to open any file in its natural application.
I take it only "SubFolders" have files in them – they can't exist in Cabinets, Drawers, or Primary Folders. This means that it's easy to build the full path to a subfolder, and all that is needed to define the "working folder" is the SubFolderID. Using the BuildPath function above, you can create a query joining all four tables (Cabinets, Drawers, PrimaryFolders and SubFolders) WHERE SubFolderID=WorkingFolderID(), and the full path to the working folder will be:
BuildPath( RootFolder(), txtCabinetLocation, txtDrawerLocation, txtPriFolderLocation, txtSubFolderLocation )
> 4. The application will need to remember the last selection in the combos and listboxes and working directory each time the user logs off and logs back in.
This is just a matter of "remembering" the SubFolderID. You could store it in a "Settings" table (in the back-end if it is shared, or the front-end if it a per-user setting) or in the registry, using SaveSetting/GetSetting.
The only remaining question is whether you want to store the filenames in the database or get them from the working folder on the fly.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, 2 August 2017 02:46
To: 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Subject: Re: RE: RE: [MS_AccessPros] Creating a folder structure based on combos?
I would like for the combo\listboxes to create the structure. This would only happen when the user chooses to add a new cabinet. If the the user chooses to manage the cabinet, then he\she needs to be able to modify the existing structure.
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Valar Dohaeris"
On Mon Jul 31 2017 14:26:26 GMT-0500 (Central Daylight Time), 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
OK, I understand. God bless "Management" :-)
The next question then, is "Do you want to duplicate the hierarchy of folder and file names in the Access database (and risk it getting out of sync every time a file is added or deleted) or do you want to populate the combo/list boxes directly from the folder structure?
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 1 August 2017 01:27
To: 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Subject: Re: RE: [MS_AccessPros] Creating a folder structure based on combos?
Yes, that is what the Management wants...
With Warm Regards,
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265 Ext. 130
Fax (605)964-1070
"Anyone who claimed that old age had brought them patience was either lying or senile."
On Sun Jul 30 2017 17:23:08 GMT-0500 (Central Daylight Time), 'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi Art
I can't help but think you are trying to reinvent the Windows File Explorer!
Is there any compelling reason to reproduce the file system folder/document hierarchy in Access?
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, 31 July 2017 03:40
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Creating a folder structure based on combos?
Currently writing a document management system in Access and would like to know if the following is possible:
I have my home screen called frmMain. On this form I have a cboCabinet (unbound - row source: SELECT tblCabinet.intCabinetID, tblCabinet.txtCabinetName, tblCabinet.txtCabinetLocation FROM tblCabinet; ) which is at the highest level of the hierarchy.
Then is the combo cboDrawer (unbound - row source:SELECT tblDrawer.intDrawerID, tblDrawer.intCabinetID, tblDrawer.txtDrawerName, tblDrawer.txtDrawerLocation FROM tblDrawer; which is the secondary level of the hierarchy.
Thirdly is my combobox cboPriFolder (unbound - row source: SELECT tblPrimaryFolder.intPriFolderID, tblPrimaryFolder.intDrawerID, tblPrimaryFolder.txtPriFolderName, tblPrimaryFolder.txtPriFolderLocation FROM tblPrimaryFolder; which is the tertiary level of the folder hierarchy.
Lastly is my listbox called lstSubFolder (unbound - rowsource:SELECT tblSubFolder.intSubFolderID, tblSubFolder.txtSubFolderName, tblSubFolder.intPriFolderID FROM tblSubFolder; This is the last level of the folder hierarchy.
on frmMain, this combination of combos and lstboxes is used to set the working folder.
So the following actions need to happen:
1. the root of the folder hierarchy is always c:\work but it could be mapped out to a server drive
2. I have a frmCabinets which the user use to create the folder hierarchy. At this point I need to the mechanics to create the actual folder structure based on the combination of combos and list boxes. ( I have uploaded a few snapshots of what frmMain and frmCabinets look like to the Needs Assistance folder.
3. Once the user sets the working folder, then I need to display a list of files in that folder. The user should be able to open any file in its natural application.
4. The application will need to remember the last selection in the combos and listboxes and working directory each time the user logs off and logs back in.
I know its a lot and as I go along I suspect I will have a lot more questions....
Thank you,
Art Lorenzini
Sioux Falls, SD
Ps. pictures will be uploaded shortly.
Posted by: "Graham Mandeno" <graham@mandeno.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (13) |
Tidak ada komentar:
Posting Komentar