Results 1 to 7 of 7
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    can this be improved upon?

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The suggested code goes in the same place as your original code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

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

    Steve

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I noticed some typos in my suggested code and edited the post to fix.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi June

    Yeah i had found and resolved those

    many thanks again

    Steve

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums