ca**********@newcastle.gov.uk wrote:
Hi again,
I am mistaken, I am having difficulty running the program 4 times, each
time using a different text file and table without duplicating the
program four times. Your help would again be appreciated. The program
is the same as above shown in Post 3.
Regards.
Private Sub Command2_Click()
Dim intFor As Integer
Dim strTable As String
Dim strFileName As String
For intFor = 1 to 4
'please change the table and text file names in this For loop
Select Case Me.Frame1
Case 1
strTable = "Table1"
strFileName = "C:\Test\Test1.Txt"
Case 2
strTable = "Table2"
strFileName = "C:\Test\Test2.Txt"
Case 3
strTable = "Table3"
strFileName = "C:\Test\Test3.Txt"
Case 4
strTable = "Table4"
strFileName = "C:\Test\Test4.Txt"
End Select
'now process table/create text file
OpenAndExtract strTable, strFileName
Next intFor
End Sub
Private Sub OpenAndExtract(strTable As String, strTextFile As String)
'I am using the code you supplied earlier. You may have made
'modifications I am unaware of.
Dim blnFirstLine As Boolean
Dim dbs As Database
Dim rst As Recordset
Dim strFile As String
Dim strLine1 As String
Dim strLine2 As String
Dim strRawPayee As String, strCleanPayee As String, _
strRawClaimRefNo As String, strCleanClaimRefNo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable,dbOpenDynaset)
Open strTextFile For Input As #1
Do While Not EOF(1) ' Loop until end of file.
If Not blnFirstLine then
'read the first line and ignore the
'if/endif after that
Line Input #1, strLine1
blnFirstLine = True
Endif
Line Input #1, strLine1
Line Input #1, strLine2
'process each line here
strRawPayee = Left(strLine1, 50)
strCleanPayee = Right(strRawPayee, 46)
strRawClaimRefNo = Right(strLine2, 58)
strCleanClaimRefNo = Trim(strRawClaimRefNo)
If Len(strCleanClaimRefNo) = 8 Then
If rst.BOF Then
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
Else
rst.MoveLast
rst.AddNew
rst!Payee = strCleanPayee
rst!ClaimRefNo = strCleanClaimRefNo
End If
rst.Update
End If
Loop
Close #1
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub