By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

On error, delete file to be imported in a folder

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1.   Const TheDirectory = "c:\RT\rfidlogs\"
  2.     Dim TheFile As String
  3.     Dim TheData As String
  4.     Dim iRetValue As Long
  5.     Dim MyDB As DAO.Database
  6.     Dim rst As DAO.Recordset
  7.       TheFile = Dir(TheDirectory & "*.OUT")
  8.     If TheFile <> "" Then
  9.         Open TheDirectory & TheFile For Input As #1
  10.             Line Input #1, TheData
  11.   iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
  12.  If IsNull([TheData]) Then Exit Sub
  13.     Set MyDB = CurrentDb
  14.     Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  15.  
  16.      With rst
  17.             .AddNew        
  18.          ![RaceNumber] = TheData
  19.          ![RaceFinishTime] = Format(Now(), "General Date")
  20.  End With
  21.     rst.close
  22.     Set rst = Nothing
  23.     Set MyDB = Nothing
  24.         Close #1
  25.         Kill TheDirectory & TheFile
  26.             End If
  27.        DoCmd.GoToRecord , "", acNewRec
  28.     Exit Sub
Aug 6 '12 #1

✓ answered by 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.....


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub ImportFiles()
  5. On Error GoTo EH
  6.     Dim strPath As String
  7.     Dim strFileName As String
  8.     Dim strData As String
  9.     Dim I As Integer
  10.     Dim fAdd As Boolean
  11.  
  12.     strPath = "C:\Dirty\"
  13.     strFileName = Dir(strPath & "*.OUT")
  14.     'This loops through all your files
  15.     Do While strFileName <> ""
  16.         fAdd = True
  17.         Open strPath & strFileName For Input As #1
  18.         'We are storing the data as a string first
  19.         Line Input #1, strData
  20.         For I = 1 To Len(strData)
  21.             'We cycle through the data character by character
  22.             If Not (MID(strData, I, 1) >= "0" And MID(strData, I, 1) <= "9") Then
  23.                 'If the character is not between the values for valid integers...
  24.                 Close #1
  25.                 Kill strPath & strFileName
  26.                 I = Len(strData) 'To get out of this loop
  27.                 fAdd = False
  28.             End If
  29.         Next I
  30.         If fAdd Then
  31.             'If there are no non-integer character flags, add to the db
  32.             AddData
  33.         Else
  34.             fAdd = True
  35.         End If
  36.         Close #1
  37.         strFileName = Dir()
  38.     Loop
  39.     Exit Sub
  40. EH:
  41.     MsgBox Err.Number & Err.Description
  42.     Exit Sub
  43. End Sub
  44.  
This sub will actually add the data to your table:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddData(intValue As Integer)
  2. On Error GoTo EH
  3.     Dim MyDB As Database
  4.     Dim rst As Recordset
  5.     Set MyDB = CurrentDb()
  6.     Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  7.     With rst
  8.         .AddNew
  9.         !FieldName = intValue
  10.         .Update
  11.     End With
  12.     rst.Close
  13.     MyDB.Close
  14.     Exit Sub
  15. EH:
  16.     MsgBox Err.Number & Err.Description
  17.     Exit Sub
  18. End Sub
  19.  
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.

Share this Question
Share on Google+
24 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,055
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???
Aug 6 '12 #2

100+
P: 547
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
Attached Files
File Type: zip Clean Logs.zip (1.8 KB, 35 views)
File Type: zip DIRTY.zip (34.0 KB, 39 views)
Aug 6 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,055
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.....


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub ImportFiles()
  5. On Error GoTo EH
  6.     Dim strPath As String
  7.     Dim strFileName As String
  8.     Dim strData As String
  9.     Dim I As Integer
  10.     Dim fAdd As Boolean
  11.  
  12.     strPath = "C:\Dirty\"
  13.     strFileName = Dir(strPath & "*.OUT")
  14.     'This loops through all your files
  15.     Do While strFileName <> ""
  16.         fAdd = True
  17.         Open strPath & strFileName For Input As #1
  18.         'We are storing the data as a string first
  19.         Line Input #1, strData
  20.         For I = 1 To Len(strData)
  21.             'We cycle through the data character by character
  22.             If Not (MID(strData, I, 1) >= "0" And MID(strData, I, 1) <= "9") Then
  23.                 'If the character is not between the values for valid integers...
  24.                 Close #1
  25.                 Kill strPath & strFileName
  26.                 I = Len(strData) 'To get out of this loop
  27.                 fAdd = False
  28.             End If
  29.         Next I
  30.         If fAdd Then
  31.             'If there are no non-integer character flags, add to the db
  32.             AddData
  33.         Else
  34.             fAdd = True
  35.         End If
  36.         Close #1
  37.         strFileName = Dir()
  38.     Loop
  39.     Exit Sub
  40. EH:
  41.     MsgBox Err.Number & Err.Description
  42.     Exit Sub
  43. End Sub
  44.  
This sub will actually add the data to your table:

Expand|Select|Wrap|Line Numbers
  1. Private Sub AddData(intValue As Integer)
  2. On Error GoTo EH
  3.     Dim MyDB As Database
  4.     Dim rst As Recordset
  5.     Set MyDB = CurrentDb()
  6.     Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  7.     With rst
  8.         .AddNew
  9.         !FieldName = intValue
  10.         .Update
  11.     End With
  12.     rst.Close
  13.     MyDB.Close
  14.     Exit Sub
  15. EH:
  16.     MsgBox Err.Number & Err.Description
  17.     Exit Sub
  18. End Sub
  19.  
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.
Aug 6 '12 #4

100+
P: 547
thx twinnyfo i will give it a try
Aug 6 '12 #5

100+
P: 547
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?
Aug 7 '12 #6

twinnyfo
Expert Mod 2.5K+
P: 3,055
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub MoveOUTFile(Path As String, FileName As String, NewPath As String)
  2. On Error GoTo EH
  3.     Dim FSO As FileSystemObject
  4.     Set FSO = New FileSystemObject
  5.     FSO.MoveFile Path & FileName, NewPath & FileName
  6.     Exit Sub
  7. EH:
  8.     MsgBox Err.Number & " " & Err.Description
  9.     Exit Sub
  10. End Sub
  11.  
Then, before you loop back to the next file, call this code like this:

Expand|Select|Wrap|Line Numbers
  1.     MoveOUTFile strPath, strFileName, strNewPath
  2.  
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!
Aug 7 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 9 '12 #8

twinnyfo
Expert Mod 2.5K+
P: 3,055
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.
Aug 13 '12 #9

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. Const TheDirectory = "c:\RT\rfidlogs\"
  2.     Dim TheFile As String
  3.     Dim TheData As String
  4.     Dim iRetValue As Long
  5.     Dim MyDB As DAO.Database
  6.     Dim rst As DAO.Recordset
  7.       TheFile = Dir(TheDirectory & "*.OUT")
  8.     If TheFile <> "" Then
  9.         Open TheDirectory & TheFile For Input As #1
  10.             Line Input #1, TheData
  11.   iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
  12.  
  13.   '(If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then)
  14.  
  15. If IsNull([TheData]) Then Exit Sub
  16.  
  17. If [TheData] Like "*[!0-9]*" Then
  18.  Close #1
  19.       Kill TheDirectory & TheFile
  20.         DoCmd.GoToRecord , "", acNewRec
  21. End If
  22. End If
  23.     Set MyDB = CurrentDb
  24.     Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  25. '(If [TheData] Like Not "*[!0-9]*" Then)
  26.      With rst
  27.             .AddNew
  28.          ![RaceNumber] = TheData
  29.          ![RaceFinishTime] = Format(Now(), "General Date")
  30.          ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
  31.          ![RaceName] = [Forms]![frmRtMain]![RaceName]
  32.          .update
  33.          End With
  34.     rst.close
  35.     Set rst = Nothing
  36.     Set MyDB = Nothing
  37.             Close #1
  38.         Kill TheDirectory & TheFile
  39.         DoCmd.GoToRecord , "", acNewRec
  40.     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.
