Page 3 of 3 FirstFirst 123
Results 31 to 43 of 43
  1. #31
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    from post 30, this is the refreshed cboViewPO and the code that adds the record, like mentioned, can other parts of the PDF be read to auto add ?

    Dim strPath, FlowPath, MySL, MyPO, mName, mPDFExt, mStart, mDate, PO, SL, mCust, Path, POExist, xlFileName As String


    Dim rs As DAO.Recordset
    Dim ShipDate, MyDate, DateNow, DateExist As Date
    Dim FileName, SLExists, SLOpen, DelToExist, LiftNoExist, ManName, ManPath, Lt As String
    Dim objXL As Excel.Application
    Dim XLFromPath, XLToPath, FromName, ToName, Fp, Hp As String
    Dim xlIn, xlTo, xlCurrName As String

    PO = "PO-"

    If Me.cboFolder <> "" Then
    If Left(Me.cboViewPO, 3) = "SL-" Then
    SLOpen = Left(Me.cboViewPO, 9)
    If Not IsNull(DelToExist = DLookup("DelTo", "tblEdit", "[SLNumber] = '" & SLOpen & "'")) Then
    DoCmd.CancelEvent
    Else
    Set rs = CurrentDb.OpenRecordset("Select * From tblEdit")
    With rs
    .AddNew
    !Customer = Me.cboCustomer
    !CustAdd1 = Me.txtCustAdd1
    !CustAdd2 = Me.txtCustAdd2
    !CustTown = Me.txtCustTown
    !CustPostCode = Me.txtCustPostCode
    !ShipmentDate = Me.cboShipmentDateIndex1
    !PONumber = PO, WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !SLNumber = SL ' AUTO ADDED FROM cboViewPO
    !LiftType = Lt, this is always the same product, auto adds
    !Added = "New"
    !Status = "Planning"
    !DueDay = Me.txtDay
    !Source = Left(Me.cboSource, 3)
    !DelTo = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !Add1 = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !Add2 = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !Town = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !PostCode = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    !ItemDetails = ""
    , WOULD LIKE TO AUTO ADD IF PDF CAN BE READ
    .Update
    .Close
    End With
    Forms!frmMainMenu!frmIndex1DS.Requery
    End If
    End If
    End If

  2. #32
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I just did a bing search for 'can pdf be read using vba' and got lots of hits
    The first was https://www.bing.com/videos/search?q...ds&form=QBVDMH

    I've done similar things to get info from Word forms but not sure whether I've done the same from pdf. If you need detailed advice on this, suggest you try an acrobat forum
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #33
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, yes i will do a search, done quite a lot from access to excel and word, arranging paragraphs for word and resizing and formatting excel cells etc, all works well, i possibly was thinking of converting pdf to another format ie excel then it would work from that but i do think this is a difficult one!, going to be trying your backup method soon via the exitDB button or a temp button to start with... many thanks

  4. #34
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ok good luck. I confess to not reading the four consecutive posts 28-31 in detail.
    Busy most of today but report back if you want further help.

    Reminder. PLEASE use code tags in your posts. That's the # button in the toolbar
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #35
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, I quickly adjusted your backupBEDatabase to our path names and DB names, when i run the module from a command button, it comes up with Compile Error Sub or function not defined

    Code

    On Error GoTo Err_Handler


    'creates a copy of the backend database to the backups folder with date/time suffix
    Dim fso As Object
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    Dim STR_PASSWORD As String

    'if your BE database is password protected, enter it below or state where it can be found
    ' STR_PASSWORD = "" 'enter password
    ' STR_PASSWORD = Nz(DLookup("ItemValue", "tblProgramSettings", "ItemName='Pwd'"), "") 'example for stored password

    Set fso = CreateObject("Scripting.FileSystemObject")

    strFilename = "DMT Live.accdb" 'replace with your BE file
    strFileType = mID(strFilename, InStr(strFilename, ".")) 'e.g. .accdb

    strOldPath = "T:\DMT" & strFilename 'replace GetLinkedDBFolder with your BE folder

    'replace GetBackupsFolder with your backups folder
    strNewPath = "T:\DMT\DB Backup" & Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType

    'set path for temp file
    strTempPath = "T:\DMT\DB Backup" & Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType

    ' Debug.Print strOldPath
    ' Debug.Print strTempPath
    ' Debug.Print strNewPath

    If MsgBox("This procedure is used to make a backup copy of the back end database" & vbCrLf & _
    "The backup will be saved to the Backups folder with date/time suffix" & vbCrLf & _
    vbTab & "e.g. " & strNewPath & " " & vbCrLf & vbCrLf & _
    "This can be used for recovery in case of problems " & vbCrLf & vbCrLf & _
    "Create a backup now?", _
    vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then
    Exit Function
    Else
    'copy database to a temp file
    fso.CopyFile strOldPath, strTempPath
    Set fso = Nothing

    'compact the temp file (with password)
    DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & STR_PASSWORD & "", , ";PWD=" & STR_PASSWORD & ""
    'OR compact the temp file code if no password
    'DBEngine.CompactDatabase strTempPath, strNewPath

    'delete the tempfile
    Kill strTempPath

    DoEvents

    'get size of backup
    newlength = FileLen(strNewPath) 'in bytes

    'setup string to display file size
    If FileLen(strNewPath) < 1024 Then 'less than 1KB
    strFileSize = newlength & " bytes"
    ElseIf FileLen(strNewPath) < 1024 ^ 2 Then 'less than 1MB
    strFileSize = Round((newlength / 1024), 0) & " KB"
    ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
    strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
    Else 'more than 1GB
    strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
    End If

    DoEvents

    End If

    MsgBox "The Access backend database has been successfully backed up. " & _
    "The backup file is called " & vbCrLf & vbTab & strNewPath & vbCrLf & vbCrLf & _
    "The file size is " & strFileSize, vbInformation, "Access BE Backup completed"

    Exit_Handler:
    Exit Function

    Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
    MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & _
    Err.Description, vbCritical, "Error copying database"
    End If
    Resume Exit_Handler

    COMMAND BUTTON

    'Dim sBackendPath As String
    'Dim sFolder As String
    'Dim sFilename As String
    'Dim sExtension As String
    'sBackendPath = "T:\DMT\DB Backend\DMT Live_be" & "accdb"
    'sFolder = "T:\DMT\DB Backup"
    'sFilename = "DMT Live_be" & "accdb"
    ' assume sBackendPath contains the full path to the backend
    'Call ParseFullPath(sBackendPath, sFolder, sFilename, sExtension)
    'sFolder = sFolder
    'sFilename = sFilename & Format(Now(), "dd-mm-yy")
    'FileCopy sBackendPath, sFolder & sFilename


    BackupFEDatabase


    BackupBEDatabase

  6. #36
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Reminder. PLEASE use code tags in your posts. That's the # button in the toolbar!!!!
    ALSO please include the entire code for each procedure including first line (procedure name) & last line (EndSub/Function)

    Have you saved my backup code to a MODULE?
    Finally, if you run the code & break on the error, what does is say is 'NOT DEFINED'

    If you edit your last post to add code tags & the full code & the answer to these questions, I'll look at it later
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #37
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    i had pressed # key in last post, don't understand what it's for but have done

  8. #38
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not the # key, press the # icon on post editor toolbar. Pressing # icon will generate CODE tags in post. Paste your code between the CODE tags.

    Posted code does not include procedure declaration lines nor End lines.
    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.

  9. #39
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    Not the # key, press the # icon on post editor toolbar. Pressing # icon will generate CODE tags in post. Paste your code between the CODE tags.
    Exactly. Just as I wrote in post 11:
    if you use code tags by clicking the # button in the toolbar, it makes code easier to read and fixes the phantom 'spa ce' error.
    just paste or type your code between the [c o d e] and [/c o d e] placeholders. NOTE spaces added deliberately to fool the forum software
    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	12.7 KB 
ID:	34237

    Now I'm just waiting on you to respond to the points made in post 36
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #40
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, yes i have added to a module, it say sub or function nor defined, im not on work database at moment unless i download from server, i hope i do this correctly, i will paste the code between # icons

    Code:
    On Error GoTo Err_Handler 'creates a copy of the backend database to the backups folder with date/time suffix Dim fso As Object Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String Dim newlength As Long Dim STR_PASSWORD As String 'if your BE database is password protected, enter it below or state where it can be found ' STR_PASSWORD = "" 'enter password ' STR_PASSWORD = Nz(DLookup("ItemValue", "tblProgramSettings", "ItemName='Pwd'"), "") 'example for stored password Set fso = CreateObject("Scripting.FileSystemObject") strFilename = "DMT Live.accdb" 'replace with your BE file strFileType = mID(strFilename, InStr(strFilename, ".")) 'e.g. .accdb strOldPath = "T:\DMT" & strFilename 'replace GetLinkedDBFolder with your BE folder 'replace GetBackupsFolder with your backups folder strNewPath = "T:\DMT\DB Backup" & Left(strFilename, InStr(strFilename, ".") - 1) & "_" & Format(Now, "yyyymmddhhnnss") & strFileType 'set path for temp file strTempPath = "T:\DMT\DB Backup" & Left(strFilename, InStr(strFilename, ".") - 1) & "_TEMP" & strFileType ' Debug.Print strOldPath ' Debug.Print strTempPath ' Debug.Print strNewPath If MsgBox("This procedure is used to make a backup copy of the back end database" & vbCrLf & _ "The backup will be saved to the Backups folder with date/time suffix" & vbCrLf & _ vbTab & "e.g. " & strNewPath & " " & vbCrLf & vbCrLf & _ "This can be used for recovery in case of problems " & vbCrLf & vbCrLf & _ "Create a backup now?", _ vbExclamation + vbYesNo, "Copy the Access BE database?") = vbNo Then Exit Function Else 'copy database to a temp file fso.CopyFile strOldPath, strTempPath Set fso = Nothing 'compact the temp file (with password) DBEngine.CompactDatabase strTempPath, strNewPath, ";PWD=" & STR_PASSWORD & "", , ";PWD=" & STR_PASSWORD & "" 'OR compact the temp file code if no password 'DBEngine.CompactDatabase strTempPath, strNewPath 'delete the tempfile Kill strTempPath DoEvents 'get size of backup newlength = FileLen(strNewPath) 'in bytes 'setup string to display file size If FileLen(strNewPath) < 1024 Then 'less than 1KB strFileSize = newlength & " bytes" ElseIf FileLen(strNewPath) < 1024 ^ 2 Then 'less than 1MB strFileSize = Round((newlength / 1024), 0) & " KB" ElseIf newlength < 1024 ^ 3 Then 'less than 1GB strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 2), 1) & " MB)" Else 'more than 1GB strFileSize = Round((newlength / 1024), 0) & " KB (" & Round((newlength / 1024 ^ 3), 2) & " GB)" End If DoEvents End If MsgBox "The Access backend database has been successfully backed up. " & _ "The backup file is called " & vbCrLf & vbTab & strNewPath & vbCrLf & vbCrLf & _ "The file size is " & strFileSize, vbInformation, "Access BE Backup completed" Exit_Handler: Exit Function Err_Handler: Set fso = Nothing If Err <> 0 Then MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & _ Err.Description, vbCritical, "Error copying database" End If Resume Exit_Handler COMMAND BUTTON 'Dim sBackendPath As String 'Dim sFolder As String 'Dim sFilename As String 'Dim sExtension As String 'sBackendPath = "T:\DMT\DB Backend\DMT Live_be" & "accdb" 'sFolder = "T:\DMT\DB Backup" 'sFilename = "DMT Live_be" & "accdb" ' assume sBackendPath contains the full path to the backend 'Call ParseFullPath(sBackendPath, sFolder, sFilename, sExtension) 'sFolder = sFolder 'sFilename = sFilename & Format(Now(), "dd-mm-yy") 'FileCopy sBackendPath, sFolder & sFilename BackupFEDatabase BackupBEDatabase

  11. #41
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    PS the code is copied in my Public Functions Module as i have other website and vehicle tracing in public modules so they can be called from anywhere in VBA, done the same with Backup. PS hope i added the # code correctly !!

    Many Thanks

  12. #42
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code still does not show procedure declaration and End lines, which may be why it errors. Those lines would be like:

    Code:
    Option Compare Database
    Option Explicit
    
    ____________________________
    
    Sub SendEmail()
    'your code here ...
    End Sub
    ____________________________
    
    Private Sub btnEmail_Click()
    'your code here ...
    End Sub
    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. #43
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DMT Dave View Post
    Hi ridders52, yes i have added to a module, it say sub or function nor defined
    Following on behind June again, look back to this post where I uploaded my 2 backup functions: https://www.accessforums.net/showthr...344#post398344

    We can only respond to what you post and what you have posted is incomplete
    The first function starts and ends with:

    Code:
    Public Function BackupBEDatabase()
    
    On Error GoTo Err_Handler
    
    'creates a copy of the backend database to the backups folder with date/time suffix 
    
    ....
    Err_Handler:
        Set fso = Nothing
        If Err <> 0 Then
          MsgBox "Error " & Err.Number & " in BackupBEDatabase procedure : " & _
             Err.description, vbCritical, "Error copying database"
        End If
        Resume Exit_Handler
        
    End Function
    The 2 lines in RED are missing in your post - WHY?
    If you didn't include them, the command button code will fail at the line BackupBEDatabase

    There was a second function BackupFEDatabase - did you copy all of that into your module? If not, the command button code will fail at the line BackupFEDatabase


    Now that may not be your problem because your command button code also lacks the start / end lines
    That's what I getting at when I said to post the full code

    I also wouldn't run both backup routines from one button as there is a risk the first function won't complete before the second one starts.
    Run each from separate command buttons

    Back to you ...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Gmail signature with email address syntax error
    By Gina Maylone in forum Access
    Replies: 2
    Last Post: 01-26-2016, 07:41 AM
  2. Add Outlook Signature to Email - CODE
    By floyd in forum Programming
    Replies: 1
    Last Post: 11-27-2013, 09:23 AM
  3. Code to include Outlook Signature in Email from Access
    By floyd in forum Code Repository
    Replies: 0
    Last Post: 11-27-2013, 08:52 AM
  4. Replies: 7
    Last Post: 11-27-2013, 08:37 AM
  5. adding signature to the email sendobject
    By webisti in forum Access
    Replies: 3
    Last Post: 07-10-2012, 03:08 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