Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 42
  1. #16
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Your NewName variable is outside the loop so its not changing. Add some debug.prints so you can see how the variables resolve.
    Another thing to consider is your use of input box. What happens if they enter 4? Also what happens if there are no records returned by your recordset?
    I'm also not familiar with docmd.cancelevent. Does that exit the procedure?



    edit: a quick test showed that docmd.cancelevent does nothing. the procedure will continue. You probably want "exit sub"

  2. #17
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Moke 123, all works great , thank you so much guy's for all of your help

  3. #18
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Moke, June7, the renaming of the files works very well, again thank you so much, i am now having a problem killing the files after we have done what we need to do, can you help as i have used Kill file before but now debugging ??

    Click image for larger version. 

Name:	Capture1.JPG 
Views:	20 
Size:	14.0 KB 
ID:	39773

    Click image for larger version. 

Name:	Capture5.JPG 
Views:	21 
Size:	88.1 KB 
ID:	39774

  4. #19
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    The f1, f2, f3 etc is the correct file name mDealer & "-" & Format(Now(),"dd-mm-yy") & "-1" & ".jpg"

    imgPath is correct "T:\Images\Collections" & Me.cboDealer & ""

  5. #20
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Is it that i have to delete the files before Kill File ?

  6. #21
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Solved, Kill imgPath & "*.*"

  7. #22
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    The code above limits you to 3 attachments but your original post stated there may be up to 6.
    You could construct a loop to attach more or less.
    something along the lines of
    Code:
    for i = 1 to FileQty
    .attachment.add imgPath & ("f" & i)
    next i

  8. #23
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Ah brillaint, thank you Moke123, this eliminates having .attachments.Add if there is 1 or 6 files ?

    Yes i did mention six but still proramming to allow for File Qty but your method will attach however many files are in the folder, is that correct ??

    Again, greatly appreciated

  9. #24
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Would the f in the brackets account for 1 or any qty of file names ??

    I have Stringed f1 and f2 and f3 as File names ending in -1.jpg and f2 is -2.jpg and f3 is -3.jpg

    Will your loop attach all files in that folder ????

    Much appreciated

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    The number of loop iterations is determined by FileQty. If this is equal to the number of files in folder then all will be attached.
    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.

  11. #26
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi June7, thank you for your response, i was unsure what the ("f" is in the brackets, i was assuming that is my file names ie: f1, f2,f3 ? can i now delete f1, f2 and f3 ?

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    "f" is just literal text. Can use whatever text you want.

    If you are done with renamed files you can delete.
    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.

  13. #28
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Moke123, i love this idea to prevent having to use IF, End IF or Select Case FileQty

    I am getting an error when testing this, there is also an Integer set to ic, this is copying the files to a backup folder (accPath) before attaching then deleting

    Do I have the code in the incorrect place or do I have this wrong all together ??

    I am getting runtime error 53 File Not Found ?

    I have commented out my select case file qty code to add much shorter one in of your suggestion of a loop of files to attach but the file not found error comes up!

    are you able to correct my code and highlight corrected ?

    I want to copy these files to AccPath then attach them to mail then delete them from imgPath, deleting from imgPAth works ok

    Code:
    Set fso =CreateObject("Scripting.FileSystemObject")
    Set ObjFiles = fso.getfolder(imgPath).Files
    FileQty = ObjFiles.Count
    'Select Case FileQty
    'Case 1
    'f1 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-1" & ".jpg"
    'FileCopy imgPath & f1, AccPath & f1
    'Case 2
    'f1 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-1" & ".jpg"
    'f2 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-2" & ".jpg"
    'FileCopy imgPath & f1, AccPath & f1
    'FileCopy imgPath & f2, AccPath & f2
    'Case 3
    'f1 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-1" & ".jpg"
    'f2 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-2" & ".jpg"
    'f3 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-3" & ".jpg"
    'FileCopy imgPath & f1, AccPath & f1
    'FileCopy imgPath & f2, AccPath & f2
    'FileCopy imgPath & f3, AccPath & f3
    'Case 4
    'f1 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-1" & ".jpg"
    'f2 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-2" & ".jpg"
    'f3 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-3" & ".jpg"
    'f4 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-4" & ".jpg"
    'FileCopy imgPath & f1, AccPath & f1
    'FileCopy imgPath & f2, AccPath & f2
    'FileCopy imgPath & f3, AccPath & f3
    'FileCopy imgPath & f4, AccPath & f4
    'Case 5
    'f1 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-1" & ".jpg"
    'f2 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-2" & ".jpg"
    'f3 = Me.cboDealerIndex7 & "-" & Format(Now(),"dd-mm-yy") & "-3" & ".jpg"
    'f4 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-4" & ".jpg"
    'f5 = Me.cboDealerIndex7 & "-" &Format(Now(), "dd-mm-yy") & "-5" & ".jpg"
    'FileCopy imgPath & f1, AccPath & f1
    'FileCopy imgPath & f2, AccPath & f2
    'FileCopy imgPath & f3, AccPath & f3
    'FileCopy imgPath & f4, AccPath & f4
    'FileCopy imgPath & f5, AccPath & f5
    'End Select
    For ic = 1 To FileQty
    FileCopy imgPath & ic, AccPath & ic
    Next ic
     
    Set MyItem = MyApp.CreateItem(olMailItem)
    With MyItem
        .To = MailTo
        .CC = mCC
        For i = 1 To FileQty
        .Attachments.Add imgPath & i
        Next i
       'Select Case FileQty
       'Case 1
       '.Attachments.Add imgPath & f1
       'Case 2
       '.Attachments.Add imgPath & f1
       '.Attachments.Add imgPath & f2
       'Case 3
       '.Attachments.Add imgPath & f1
       '.Attachments.Add imgPath & f2
       '.Attachments.Add imgPath & f3
       'Case 4
       '.Attachments.Add imgPath & f1
       '.Attachments.Add imgPath & f2
       '.Attachments.Add imgPath & f3
       '.Attachments.Add imgPath & f4
       'Case 5
       '.Attachments.Add imgPath & f1
       '.Attachments.Add imgPath & f2
       '.Attachments.Add imgPath & f3
       '.Attachments.Add imgPath & f4
       '.Attachments.Add imgPath & f5
       'End Select
        .subject = "Consignment" &" " & Me.txtConsignment
        .Body = TOD & " " &"Your consignment" & " " & Me.txtConsignment &" " & "is now ready for collection," & Chr(10)& Chr(10) & _
        "Total Boxes:" & "" & Me.txtTotalBoxes & Chr(10) & Chr(10) & _
        "Package Information:" &" " & Me.cboPallets & "x" & " " &Me.cboPalletType & Chr(10) & Chr(10) & _
         "Please ask your courier toquote consignment:" & " " & "C-" &Me.txtConsignment & " " & "upon collection" &Chr(10) & Chr(10) & _
         "Estimated Total Weight:"& " " & Me.txtTotalWeight & " " &"Kg's" & Chr(10) & Chr(10) & _
        "To help prevent delays, pleaseinform us or ask your courier to contact us with the anticipated collectionday:" & Chr(10) & Chr(10) & _
        "Consignment details are:"& Chr(10) & Chr(10) & _
        mBody & Chr(10) & Chr(10) & _
        stSender
        .Display
        End With
        Kill imgPath & "*.*"
    Last edited by DMT Dave; 09-22-2019 at 08:11 AM.

  14. #29
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Where is code setting imgPath and AccPath?

    Why are you concatenating ic to imgPath?
    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.

  15. #30
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    HI June7, ic is INteger (image copy) and accPath is a customer location on the server

    So we have imgPath where we renamed the files by loop (files are present)
    we now want to copy these files to ic (images copy) ic is integer to accPAth
    For ic = 1 To FileQty

    FileCopy imgPath & ic, AccPath & ic

    Next ic

    then loop through the files to attach to mail (as Moke123 suggestion which is better than what I was doing via select case fileQty
    For i = 1 To FileQty

    .Attachments.Add imgPath & i

    Next i

    then remove the files
    Kill FIles (works ok)

    so I am looking to loop files and copy them to another path (accPath) ?

    So I am looking to find where I have gone wrong in loop the files from imgPath to attach to mail ?

    Thank you for your reposne
    When i tried

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Renaming a Module
    By George in forum Modules
    Replies: 4
    Last Post: 09-04-2017, 06:43 AM
  2. Renaming values?
    By brownk in forum Queries
    Replies: 6
    Last Post: 06-25-2012, 12:56 PM
  3. Renaming Utility
    By bginhb in forum Access
    Replies: 3
    Last Post: 11-02-2011, 03:11 PM
  4. Renaming a file using VBA
    By bfaucher in forum Programming
    Replies: 1
    Last Post: 11-01-2011, 02:56 PM
  5. Renaming fields
    By WilsonsW in forum Access
    Replies: 10
    Last Post: 03-25-2011, 01:20 PM

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