Aug 13 '12 #10

twinnyfo
Expert Mod 2.5K+
P: 3,055
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.
Aug 13 '12 #11

100+
P: 547
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.
Attached Files
File Type: zip all_data.zip (1.1 KB, 38 views)
File Type: zip clean_data.zip (262 Bytes, 45 views)
File Type: zip dirty_data.zip (264 Bytes, 46 views)
File Type: zip ImportRFIDData.zip (100.1 KB, 47 views)
Aug 14 '12 #12

twinnyfo
Expert Mod 2.5K+
P: 3,055
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.
Aug 14 '12 #13

100+
P: 547
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
Aug 14 '12 #14

100+
P: 547
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.
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Form_Timer_Err
  2.   Call Importdata
  3.  
  4. Form_Timer_Exit:
  5.     Exit Sub
  6.  
  7. Form_Timer_Err:
  8. Close #1
  9.    Kill "C:\RT\rfidlogs\*.out"
  10.     DoCmd.GoToRecord , "", acNewRec
  11.         'MsgBox Error$
  12.      Resume Form_Timer_Exit
  13.  
and the main code
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo Importdata_Err
  2.   'On Error GoTo BillRedEnd
  3.     Const TheDirectory = "c:\RT\rfidlogs\"
  4.     Dim TheFile As String
  5.     Dim TheData As String
  6.     Dim iRetValue As Long
  7.     Dim MyDB As DAO.Database
  8.     Dim rst As DAO.Recordset
  9.       TheFile = Dir(TheDirectory & "*.OUT")
  10.     If TheFile <> "" Then
  11.         Open TheDirectory & TheFile For Input As #1
  12.             Line Input #1, TheData
  13.       If IsNull([TheData]) Then
  14. Exit Sub
  15.  ElseIf [TheData] Like "*[!0-9]*" Then
  16.  Close #1
  17.       Kill TheDirectory & TheFile
  18.       DoCmd.GoToRecord , "", acNewRec
  19.     Else
  20.  'If [TheData] Like Not "*[!0-9]*" Then
  21.  iRetValue = sndPlaySound(CurrentProject.Path & "\bleep4.wav", SND_ASYNC)
  22.       Set MyDB = CurrentDb
  23.    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  24.  
  25.     With rst
  26.            .AddNew
  27.         ![RaceNumber] = TheData
  28.          ![RaceFinishTime] = Format(Now(), "General Date")
  29.          ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
  30.          ![RaceName] = [Forms]![frmRtMain]![RaceName]
  31.         .update
  32.          rst.close
  33.    Set rst = Nothing
  34.    Set MyDB = Nothing
  35.             Close #1
  36.        Kill TheDirectory & TheFile
  37.        DoCmd.GoToRecord , "", acNewRec
  38. If [TheData] = "" Then
  39. Cancel = True
  40. End If
  41. Importdata_Exit:
  42.   Exit Sub
  43. Importdata_Err:
  44.  Close #1
  45. MsgBox Error$
  46.    Exit Sub
  47.        Resume Importdata_Exit
  48. End With
  49. 'End If
  50. End If
  51. 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
Aug 15 '12 #15

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 15 '12 #16

100+
P: 547
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?
Aug 15 '12 #17

zmbd
Expert Mod 5K+
P: 5,287
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
Aug 15 '12 #18

100+
P: 547
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
Attached Files
File Type: zip ImportRFIDData -1608.zip (113.1 KB, 45 views)
Aug 16 '12 #19

twinnyfo
Expert Mod 2.5K+
P: 3,055
neels,

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

