Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Nov 2004
    Posts
    78

    Unanswered: Multiple snapshots for each report

    I currently use the following function

    Private Sub Report_Activate()
    DoCmd.OutputTo acOutputReport, "rptInvoice", "Snapshot", "C:\Snapshots\" & Reports!Invoice.OrderNo & ".snp", True
    End Sub

    However, I would like to keep a copy of each invoice version submitted to the customer (usually there is no more than 4).
    My solution would be to create 4 fields “snapshot1“ , “snapshot2“ , “snapshot3“ , and “snapshot4“ for each order.
    The Report_Activate function will be removed.
    It will be required to select the version before working with the following buttons.
    There will be a button “Create snapshot“ which will create the file and save it into the snapshot folder, the file name will be the orderNo.
    For the second version, the file name will be the OrderNo & - & 1 like 5657, 5657-1, 5657-2

    Another button “Add“, which will fill in the file name into the field snapshot1
    Another button “Del“, which will delete the file name from the field snapshot1
    Another button “View“, which will search for a snapshot file with a name matched the field snapshot1

    What I need now, is a function that searches in the folder for a snapshot with the name matched the field on the form. Can anyone help me with this?

    First I was thinking of creating a sub table that keeps the snapshot names for each order, but it turned out to be more coding.

    I would appreciate if you can also tell me if there are any other ways.

    Thank you
    Joe

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    to directly answer, you can use the "Scripting.FileSystemObject" to search folders for a given field name.

    However, I question the entire premise of storing the files to begin with. Are there special circumstances that wouldn't allow you to (re)generate the reports on the fly at any point in time?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Posts
    78
    In my business, the data is being changed from time to time. For an example, I would not issue a credit; I will revise the invoice and send it again. So I would like to see all versions.
    Thanks

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the easiest method is to amend the date and/or time to the file name, it also gives you a meaningful reference for the file. otherwise Teddy's just about coverted it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Joe1
    In my business, the data is being changed from time to time. For an example, I would not issue a credit; I will revise the invoice and send it again. So I would like to see all versions.
    Thanks
    I would approach this by preserving the changes and handling versioning in the database itself. If you have critical changes in your database that affect your customers, you would behoove yourself to accomodate historical tracking of those changes at the database level as opposed to relying on external file storage.

    Anyways, that scripting.filesystemobject I mentioned is probably what you're after.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    I think basically he wants to log changes with out logging them in the DB itself, for reference rather than data integrity
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by m.timoney
    I think basically he wants to log changes with out logging them in the DB itself, for reference rather than data integrity
    I understand this. However when one is dealing with an invoicing database, preserving changes in-line can be a lifesaver.

    I leave it to him though, it's his baby.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Nov 2004
    Posts
    78
    I’m searching the help file for FileSystemObject. However, if you have the code already, I would appreciate if you can share it with me.
    Thanks

  9. #9
    Join Date
    Nov 2004
    Posts
    78
    I do not want to save a copy in a database. Like I said is what I need. To save a snapshot in a separated folder when needed.

    I’m still looking for the code for the Private Sub Report_Activate that will check to see if a snapshoot with that order number exists in the folder. If so, ask the user if he wants to create a second version, if so name it 5657-2

    Thanks
    Joe

  10. #10
    Join Date
    Nov 2004
    Posts
    78
    I prepared a small sample db, where I already added the buttons needed for the snapshots. I’m sorry for asking but I spent 2 hours for this small issue and couldn’t figure out the code. Can anyone please help me?

    When I click on the create button, the function should check if a snapshot file with the current order number exist in the C:\ folder, if yes, ask the user if he wants to create a second version, if yes, create another snapshot with order number plus “,1” etc.

    Thank you
    Joe
    Attached Files Attached Files

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    I'll give you a hint
    Code:
        Dim fileSys, file
        Dim filename As String
        Dim i As Integer
        i = 0
    
        Set fileSys = CreateObject("Scripting.FileSystemObject")
    
        OrderID.SetFocus
        Do
            i = i + 1
            On Error GoTo FileNotFound
            filename = "C:\Snapshots\" & OrderID.Text & "_" & i & ".snp"
            Set file = fileSys.GetFile("C:\Snapshots\" & OrderID.Text & "_" & i & ".snp")
        Loop
    FileNotFound:
        'code for saving the report
    Last edited by m.timoney; 01-09-07 at 05:47.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Nov 2004
    Posts
    78
    I very appreciate your help. Especially as you see that I’m spending days on such a small function.

    I have revised the form and added more functions. However, I was not able to plug-in your code into my function. Can you please open the attached DB and see where I highlighted the line that needs to be update.

    Thank you
    Joe
    Attached Files Attached Files

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could consider doing is adding the customer number and timestamp (in YYYYMMDDHHMMSS format) of the report generation as a suffix to the snashot filename. that should work assuming that you never get two users trying to generate ther same report for the saem customer at the same time.

    However to me it smacks of a design problem.. if your design is right you shouldeb able to view all the history on the invoice on screen and generate a reprot as required. Im surprised that you generate revised invoices, I thought it was industry practise to send any corrections on a new invoice so that both sides have traceability on the invoice and any changes.
    Last edited by healdem; 01-10-07 at 05:30.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    the bit of code that has been commented out (which is what i assume you meant by highlighted) deals with loading the saved snapshot back into access, which isn't what you asked about. and is frankly something i don't think you can do in access.

    the code i provided would have served inplace of that if...elseif...elseif...Endif that you've used to find it
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Nov 2004
    Posts
    78
    I’m 95% done, and need your assistant.
    I’m not good with the search, find, delete, open files.

    Sorry that I revised your code, as I said I didn’t understood it, especially what are you looping, so I tried the best.

    I’m not trying to bring in any snapshot into access; I just want to open it.

    The code below will flag if the file doesn’t exist. However, it still doesn’t open the file. Can you please modify it again? If your code needs to be added back, I will try to study it again.

    Thanks

    Code:
    Private Sub cmdViewSnapshot_Click()
    On Error GoTo Err_cmdViewSnapshot_AfterUpdate
    
    Dim SnapshotID As String
    Dim SnapshotPath As String
    
    Select Case frSnapshots
    Case 1
    SnapshotID = Me.Snapshot1
    Case 2
    SnapshotID = Me.Snapshot2
    Case 3
    SnapshotID = Me.Snapshot3
    Case 4
    SnapshotID = Me.Snapshot4
    Case Else
    MsgBox "Please select a snapshot"
    GoTo Exit_cmdViewSnapshot_AfterUpdate
    End Select
    
    SnapshotPath = "C:\snapshots\" & SnapshotID & ".snp"
    
        Dim fileSys, file
        Set fileSys = CreateObject("Scripting.FileSystemObject")
        Set file = fileSys.GetFile(SnapshotPath)
    
    Exit_cmdViewSnapshot_AfterUpdate:
           Exit Sub
    Err_cmdViewSnapshot_AfterUpdate:
        Select Case Err
            Case 94
            MsgBox "Please select a snapshot"
                Case 53
                MsgBox "I can't find the snapshot in the folder C:\Snapshots", , "I'm sorry.."
            Case Else
            MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume Exit_cmdViewSnapshot_AfterUpdate
         End Select
    
    End
    Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •