Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228

    resart code and add to counter.

    I'm working on a filing system for reports. Basically if the report exists then I want to add a 1 to the end (or 2 if that exists.. and so on).



    I have no problem with the majority of this. But I'm testing how to restart code and increase a counter. Its just basically counting how many times its ran the code.

    here is what I have:

    Code:
    Private Sub Command17_Click()
    Dim answer As Integer
    Dim counter As Integer
    counter = counter + 1
    MsgBox counter
    answer = MsgBox("Are you sure you want to restart the code?", vbYesNo + vbQuestion, "Empty Sheet")
    If answer = vbYes Then
        boolRestart = True
        Command17_Click
    Else
       MsgBox "loop ended"
    End If
    End Sub
    This code will reset the counter every time it's ran. Either I need to restart the code at a later point or find another way to implement the counter.

    any ideas?

    Andy.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you need it to only reset when you tell it, and you could close the database in between resets then you will need to store the value in a table somewhere.

    If it's only for the time the form is open then declare the counter in the forms declaration section before you have any other procedures and it will persist until the form is closed.

    If it's for the duration of the database being opened you could either - store it in a table again, write it to a hidden form control, a tempvar or a publicly declared property or global variable.

    Also is this globally or user specific?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    user specific. It will literally just keep running/looping until it can give a PDF a unique name.

    They are named "project" & "todays date" but its possible to run multiple reports for the same project on the same day. I'm just trying to make sure its not overwritten on save.

    The counter will just be used in an attempt to save the file. Hope this helps in your understanding, Thank you for your suggestion. I will do some reading and let you know. Thanks.

  4. #4
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm assuming it would be project and day specific then.
    Why not include a time stamp in the file name as well (e.g. MyPDf_Project1_20190212_1435.pdf) then you know it won't be overwritten, unless they save it within the same minute?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Sorry for the delayed response. I was away last week. I did think of this and it is an easier solution. I was liking the idea of a counter and thought I'd have a try. Ill stick with the timestamp.

    Cheers anyway.

    I may come back to this if i have the time. If so I'll update.

  6. #6
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    Using one of your suggestions it was very easy. Thanks a lot.

    Code:
    Private Sub Command16_Click()Dim outputFileName As String
    Dim ExportProject As String
    
    
    ExportProject = Me!TSJob
    Me!txtcounter = Me!txtcounter + 1
    
    
    
    
    Dim MyPath As String
    
    
    MyPath = "\server\Exported Project Hours\" & ExportProject & "\"
    If Len(Dir(MyPath, vbDirectory)) = 0 Then
    MkDir MyPath
                End If
    
    
    
    
    
    
    outputFileName = "\server\Exported Project Hours\" & ExportProject & "\" & ExportProject & "-EXPORT-" & Format(Date, "dd-MM-yyyy") & "(" & Me!txtcounter & ")" & ".xls"
    
    
    If Not Dir(outputFileName, vbDirectory) = vbNullString Then
        MsgBox "exists"
        boolRestart = True
        Command16_Click
    Else
        MsgBox "does not exist"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "exportProjectBreakdown", outputFileName, True
    End If
    
    
    
    
    Debug.Print ExportProject
    Debug.Print outputFileName
    
    
    
    
    
    
    
    
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName, True
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName, True
    
    
    End Sub

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

Similar Threads

  1. Replies: 27
    Last Post: 12-02-2015, 06:09 PM
  2. Problem with Multi'user custom counter code
    By sneuberg in forum Programming
    Replies: 1
    Last Post: 05-09-2015, 09:09 AM
  3. Counter
    By Skroof in forum Access
    Replies: 3
    Last Post: 05-14-2012, 08:25 AM
  4. ID Counter Problem
    By slash23 in forum Access
    Replies: 2
    Last Post: 07-06-2011, 11:57 AM
  5. lap Counter
    By challenger in forum Access
    Replies: 4
    Last Post: 07-06-2010, 02: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