Expand|Select|Wrap|Line Numbers
  1. Public Sub Importdata()
  2. On Error GoTo Importdata_Err
  3.   'On Error GoTo BillRedEnd
  4.     Const TheDirectory = "c:\rfidlogs\"
  5.     Dim TheFile As String
  6.     Dim TheData As String
  7.     Dim MyDB As DAO.Database
  8.     Dim rst As DAO.Recordset
  9.       TheFile = Dir(TheDirectory & "*.OUT")
  10.     If TheFile <> "" Then
  11.         Open TheDirectory & TheFile For Input As #1
  12.             Line Input #1, TheData
  13.  
  14.    'If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then
  15.     If IsNull([TheData]) Then
  16.         Exit Sub
  17.     ElseIf [TheData] Like "*[!0-9]*" Then
  18.         Close #1
  19.              Kill TheDirectory & TheFile
  20.              'DoCmd.GoToRecord , "", acNewRec
  21.     Else
  22.         'If [TheData] Like Not "*[!0-9]*" Then
  23.         Set MyDB = CurrentDb
  24.         Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  25.  
  26.         With rst
  27.             .AddNew
  28.             ![RaceNumber] = TheData
  29.             ![RaceFinishTime] = Format(Now(), "General Date")
  30.             ![Racedate] = [Forms]![frmRtMain]![RacingDate]
  31.             ![RaceName] = [Forms]![frmRtMain]![RaceName]
  32.             .update
  33.             rst.Close
  34.             Set rst = Nothing
  35.             Set MyDB = Nothing
  36.  
  37.             Close #1
  38.             Kill TheDirectory & TheFile
  39.         'DoCmd.GoToRecord , "", acNewRec
  40.  
  41.         If IsNull([TheData]) Then
  42.             Exit Sub
  43.         End If
  44.         'If [TheData] = "" Then
  45.         'Cancel = True
  46.         'End If
  47. Importdata_Exit:
  48.     Exit Sub
  49.  
  50. Importdata_Err:
  51.     Close #1
  52.     Kill TheDirectory & TheFile
  53.     'MsgBox Error$
  54. On Error Resume Next
  55.     Exit Sub
  56.     Resume Importdata_Exit
  57.     End With
  58.     End If
  59.     End If
  60. End Sub
  61.  
First, I would always include the statement:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2.  
in every module. It's just a good practice.

Let's discuss these changes:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub Importdata()
  5. On Error GoTo Importdata_Err
  6.     'On Error GoTo BillRedEnd
  7.     Const TheDirectory = "c:\rfidlogs\"
  8.     Dim TheFile As String
  9.     Dim TheData As String
  10.     Dim MyDB As DAO.Database
  11.     Dim rst As DAO.Recordset
  12.     TheFile = Dir(TheDirectory & "*.OUT")
  13.     If TheFile <> "" Then
  14.         Open TheDirectory & TheFile For Input As #1
  15.         Line Input #1, TheData
  16.         Close #1
  17.  
  18.    'If IsNull([TheData]) Or [TheData] Like "*[!0-9]*" Then
  19.     If IsNull(TheData) Then
  20.         Exit Sub
  21.     ElseIf Not TheData Like "*[!0-9]*" Then
  22. '        Close #1
  23.         Kill TheDirectory & TheFile
  24.              'DoCmd.GoToRecord , "", acNewRec
  25.     Else
  26.         'If [TheData] Like Not "*[!0-9]*" Then
  27.         Set MyDB = CurrentDb
  28.         Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  29.  
  30.         With rst
  31.             .AddNew
  32.             ![RaceNumber] = TheData
  33.             ![RaceFinishTime] = Format(Now(), "General Date")
  34.             ![Racedate] = [Forms]![frmRtMain]![RacingDate]
  35.             ![RaceName] = [Forms]![frmRtMain]![RaceName]
  36.             .Update
  37.             .Close
  38.         End With
  39. '        rst.Close
  40. '        Set rst = Nothing
  41.         Set MyDB = Nothing
  42.  
  43. '        Close #1
  44.         Kill TheDirectory & TheFile
  45. '        Exit Sub
  46.     End If
  47.     Exit Sub
  48.         'DoCmd.GoToRecord , "", acNewRec
  49.  
  50. '        If IsNull([TheData]) Then
  51. '            Exit Sub
  52. '        End If
  53.         'If [TheData] = "" Then
  54.         'Cancel = True
  55.         'End If
  56. 'Importdata_Exit:
  57. '    Exit Sub
  58. '
  59. Importdata_Err:
  60. '    Close #1
  61. '    Kill TheDirectory & TheFile
  62.     Exit Sub
  63.     'MsgBox Error$
  64. 'On Error Resume Next
  65. '    Exit Sub
  66. '    Resume Importdata_Exit
  67. '    End With
  68. '    End If
  69. '    End If
  70. End Sub
  71.  
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub Importdata()
  5. On Error GoTo Importdata_Err
  6.     'On Error GoTo BillRedEnd
  7.     Const TheDirectory = "c:\rfidlogs\"
  8.     Dim TheFile As String
  9.     Dim TheData As String
  10.     Dim MyDB As DAO.Database
  11.     Dim rst As DAO.Recordset
  12.     TheFile = Dir(TheDirectory & "*.OUT")
  13.     If TheFile <> "" Then
  14.         Open TheDirectory & TheFile For Input As #1
  15.         Line Input #1, TheData
  16.         Close #1
  17.     End If
  18.     If IsNull(TheData) Then
  19.         Exit Sub
  20.     ElseIf Not TheData Like "*[!0-9]*" Then
  21.         Kill TheDirectory & TheFile
  22.     Else
  23.         'If [TheData] Like Not "*[!0-9]*" Then
  24.         Set MyDB = CurrentDb
  25.         Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  26.         With rst
  27.             .AddNew
  28.             ![RaceNumber] = TheData
  29.             ![RaceFinishTime] = Format(Now(), "General Date")
  30.             ![Racedate] = [Forms]![frmRtMain]![RacingDate]
  31.             ![RaceName] = [Forms]![frmRtMain]![RaceName]
  32.             .Update
  33.             .Close
  34.         End With
  35.         Set MyDB = Nothing
  36.         Kill TheDirectory & TheFile
  37.     End If
  38. Importdata_Err:
  39.     Exit Sub
  40.     MsgBox Err.Number & " " & Err.Description
  41. End Sub
  42.  
I haven't had a chance to test this, but should get you closer with fewer errors.
Aug 16 '12 #20

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub Importdata()
  2. On Error GoTo Importdata_Err
  3.   'On Error GoTo BillRedEnd
  4.     Const TheDirectory = "c:\rt\rfidlogs\"
  5.     Dim TheFile As String
  6.     Dim TheData As String
  7.     Dim MyDB As DAO.Database
  8.     Dim rst As DAO.Recordset
  9.       TheFile = Dir(TheDirectory & "*.OUT")
  10.     If TheFile <> "" Then
  11.         Open TheDirectory & TheFile For Input As #1
  12.             Line Input #1, TheData
  13.     If IsNull([TheData]) Then
  14.     Exit Sub
  15.     ElseIf [TheData] Like "*[!0-9]*" Then
  16.     Close #1
  17.       Kill TheDirectory & TheFile
  18.       DoCmd.GoToRecord , "", acNewRec
  19.     Else
  20.       Set MyDB = CurrentDb
  21.    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  22.  
  23.     With rst
  24.            .AddNew
  25.         ![RaceNumber] = TheData
  26.          ![RaceFinishTime] = Format(Now(), "General Date")
  27.          ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
  28.          ![RaceName] = [Forms]![frmRtMain]![RaceName]
  29.         .update
  30.        rst.close
  31.     End With
  32.    Set rst = Nothing
  33.    Set MyDB = Nothing
  34.    Close #1
  35.    Kill TheDirectory & TheFile
  36.    DoCmd.GoToRecord , "", acNewRec
  37.  Importdata_Err:
  38. Exit Sub
  39.  MsgBox Err.Number & " " & Err.Description
  40.  End If
  41. End If
  42. End Sub
Aug 16 '12 #21

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1. Public Sub Importdata()
  2. On Error GoTo Importdata_Err
  3.   'On Error GoTo BillRedEnd
  4.     Const TheDirectory = "c:\rfidlogs\"
  5.     Dim TheFile As String
  6.     Dim TheData As String
  7.     Dim MyDB As DAO.Database
  8.     Dim rst As DAO.Recordset
  9.       TheFile = Dir(TheDirectory & "*.OUT")
  10.     If TheFile <> "" Then
  11.         Open TheDirectory & TheFile For Input As #1
  12.             Line Input #1, TheData
  13.     If IsNull([TheData]) Then
  14.     Exit Sub
  15.     ElseIf [TheData] Like "*[!0-9]*" Then
  16.     Close #1
  17.       Kill TheDirectory & TheFile
  18.       DoCmd.GoToRecord , "", acNewRec
  19.     Else
  20.       Set MyDB = CurrentDb
  21.    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  22.  
  23.     With rst
  24.            .AddNew
  25.         ![RaceNumber] = TheData
  26.          ![RaceFinishTime] = Format(Now(), "General Date")
  27.          ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
  28.          ![RaceName] = [Forms]![frmRtMain]![RaceName]
  29.         .update
  30.        rst.close
  31.     End With
  32.    Set rst = Nothing
  33.    Set MyDB = Nothing
  34.    Close #1
  35.    Kill TheDirectory & TheFile
  36.    DoCmd.GoToRecord , "", acNewRec
  37.  Importdata_Err:
  38. Exit Sub
  39.  MsgBox Err.Number & " " & Err.Description
  40.  End If
  41. End If
  42. End Sub
Aug 16 '12 #22

100+
P: 547
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
Expand|Select|Wrap|Line Numbers
  1.  Const TheDirectory = "c:\rfidlogs\"
  2.     Dim TheFile As String
  3.     Dim TheData As String
  4.     Dim MyDB As DAO.Database
  5.     Dim rst As DAO.Recordset
  6.       TheFile = Dir(TheDirectory & "*.OUT")
  7.     If TheFile <> "" Then
  8.         Open TheDirectory & TheFile For Input As #1
  9.             Line Input #1, TheData
  10.     If IsNull([TheData]) Then
  11.     Exit Sub
  12. ElseIf [TheData] = "" Then
  13. Close #1
  14.    Kill TheDirectory & TheFile
  15.    DoCmd.GoToRecord , "", acNewRec
  16.  ElseIf [TheData] = "0" Or [TheData] = "00" or [TheData] = "000" Or [TheData] = "0000" then
  17.       Close #1
  18.       Kill TheDirectory & TheFile
  19.       DoCmd.GoToRecord , "", acNewRec
  20.  ElseIf [TheData] Like "*[!0-9]*" Then
  21.     Close #1
  22.       Kill TheDirectory & TheFile
  23.       DoCmd.GoToRecord , "", acNewRec
  24.  Else
  25.       Set MyDB = CurrentDb
  26.    Set rst = MyDB.OpenRecordset("RaceTimingT", dbOpenDynaset, dbAppendOnly)
  27.  
  28.     With rst
  29.            .AddNew
  30.         ![RaceNumber] = TheData
  31.          ![RaceFinishTime] = Format(Now(), "General Date")
  32.          ![RaceDate] = [Forms]![frmRtMain]![RacingDate]
  33.          ![RaceName] = [Forms]![frmRtMain]![RaceName]
  34.         .update
  35.        rst.close
  36.     End With
  37.    Set rst = Nothing
  38.    Set MyDB = Nothing
  39.    Close #1
  40.    Kill TheDirectory & TheFile
  41.    DoCmd.GoToRecord , "", acNewRec
The blank file and with zero's looks like this - below
Attached Files
File Type: zip blank text file.zip (138 Bytes, 38 views)
File Type: zip file with zeros.zip (496 Bytes, 47 views)
Aug 27 '12 #23

twinnyfo
Expert Mod 2.5K+
P: 3,055
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ImportData()
  2.     'Determine the File Name
  3.     If CheckValid(FileName) Then
  4.         'Do the things you do for valid file names
  5.     Else
  6.         'Just Kill the file
  7.     End If
  8. End Sub
  9.  
  10. Private Function CheckValid(FileNmae) as Boolean
  11.     'Go through your steps of validation here
  12. End Function
  13.  
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.....
Aug 30 '12 #24

100+
P: 547
thx for ideas its appreciated
Aug 31 '12 #25

Post your reply

Sign in to post your reply or Sign up for a free account.