Create counter to memorize integer value

[CuriousGeorge]

Hello,

I need to get a counter working.

I want to add an integer value to my field BananaType where all filenames are stored. (See (*) and bold text).

My problem is that every time i load new files to my database the code is run again (from start) and the variable n is set to 0. So i need to find a way to store the value of n so that it doesnt start all over everytime a new import is started.

See (*) for main code.

I created a public function where i set the variable m as Public. I thought this would do the trick since the variable m is never set to 0 and i thought since it was public that it would never be resetted. See (**)

Any ideas about how to create this 'counter' so i can differentiate my files?

Thanks

(*)

Private Sub Command0_Click()
  If MsgBox("This will open the folder for imports. Continue?", vbYesNoCancel) = vbYes Then
  Shell ("C:\Users\SSS787\exjobb\Database\Readlog_update\readlog.exe C:\Users\SSS787\exjobb\Database\Readlog_update\imports\*.*")
Dim i As Integer
  Dim tblStr As String
    Dim varItem As Variant
        Dim specname As String
                Dim sFile As String
                    Dim n As Integer
                    Dim m As Integer
 
i = 1
tblStr = ""
specname = "Import Specs"
n = 0
      With Application.FileDialog(msoFileDialogFilePicker)
         With .Filters
           .Clear
           .Add "All Files", "*.*"
         End With
 
             .AllowMultiSelect = True
             .InitialFileName = "C:\Users\SSS787\exjobb\Database\Readlog_update\imports"
             .InitialView = msoFileDialogViewDetails
                    If .Show Then
                      For Each varItem In .SelectedItems
 
                        For i = 1 To Len(varItem)
                          If IsNumeric(Mid(CStr(varItem), i, 1)) Then
                            tblStr = tblStr & Mid(CStr(varItem), i, 1)
                          End If
                        Next i
                        If Right(CStr(varItem), 4) = ".txt" Then
 
                          DoCmd.TransferText acImportDelim, specname, "TableData", CStr(varItem), True
                            i = i + 1
                              DoCmd.OpenTable "TableData", acViewNormal, acReadOnly
                                  DoCmd.Close
                                    tblStr = ""
 
                        End If
 
 
 
                            sFile = ParseFileName(CStr(varItem))
 
 
                            [B]n = CounterValue(n)[/B]
[B]                            CurrentDb.Execute "UPDATE TableData SET BananaField = '" & n & "" & sFile & "'  WHERE BananaField Is Null;"[/B]
[B]                            n = n + 1[/B]
 
                    Next varItem
 
 
                      MsgBox "Data Transferred Successfully!"
                    DoCmd.Close
                    End If
                    CounterValue (n)
      End With
  End If
 
End Sub

(**)

Public Function CounterValue(n As Integer) As Integer
    m = n + 1
    CounterValue = m
End Function

 

[DJkarl]

You need to store the value for your counter some where other than code. Every time you close the application whatever is in memory is cleared out, when you start it again it is like running it for the first time. You can store the value for your counter in a table, or a property, or an external file etc...there are numerous possibilities but if your code has the line n = 0 then everytime you run that the counter will be set to zero.