On error, delete file to be imported in a folder

[neelsfer]

I import files created by my scanner from the folder "c:\RT\rfidlogs\" folder.
If the data type imported is not in number type format, then i would like to delete files in there using something like

Kill "C:\RT\rfidlogs\*.out"

the code i use currently looks like this and works fine if the data imported is in number format. Please be so kind to assist. I

  Const TheDirectory = "c:\RT\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim iRetValue As Long
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
  iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
 If IsNull([TheData]) Then Exit Sub
    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
     With rst
            .AddNew        
         ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
 End With
    rst.close
    Set rst = Nothing
    Set MyDB = Nothing
        Close #1
        Kill TheDirectory & TheFile
            End If
       DoCmd.GoToRecord , "", acNewRec
    Exit Sub

[twinnyfo]

neelsfer,

I have been playing with this post for quite a while, and I know what I "want" to do with it, but Access VBA is not allowing it. I want to use the CInt() function to convert the data in the file to an integer data type. If the data cannot be converted to an integer, then, we expect an error, to perform some error checking code, which would be to delete the file. However, I can't get my code to trap that error. Access goes straight to error mode, without going into my error handling code.

Still tinkering with this. Perhaps someone else has some ideas???

[neelsfer]

The file i import looks like this. Set the folder correctly in vba. It puts data into a table from this folder. In line 12 of the vba, i assume one can say if it is not askii 0-9, then you delete the file with the "*.OUT" extention.
thx

[twinnyfo]

neelsfer,

I think I may have found a solution for you. I've set up a sub to loop through each file name, and then loop through the data in that file, character by character, to see if there are any "non-integer" characters. If there are, then delete the file. If there are not, then add the data to the DB.

Please let me know if this gets you going down the right track.....

Option Compare Database
Option Explicit
 
Private Sub ImportFiles()
On Error GoTo EH
    Dim strPath As String
    Dim strFileName As String
    Dim strData As String
    Dim I As Integer
    Dim fAdd As Boolean
 
    strPath = "C:\Dirty\"
    strFileName = Dir(strPath & "*.OUT")
    'This loops through all your files
    Do While strFileName <> ""
        fAdd = True
        Open strPath & strFileName For Input As #1
        'We are storing the data as a string first
        Line Input #1, strData
        For I = 1 To Len(strData)
            'We cycle through the data character by character
            If Not (MID(strData, I, 1) >= "0" And MID(strData, I, 1) <= "9") Then
                'If the character is not between the values for valid integers...
                Close #1
                Kill strPath & strFileName
                I = Len(strData) 'To get out of this loop
                fAdd = False
            End If
        Next I
        If fAdd Then
            'If there are no non-integer character flags, add to the db
            AddData
        Else
            fAdd = True
        End If
        Close #1
        strFileName = Dir()
    Loop
    Exit Sub
EH:
    MsgBox Err.Number & Err.Description
    Exit Sub
End Sub
 

This sub will actually add the data to your table:

Private Sub AddData(intValue As Integer)
On Error GoTo EH
    Dim MyDB As Database
    Dim rst As Recordset
    Set MyDB = CurrentDb()
    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
    With rst
        .AddNew
        !FieldName = intValue
        .Update
    End With
    rst.Close
    MyDB.Close
    Exit Sub
EH:
    MsgBox Err.Number & Err.Description
    Exit Sub
End Sub
 

You may also want to add code to move the valid files or just delete them once you are finished. It's up to you and how you want to manage the valid files.

[neelsfer]

thx twinnyfo i will give it a try

[neelsfer]

Thx for the time spend so far. It highly appreciated.
Currently i have a popup form that is opened when the rfid reader starts to transmit data, and the timer function is set to 100 to append the data from the ".out" files, into the RacetimeT table. How would i get your vba to cleanup the ".out" files and get it to append the racenumbers into the table at the same time? I imagine i should call both function one after another?

[twinnyfo]

