Updating table from import

[smehnert]

Help all...Its a fixed width file... I need to match up the CustRef from the text file to the CustRef in my table and update the corresponding fields in the table from the file....here is where I got up to...I am just adding to a test table at the moment...

Function ImportFile(tbFile)
Dim mFileNumber, mFileName As String, mLine As String, I As Integer
Dim DataStart As Boolean

'you can open a recordset to put values into
Dim r As Recordset
Set r = CurrentDb.OpenRecordset("tblTest")
DataStart = False

mFileNumber = FreeFile
mFileName = tbFile

Open mFileName For Input As #mFileNumber Len = 100
I = 0
Do While Not EOF(mFileNumber)

'This starts the reading the info, where the data starts
Do Until Mid(mLine, 1, 7) = "3001629"

I = I + 1
Input #mFileNumber, mLine
Loop

'remove this line after you get your parsing stuff working right
'If MsgBox(mLine, vbOKCancel, "Line " & i) = vbCancel Then Stop
'put your parsing stuff here
r.AddNew
r!f1 = Mid(mLine, 9, 8)
r!f2 = Mid(mLine, 22, 2)
r!f3 = Mid(mLine, 30, 1)
r.Update
I = I + 1
Input #mFileNumber, mLine
' r.MoveNext

'MsgBox Mid(mLine, 8, 6)
Loop
r.AddNew
r!f1 = Mid(mLine, 9, 8)
r!f2 = Mid(mLine, 22, 2)
r!f3 = Mid(mLine, 30, 1)
r.Update

Close #mFileNumber
End Function