can this be improved upon?

[sdel_nevo]

Hi All

i have this code running on my customer details "on load" event

If Len(Dir(DLookup("FilePath", "Company Details", "CompanyID = 1") & "\" & Me.Full_Name, vbDirectory)) = 0 ThenMkDir (DLookup("FilePath", "Company Details", "CompanyID = 1")) & "\" & Me.Full_Name
End If




If Len(Dir(DLookup("FilePath", "Company Details", "CompanyID = 1") & "\" & Me.Full_Name & "\Invoices", vbDirectory)) = 0 Then
MkDir (DLookup("FilePath", "Company Details", "CompanyID = 1")) & "\" & Me.Full_Name & "\Invoices"
End If


If Len(Dir(DLookup("FilePath", "Company Details", "CompanyID = 1") & "\" & Me.Full_Name & "\Quotes", vbDirectory)) = 0 Then
MkDir (DLookup("FilePath", "Company Details", "CompanyID = 1")) & "\" & Me.Full_Name & "\Quotes"
End If


If Len(Dir(DLookup("FilePath", "Company Details", "CompanyID = 1") & "\" & Me.Full_Name & "\Statements", vbDirectory)) = 0 Then
MkDir (DLookup("FilePath", "Company Details", "CompanyID = 1")) & "\" & Me.Full_Name & "\Statements"
End If

this code makes the relivent folders "invoice , Quotes and statements" in the path set in the company details form

this works really well but i am wondering if this can be reduced to something more efficient,

as it is at the moment it works really well i am just curious to see if it can be improved upon

Steve

[June7]

Why use the DLookup if the value returned is always the same because the criteria never changes? This is to allow for deployment to different user companies?

Instead of the multiple DLookups, run it once and set a variable.

Dim strPath As String
strPath = Nz(DLookup("FilePath", "Company Details", "CompanyID = 1"),"")
If strPath <> "" Then
strPath = strPath & "\" & Me.Full_Name
If Dir(strPath, vbDirectory) = "" Then MkDir strPath
If Dir(strPath & "\Invoices", vbDirectory) = "" Then MkDir strPath & "\Invoices"
If Dir(strPath & "\Quotes", vbDirectory) = "" Then MkDir strPath & "\Quotes"
If Dir(strPath & "\Statements", vbDirectory) = "" Then MkDir strPath & "\Statements"
End If

[sdel_nevo]

Hi June

Many thanks for getting back

to make this a variable, would this be put in a module?

i think the plan for the company is to be able to use this with more than 1 business

steve

[June7]

The suggested code goes in the same place as your original code.

[sdel_nevo]

Hi

I Just realised that sorry for the silly question and many thanks for the help

Steve

[June7]

I noticed some typos in my suggested code and edited the post to fix.

[sdel_nevo]

Hi June

Yeah i had found and resolved those

many thanks again

Steve