If your intent is to delete every file that is evaluated, then simply move the Kill statement to right before the Loop statment, so that the file is deleted before the code starts looking for another file.

If your intent is to save all valid files, then you would add code to move the file before looping to the next file in the directory. You would have to create a File System Object in order to do that, similar to this:

Private Sub MoveOUTFile(Path As String, FileName As String, NewPath As String)
On Error GoTo EH
    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
    FSO.MoveFile Path & FileName, NewPath & FileName
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub
 

Then, before you loop back to the next file, call this code like this:

    MoveOUTFile strPath, strFileName, strNewPath
 

strNewPath would be your destination directory that you would have to declare in the previous code and set appropriately.

Please let me know if you need any additional assistance. Hope this helps!

[zmbd]

Although the filesystem object is more flexable..

Now what happens when the admins turn off file scripting...

VBA has some built in things to handle this
(these are from the BASIC core set)
- You can also use a "Name <oldfilename> AS <newfilename>" to rename the file as something you've already looked at within the same directory... you can also move the file using this method however, I prefer the...
- You can also use the "FileCopy <source>, <destination>" methods (then "kill" the original file)
-

tart your VBA editor and create a sub... use the above and do a [F1] help to get the details

Also http://www.applecore99.com/gen/gen061.asp


-z

[twinnyfo]

neels,

Thanks for the PM.... Based on your original question, you said you want to delete ALL FILES in the directory if ANY of the files have non-integer data?

If this is the case, I would create a testing function that cycles through the data to see if it meets your requirements, then another for importing it to a table.

In the testing function, it should return a Boolean value, that as you cycle through every file, if ANY file contains bad data, then set the function returns True, and returns to your form.

Upon returning to your form, you check the value you received from the function. If it is true, you delete all the files. If it is false, you import the files.

I hope this makes sense and assists you in your project.

[neelsfer]

Thx Twinnyfo.
Is it not perhaps possible to delete the .out file at the point where the "incorrect" data arrives at "theData". lines 15/17/25
something like this

Const TheDirectory = "c:\RT\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim iRetValue As Long
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
  iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
 
  '(If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then)
 
If IsNull([TheData]) Then Exit Sub
 
If [TheData] Like "*[!0-9]*" Then
 Close #1
      Kill TheDirectory & TheFile
        DoCmd.GoToRecord , "", acNewRec
End If
End If
    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
'(If [TheData] Like Not "*[!0-9]*" Then)
     With rst
            .AddNew
         ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
         ![RaceName] = [Forms]![frmRtMain]![RaceName]
         .update
         End With
    rst.close
    Set rst = Nothing
    Set MyDB = Nothing
            Close #1
        Kill TheDirectory & TheFile
        DoCmd.GoToRecord , "", acNewRec
    Exit Sub

With the above code i get a datatype error in line 28
This is the last major bug i still have to fix,before my application is 100% functional.

[twinnyfo]

Double check whether RaceNumber is a string or a number. TheData is a string. If you want to save it as a number use one of the convert functions to make TheData into a proper data type for the field.

[neelsfer]

Here is an example of what i am trying to achieve by importing racenumbers continously from a textfile in the rfidlogs folder, using the timer function . The instructions are on the main form.

Create as folder called rfidlogs on the c- drive and add the 3 zip files to it related to data. Extract each one individually when required, to this folder.

Twinnyfo - perhaps this will clarify my problem better as i need to first screen for ".out" files that contain characters other than 0-9 , delete the file and then continue to add ".out" files that only contain 0-9 numbers, to the racenumber field that is number type using the timer function continiously.

In short - clean or dirty ".out" files may be added in succession to the rfidlogs folder by the scanner, and the dirty ones must be deleted instantly as and when its added to the rfidlogs folder, while clean ones are added to the racenumber field in the racetimeT table, and then deleted from this folder only.

[twinnyfo]

neelsfer,

I will probably have to look at this tonight when I have a chance. Please confirm that I understand this flow of processes here:

1. A scanner adds these ".out" files on a regular basis to the \rfidlogs folder

2. You want to check the status of these files, using the timer function on a form.

2.a. If the file is "dirty", the file is deleted automatically.

2.b. If the file is "clean", the data in the file is saved to the RaceTimingT table, recording the RaceNumber (the data from teh scanner), the RaceFinishTime, RaceDate and RaceName

2.c. Still a bit confused here. Once 2.b. is complete above, is the file moved to a new location or deleted?

This will help me direct you once I take a more detailed look at the attachments you have provided.

I think we are getting closer.

[neelsfer]

Thx T.Almost Spot on!
1. The timer function is set to automatically delete "dirty data" or append "clean data" to the racetimingT table when any files arrive in the c:\Rfidlogs, while the scanner is switched on, .
2. 2c - the ".out file" is currently automatically deleted from the rfidlogs folder, once the "clean 0-9 " racenumber (number field) (racefinishtime, racedate and Racename originate from the main form) has been added to the RacetimingT table. It would be nice however to move any files to a folder called c:\rfidfiles, before deletion for reference later.
Thx for your time in looking at this "mini version" of my current problem

[neelsfer]

I spend whole of last night on this, and managed to combine the delete and add functions of data from files.The error trapping is now lacking a bit, as a get an error " input past end of file" and if i press enter it carries on as normal -
this is how i combined it.

On Error GoTo Form_Timer_Err
  Call Importdata
 
Form_Timer_Exit:
    Exit Sub
 
Form_Timer_Err:
Close #1
   Kill "C:\RT\rfidlogs\*.out"
    DoCmd.GoToRecord , "", acNewRec
        'MsgBox Error$
     Resume Form_Timer_Exit
 

and the main code

On Error GoTo Importdata_Err
  'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\RT\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim iRetValue As Long
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
      If IsNull([TheData]) Then
Exit Sub
 ElseIf [TheData] Like "*[!0-9]*" Then
 Close #1
      Kill TheDirectory & TheFile
      DoCmd.GoToRecord , "", acNewRec
    Else
 'If [TheData] Like Not "*[!0-9]*" Then
 iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
      Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
    With rst
           .AddNew
        ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
         ![RaceName] = [Forms]![frmRtMain]![RaceName]
        .update
         rst.close
   Set rst = Nothing
   Set MyDB = Nothing
            Close #1
       Kill TheDirectory & TheFile
       DoCmd.GoToRecord , "", acNewRec
If [TheData] = "" Then
Cancel = True
End If
Importdata_Exit:
  Exit Sub
Importdata_Err:
 Close #1
MsgBox Error$
   Exit Sub
       Resume Importdata_Exit
End With
'End If
End If
End If

Any suggestions on the error trapping? - perhaps it should kill any files left in the c:\rfidlogs folder and then continue as normal

[zmbd]

Which line in which code block is the error occuring?
Remember when reading the files it appears that they have been opened as a read forward, you'll need to move the pointer back to the start of the file if you need to re-read the information.

also in post 15
second code block
line 14 - you are exiting the sub witht the file still open...

Also, after line 14 it appears that you are not reading from the text file at any point after that... is that correct? If so, then use a single close command right after reading the file between lines 12 and 13... remove all of the other close commands from the code.

-z

[neelsfer]

Sorry zmbd i am a bit lost and dumb.
I have a feeling that line 18 and 37 may have caused the problem. Will "addnew" in line 26 not fo the same?
The data arrives from .out file in line 12,and then if nothing arrives (isnull) then it must exit code (line 13+14), and if it contains any characters other than 0-9 (line 15-18), then it must remove the .out file from the rfidlogs folder, and if it contains data characters 0-9 (line 22-37) it will be appended into racetimingT table. In 38-39 is must cancel event if "thedata" is empty.
Any suggestions on how to cleanup this code a bit and add error trapping so that it returns to the start once these functions have taken place?
What should i add to the ontimer event for error trapping, where this function is called from?

