Use more than one field name value in creating a file structure

[jpihpa]

Hi, I am brand new to the site and have only been working on programming my database for the last three weeks. I have gotten a lot of information from here but I have one thing that is not wanting to work and I can't find information on it. What I want to do is use three fields in my database as my file structure on the server we use.



I have three fields named ProjectNumber, County, City and the "strPath" I want to use is basically (can't name the true path) "X:\AAA\BBB\CCC\DDD"

The jist of what I want to do is make a code that says to create a file structure as such - X:\AAA\BBB\CCC\DDD\County\City\ProjectNumber

I have the code that creates the County directory just fine (thanks to information found here) but I can't figure out what to do so that it will take all of the fields and make the ENTIRE path. This is the code that I used to get it to make the County directory just fine:

Private Sub ProjectNumber_AfterUpdate()
strPath = "X:\AAA\BBB\CCC\DDD" & Me.County
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If
Shell "explorer.exe " & strPath, vbNormalFocus
End Sub

As you can see, what it does is creates the directory and then brings it up once it is created. I want the program to check and make sure though that if the county I put into the record exists, it doesn't try to create a directory for it and then not work. For instance, if the county is already up as a folder, I don't want it to try to create a new directory that already exists, but otherwise go ahead and create a new city directory instead. Likewise, if the county and city already exist, then just put a new project number directory under the city directory. I know this is probably a simple thing but I can't get it to work. Again I am trying to use the "Me.County" then subdirectory "Me.City" and then subdirectory "Me.ProjectNumber."

If anyone has some advice and you could tell me how to get this to work so I don't have to fuss with it anymore I would appreciate it beyond anyone's understanding.

jpihpa

[June7]

Modify the strPath variable at each level and check for existence.
Don't forget the \ at each concatenation. Something like:

Private Sub ProjectNumber_AfterUpdate()
strPath = "X:\AAA\BBB\CCC\DDD" & "\" & Me.County
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
strPath = strPath & "\" & Me.City
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
strPath = strPath & "\" & Me.ProjectNumber
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
Shell "explorer.exe " & strPath, vbNormalFocus
End Sub

[jpihpa]

Thank you so much!!! I think the problem I was having was that I didn't know you could, or at least how to, specify a new strPath each time. Here is how I got it to work for anyone that needs this information in the future:

Private Sub ProjectNumber_AfterUpdate()
strPath = "X:\AAA\BBB\CCC" & "\" & Me.County
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If

strPath = "X:\AAA\BBB\CCC" & "\" & Me.County & "\" & Me.City
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If

strPath = "X:\AAA\BBB\CCC" & "\" & Me.County & "\" & Me.City & "\" & Me.ProjectNumber
If Len(Dir(strPath, vbDirectory)) = 0 Then
MkDir strPath
End If

Shell "explorer.exe " & strPath, vbNormalFocus
End Sub

I also put in a hyperlink button and made it go to the last strPath, the coding for my hyperlink is as follows:

Private Sub Command212_Click()
Command212.HyperlinkAddress = "X:\AAA\BBB\CCC" & "\" & Me.County & "\" & Me.City & "\" & Me.ProjectNumber
End Sub

Thank you again to June7 for a successful tip on how to accomplish this. Now my only issue is possibly putting the address of the project instead of the project number. What I want to do is get each part of the address (I separated them out into a house number, cardinal direction, street name, street type, and then unit/apartment number if they exist) and name the final folder with those fields instead of/right after (haven't decided) the project number. I know by putting Me.ProjectNumber & " " & Me.HouseNumber & " " & Me.StreetCardinalDirection & " " & Me.StreetName & " " & Me.StreetType that I can get what I want for non-condo/apartment addresses, but my question would be on the condo/apartments ones...if there is an apartment number I want it to add & " " & "Unit" & " " & Me.UnitApartmentNumber to the string but if its just a regular house address, I just want to cut it off after Me.StreetType. Any ideas? (After this question is answered I will mark this thread as solved. Thanks again.)

[June7]

Use an IIf expression.

& IIf(IsNull(Me.UnitApartmentNumber),"", " " & "Unit" & " " & Me.UnitApartmentNumber)

[jpihpa]

Thank you so much for your help June7. You are awesome! This thread will be marked as solved.