Hi folks,
I have an import button on a form that pulls data from a fixed width file into a database. When I click the button, it imports only the first field from the fixed width file into every field of the database instead of importing all the data.
Example, if the first field in the fw file is xoxox and the second field contains 12345, it is importing xoxox into every field in the database instead of xoxox into the first field, and 12345 into the second, etc.
Code copypasta:
Private Sub CmdImport_Click()
Dim fs, f, teststring As String, linecount As Integer, maxcount As Integer, rsResponse As Object
Open "c:\fwfile.txt" For Input As #1 ' Open file.
linecount = 0
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
linecount = linecount + 1 ' count the lines
Loop
Close #1 ' Close file.
maxcount = linecount
linecount = 0
MsgBox maxcount
Open "c:\fwfile.txt" For Input As #1 ' Open file.
Set rsResponse = CurrentDb.OpenRecordset("MY_TABLE")
For linecount = 1 To maxcount + 0 ' Loop until end of file last line.
Line Input #1, TextLine ' Read line into variable.
If linecount > 1 Then 'skip first line
With rsResponse
.AddNew
!Field1 = left(TextLine, 1)
!Field2 = left(TextLine, 12)
!Field3 = left(TextLine, 2)
.Update
.Bookmark = .LastModified
End With
rsResponse.MoveNext
End If
Next linecount
rsResponse.Close
Set rsResponse = Nothing
Close #1 ' Close file.
MsgBox "Import complete!"
End Sub
Any ideas???
I have an import button on a form that pulls data from a fixed width file into a database. When I click the button, it imports only the first field from the fixed width file into every field of the database instead of importing all the data.
Example, if the first field in the fw file is xoxox and the second field contains 12345, it is importing xoxox into every field in the database instead of xoxox into the first field, and 12345 into the second, etc.
Code copypasta:
Private Sub CmdImport_Click()
Dim fs, f, teststring As String, linecount As Integer, maxcount As Integer, rsResponse As Object
Open "c:\fwfile.txt" For Input As #1 ' Open file.
linecount = 0
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
linecount = linecount + 1 ' count the lines
Loop
Close #1 ' Close file.
maxcount = linecount
linecount = 0
MsgBox maxcount
Open "c:\fwfile.txt" For Input As #1 ' Open file.
Set rsResponse = CurrentDb.OpenRecordset("MY_TABLE")
For linecount = 1 To maxcount + 0 ' Loop until end of file last line.
Line Input #1, TextLine ' Read line into variable.
If linecount > 1 Then 'skip first line
With rsResponse
.AddNew
!Field1 = left(TextLine, 1)
!Field2 = left(TextLine, 12)
!Field3 = left(TextLine, 2)
.Update
.Bookmark = .LastModified
End With
rsResponse.MoveNext
End If
Next linecount
rsResponse.Close
Set rsResponse = Nothing
Close #1 ' Close file.
MsgBox "Import complete!"
End Sub
Any ideas???