[zmbd]

Not dumb... and not lost... we're with you :)
However.... I think we should have turned left at that last intersection!

I was only covering the actual reading if the textfile. Once you are done pulling the information from the file you should go ahead and close the file... best practice.

Refering to the code in the second code block of post #15:

When you read the information from the file in you do so into the "TheData" variable... why is it being encased within braces "[]" in subsequent lines ? This is normally used for table/field/control names and not variables within code.

Line 18 and 37: I am working on the premise that you already have a table/query/form open otherwise you should have an error at this line. Neither of these commands should deal with your text file being open or closed; thus, should have nothing to doe with the error.

Let's see if you can locate the exact line the error occurs by placing the "STOP" command between lines 8 and 9. This way you can use [F8] to step thru the program line by line until it errors. We don't want your error trap at this point in the troubleshooting so that we know exactly when/where the error occurs.

-z

[neelsfer]

Here is the demo version with the current code extracting racenumbers from the ".out files in the rfidlogs folder if you copy it in there. It works but i need to "tidy up the error trapping" please to delete(kill) all ".out" files in the c:\rfidlogs, when an error occurs at anytime and then the popupbox called "frmchipnt" collecting data must not "freeze up" after the error ocurred. I am getting there slowly now. Just to improve my error control to allow the "frmChipNt" form to function non-stop, when errors throw it "off-balance"
get ".out" data from previous posting - all_data.zip file to test
If you get it to right, pls be so kind to post corrected vba to assist me.Thx

[twinnyfo]

neels,

I will post the code from your db, then discuss. Then offer some changes....

Public Sub Importdata()
On Error GoTo Importdata_Err
  'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
 
   'If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then
    If IsNull([TheData]) Then
        Exit Sub
    ElseIf [TheData] Like "*[!0-9]*" Then
        Close #1
             Kill TheDirectory & TheFile
             'DoCmd.GoToRecord , "", acNewRec
    Else
        'If [TheData] Like Not "*[!0-9]*" Then
        Set MyDB = CurrentDb
        Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
        With rst
            .AddNew
            ![RaceNumber] = TheData
            ![RaceFinishTime] = Format(Now(), "General Date")
            ![Racedate] = [Forms]![frmRtMain]![RacingDate]
            ![RaceName] = [Forms]![frmRtMain]![RaceName]
            .update
            rst.Close
            Set rst = Nothing
            Set MyDB = Nothing
 
            Close #1
            Kill TheDirectory & TheFile
        'DoCmd.GoToRecord , "", acNewRec
 
        If IsNull([TheData]) Then
            Exit Sub
        End If
        'If [TheData] = "" Then
        'Cancel = True
        'End If
Importdata_Exit:
    Exit Sub
 
Importdata_Err:
    Close #1
    Kill TheDirectory & TheFile
    'MsgBox Error$
On Error Resume Next
    Exit Sub
    Resume Importdata_Exit
    End With
    End If
    End If
End Sub
 

First, I would always include the statement:

Option Explicit
 

in every module. It's just a good practice.

Let's discuss these changes:

Option Compare Database
Option Explicit
 
Public Sub Importdata()
On Error GoTo Importdata_Err
    'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
        Line Input #1, TheData
        Close #1
 
   'If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then
    If IsNull(TheData) Then
        Exit Sub
    ElseIf Not TheData Like "*[!0-9]*" Then
'        Close #1
        Kill TheDirectory & TheFile
             'DoCmd.GoToRecord , "", acNewRec
    Else
        'If [TheData] Like Not "*[!0-9]*" Then
        Set MyDB = CurrentDb
        Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
        With rst
            .AddNew
            ![RaceNumber] = TheData
            ![RaceFinishTime] = Format(Now(), "General Date")
            ![Racedate] = [Forms]![frmRtMain]![RacingDate]
            ![RaceName] = [Forms]![frmRtMain]![RaceName]
            .Update
            .Close
        End With
'        rst.Close
'        Set rst = Nothing
        Set MyDB = Nothing
 
'        Close #1
        Kill TheDirectory & TheFile
'        Exit Sub
    End If
    Exit Sub
        'DoCmd.GoToRecord , "", acNewRec
 
'        If IsNull([TheData]) Then
'            Exit Sub
'        End If
        'If [TheData] = "" Then
        'Cancel = True
        'End If
'Importdata_Exit:
'    Exit Sub
'
Importdata_Err:
'    Close #1
'    Kill TheDirectory & TheFile
    Exit Sub
    'MsgBox Error$
'On Error Resume Next
'    Exit Sub
'    Resume Importdata_Exit
'    End With
'    End If
'    End If
End Sub
 

First, I aligned your nested lines properly--it's much easier to follow from a troubleshooter's perspective. My default indent is 4 spaces, some use 5, some use 2, you could use 12. It's just a good practice.

First, notice in line 16 that I closed #1, since you already have the data imiported into TheData, there is no reason to keep the file open.

I removed the brackets from around TheData in lines 19 and 21, as it is not required and would probably cause problems.

In line 26, you had the code set to delete the file if id DID meet your criteria!

In lines 30 and following, you had your end with within the error checking lines?????? I added the .Close to the With, and ended the With, since you don't use the rst anymore, just close it, and clear MyDB as well. Then, Kill the file and exit.

You also did not have an end if (except within the error handler???), so that was added in line 46.

You MAY have some problems in your Error handler if #1 was closed before it gets to the EH. Also, if there is a problem with the code(not just an invalid file), you may not want to Kill the file in your EH. You may want to ensure you only Kill the file when you are SURE you want to kill it.

I also noticed you are missing an end if on your first If...Then statement.

If we clean this all up, this is what I have:

Option Compare Database
Option Explicit
 
Public Sub Importdata()
On Error GoTo Importdata_Err
    'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
        Line Input #1, TheData
        Close #1
    End If
    If IsNull(TheData) Then
        Exit Sub
    ElseIf Not TheData Like "*[!0-9]*" Then
        Kill TheDirectory & TheFile
    Else
        'If [TheData] Like Not "*[!0-9]*" Then
        Set MyDB = CurrentDb
        Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
        With rst
            .AddNew
            ![RaceNumber] = TheData
            ![RaceFinishTime] = Format(Now(), "General Date")
            ![Racedate] = [Forms]![frmRtMain]![RacingDate]
            ![RaceName] = [Forms]![frmRtMain]![RaceName]
            .Update
            .Close
        End With
        Set MyDB = Nothing
        Kill TheDirectory & TheFile
    End If
Importdata_Err:
    Exit Sub
    MsgBox Err.Number & " " & Err.Description
End Sub
 

I haven't had a chance to test this, but should get you closer with fewer errors.

[neelsfer]

thx a million twinnyfo - your time is appreciated and i learned a lot
I could not get it to work 100% with your code, but i adapted my error trapping using your vba and it seems to work fine now.
This is the vba that seems to work fine. If you have any other suggestions - pls tell me

Public Sub Importdata()
On Error GoTo Importdata_Err
  'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\rt\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
    If IsNull([TheData]) Then
    Exit Sub
    ElseIf [TheData] Like "*[!0-9]*" Then
    Close #1
      Kill TheDirectory & TheFile
      DoCmd.GoToRecord , "", acNewRec
    Else
      Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
    With rst
           .AddNew
        ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
         ![RaceName] = [Forms]![frmRtMain]![RaceName]
        .update
       rst.close
    End With
   Set rst = Nothing
   Set MyDB = Nothing
   Close #1
   Kill TheDirectory & TheFile
   DoCmd.GoToRecord , "", acNewRec
 Importdata_Err:
Exit Sub
 MsgBox Err.Number & " " & Err.Description
 End If
End If
End Sub

[neelsfer]

thx a million twinnyfo - your time is appreciated and i learned a lot
I could not get it to work 100% with your code, but i adapted my error trapping using your vba and it seems to work fine now.
This is the vba that seems to work fine. If you have any other suggestions - pls tell me

Public Sub Importdata()
On Error GoTo Importdata_Err
  'On Error GoTo BillRedEnd
    Const TheDirectory = "c:\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
    If IsNull([TheData]) Then
    Exit Sub
    ElseIf [TheData] Like "*[!0-9]*" Then
    Close #1
      Kill TheDirectory & TheFile
      DoCmd.GoToRecord , "", acNewRec
    Else
      Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
    With rst
           .AddNew
        ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
         ![RaceName] = [Forms]![frmRtMain]![RaceName]
        .update
       rst.close
    End With
   Set rst = Nothing
   Set MyDB = Nothing
   Close #1
   Kill TheDirectory & TheFile
   DoCmd.GoToRecord , "", acNewRec
 Importdata_Err:
Exit Sub
 MsgBox Err.Number & " " & Err.Description
 End If
End If
End Sub

[neelsfer]

Still on this problem with another twist in the tail.
Using the above code i have a problem when the ".out" file is blank (empty) or when it only contains "0" or "00" or "000" or "0000". It must then be killed in the c:\rfidlogs folder.(lines 12 and 16)
Please assist

 Const TheDirectory = "c:\rfidlogs\"
    Dim TheFile As String
    Dim TheData As String
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
      TheFile = Dir(TheDirectory & "*.OUT")
    If TheFile <> "" Then
        Open TheDirectory & TheFile For Input As #1
            Line Input #1, TheData
    If IsNull([TheData]) Then
    Exit Sub
ElseIf [TheData] = "" Then
Close #1
   Kill TheDirectory & TheFile
   DoCmd.GoToRecord , "", acNewRec
 ElseIf [TheData] = "0" Or [TheData] = "00" or [TheData] = "000" Or [TheData] = "0000" then
      Close #1
      Kill TheDirectory & TheFile
      DoCmd.GoToRecord , "", acNewRec
 ElseIf [TheData] Like "*[!0-9]*" Then
    Close #1
      Kill TheDirectory & TheFile
      DoCmd.GoToRecord , "", acNewRec
 Else
      Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
 
    With rst
           .AddNew
        ![RaceNumber] = TheData
         ![RaceFinishTime] = Format(Now(), "General Date")
         ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
         ![RaceName] = [Forms]![frmRtMain]![RaceName]
        .update
       rst.close
    End With
   Set rst = Nothing
   Set MyDB = Nothing
   Close #1
   Kill TheDirectory & TheFile
   DoCmd.GoToRecord , "", acNewRec

The blank file and with zero's looks like this - below

[twinnyfo]

neels,

One option you may want to incorporate with this code, although it would be more of a larger step, is to establish flag within your code first, to establish 1) is this a file that you can use and then 2) do I save the data and/or 3) do I kill the file. It seems like you kill the file no matter what, so you may not need that flag.

The point here is that your code would look at a file, evaluate it first and simply respond to the rest of your code that there are certain other actions to be done. Then, you create separate functions to perform those specific actions.

For example:

Private Sub ImportData()
    'Determine the File Name
    If CheckValid(FileName) Then
        'Do the things you do for valid file names
    Else
        'Just Kill the file
    End If
End Sub
 
Private Function CheckValid(FileNmae) as Boolean
    'Go through your steps of validation here
End Function
 

This allows you to separate what you are actually doing, and, in the cases with very complex validation functions, aids in troubleshooting and is somewhat cleaner programming.

Just a thought, even though the concept may throw a wrench into what you already have working. Not sure if this helps or just confuses more.....

[neelsfer]

thx for ideas its appreciated