I have the following code:

Sub AppendFromTxt()

Dim lngI As Long
Dim strBuffer As String
Dim intFileNumber As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

intFileNumber = FreeFile

Open "C:\Temp.txt" For Input As #intFileNumber

'***Get rid of the first 5 lines

For lngI = 1 To 5

Line Input #intFileNumber, strBuffer

Next lngI

Do Until EOF(intFileNumber)

Line Input #intFileNumber, strBuffer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTemp")

With rs

Do Until .EOF

.AddNew

If IsNull(.Fields("Field1").Value)
Or .Fields("Field1").Value = "" Then

.Fields("Field1").Value = strBuffer

End If

.MoveNext

Loop

End With

Loop

Close #intFileNumber

End Sub

....that opens the textfile, eliminates the first five
lines as junk and attempts to insert the text into a temp
table. It loops through each line fine and strBuffer
changes with each loop. But it doesn't write to the
Recordset. I'm missing something very simple here. What is
it?!?

Thanks

M

M,

I do not see anywhere that you have used the .Update method with the
..AddNew -- it's required when you want to write a new record or change an
existing record using the .Edit method. Try putting it before the line
containing the .MoveNext method.

Also, you might want to try moving the following two lines:

> Set db = CurrentDb
> Set rs = db.OpenRecordset("tblTemp")

so that they come before the execution of your first loop, for example:

> Set db = CurrentDb
> Set rs = db.OpenRecordset("tblTemp")

> Do Until EOF(intFileNumber)
>
> Line Input #intFileNumber, strBuffer




hth,
--

Cheryl Fischer, MVP Microsoft Access



"M" <(E-Mail Removed)> wrote in message
news:1210301c44257$a556e5a0$(E-Mail Removed)...
> I have the following code:
>
> Sub AppendFromTxt()
>
> Dim lngI As Long
> Dim strBuffer As String
> Dim intFileNumber As Integer
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> intFileNumber = FreeFile
>
> Open "C:\Temp.txt" For Input As #intFileNumber
>
> '***Get rid of the first 5 lines
>
> For lngI = 1 To 5
>
> Line Input #intFileNumber, strBuffer
>
> Next lngI
>
> Do Until EOF(intFileNumber)
>
> Line Input #intFileNumber, strBuffer
>
> Set db = CurrentDb
> Set rs = db.OpenRecordset("tblTemp")
>
> With rs
>
> Do Until .EOF
>
> .AddNew
>
> If IsNull(.Fields("Field1").Value)
> Or .Fields("Field1").Value = "" Then
>
> .Fields("Field1").Value = strBuffer
>
> End If
>
> .MoveNext
>
> Loop
>
> End With
>
> Loop
>
> Close #intFileNumber
>
> End Sub
>
> ...that opens the textfile, eliminates the first five
> lines as junk and attempts to insert the text into a temp
> table. It loops through each line fine and strBuffer
> changes with each loop. But it doesn't write to the
> Recordset. I'm missing something very simple here. What is
> it?!?
>
> Thanks
>
> M

I knew it was something simple. Here's what worked:

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTemp")

Do Until EOF(intFileNumber)

Line Input #intFileNumber, strBuffer

With rs

.AddNew

If IsNull(.Fields("Field1").Value)
Or .Fields("Field1").Value = "" Then

.Fields("Field1").Value = strBuffer

End If

.Update

End With

Loop

Close #intFileNumber

End Sub

....I eliminated the second loop and moved the db/rs
statements as you advised. Thanks for your help!

M



>-----Original Message-----
>M,
>
>I do not see anywhere that you have used the .Update
method with the
>..AddNew -- it's required when you want to write a new
record or change an
>existing record using the .Edit method. Try putting it
before the line
>containing the .MoveNext method.
>
>Also, you might want to try moving the following two
lines:
>
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("tblTemp")
>
>so that they come before the execution of your first
loop, for example:
>
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("tblTemp")
>
>> Do Until EOF(intFileNumber)
>>
>> Line Input #intFileNumber, strBuffer
>
>
>
>
>hth,
>--
>
>Cheryl Fischer, MVP Microsoft Access
>
>
>
>"M" <(E-Mail Removed)> wrote in message
>news:1210301c44257$a556e5a0$(E-Mail Removed)...
>> I have the following code:
>>
>> Sub AppendFromTxt()
>>
>> Dim lngI As Long
>> Dim strBuffer As String
>> Dim intFileNumber As Integer
>> Dim db As DAO.Database
>> Dim rs As DAO.Recordset
>>
>> intFileNumber = FreeFile
>>
>> Open "C:\Temp.txt" For Input As #intFileNumber
>>
>> '***Get rid of the first 5 lines
>>
>> For lngI = 1 To 5
>>
>> Line Input #intFileNumber, strBuffer
>>
>> Next lngI
>>
>> Do Until EOF(intFileNumber)
>>
>> Line Input #intFileNumber, strBuffer
>>
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("tblTemp")
>>
>> With rs
>>
>> Do Until .EOF
>>
>> .AddNew
>>
>> If IsNull(.Fields("Field1").Value)
>> Or .Fields("Field1").Value = "" Then
>>
>> .Fields("Field1").Value = strBuffer
>>
>> End If
>>
>> .MoveNext
>>
>> Loop
>>
>> End With
>>
>> Loop
>>
>> Close #intFileNumber
>>
>> End Sub
>>
>> ...that opens the textfile, eliminates the first five
>> lines as junk and attempts to insert the text into a
temp
>> table. It loops through each line fine and strBuffer
>> changes with each loop. But it doesn't write to the
>> Recordset. I'm missing something very simple here. What
is
>> it?!?
>>
>> Thanks
>>
>> M
>
>
>.
>

M wrote:

>I have the following code:
>
>Sub AppendFromTxt()
>
>Dim lngI As Long
>Dim strBuffer As String
>Dim intFileNumber As Integer
>Dim db As DAO.Database
>Dim rs As DAO.Recordset
>
>intFileNumber = FreeFile
>
>Open "C:\Temp.txt" For Input As #intFileNumber
>
>'***Get rid of the first 5 lines
>
>For lngI = 1 To 5
>
> Line Input #intFileNumber, strBuffer
>
>Next lngI
>
>Do Until EOF(intFileNumber)
>
> Line Input #intFileNumber, strBuffer
>
> Set db = CurrentDb
> Set rs = db.OpenRecordset("tblTemp")
>
> With rs
>
> Do Until .EOF
>
> .AddNew
>
> If IsNull(.Fields("Field1").Value)
>Or .Fields("Field1").Value = "" Then
>
> .Fields("Field1").Value = strBuffer
>
> End If
>
> .MoveNext
>
> Loop
>
> End With
>
>Loop
>
>Close #intFileNumber
>
>End Sub
>
>...that opens the textfile, eliminates the first five
>lines as junk and attempts to insert the text into a temp
>table. It loops through each line fine and strBuffer
>changes with each loop. But it doesn't write to the
>Recordset. I'm missing something very simple here. What is
>it?!?


Actually you've several problems with that code logic. The
one you're asking for is that you have to use the .Update
method to save the new record.

However, if you fix that, then you'r going to get a real
mess because of the loop until eof. The way you have it,
you're trying to add a new record for every record that
you've previously added (which doesn't make sense to me).

I also don't understand why you then check if the new record
has a Null value in a field and of course it does because
nothing has been inserted yet.

This whole approach is going to be rather slow to execute,
so if you don't have a good reason that you haven't yet
explained, why don't you just use TransferText to import the
text file into the table (and then delete the unwanted five
records)??
--
Marsh
MVP [MS Access]

You're totally right --- I normally do these things that
way. Just trying a new approach.

Thanks for your advice.

M


>-----Original Message-----
>M wrote:
>
>>I have the following code:
>>
>>Sub AppendFromTxt()
>>
>>Dim lngI As Long
>>Dim strBuffer As String
>>Dim intFileNumber As Integer
>>Dim db As DAO.Database
>>Dim rs As DAO.Recordset
>>
>>intFileNumber = FreeFile
>>
>>Open "C:\Temp.txt" For Input As #intFileNumber
>>
>>'***Get rid of the first 5 lines
>>
>>For lngI = 1 To 5
>>
>> Line Input #intFileNumber, strBuffer
>>
>>Next lngI
>>
>>Do Until EOF(intFileNumber)
>>
>> Line Input #intFileNumber, strBuffer
>>
>> Set db = CurrentDb
>> Set rs = db.OpenRecordset("tblTemp")
>>
>> With rs
>>
>> Do Until .EOF
>>
>> .AddNew
>>
>> If IsNull(.Fields("Field1").Value)
>>Or .Fields("Field1").Value = "" Then
>>
>> .Fields("Field1").Value = strBuffer
>>
>> End If
>>
>> .MoveNext
>>
>> Loop
>>
>> End With
>>
>>Loop
>>
>>Close #intFileNumber
>>
>>End Sub
>>
>>...that opens the textfile, eliminates the first five
>>lines as junk and attempts to insert the text into a
temp
>>table. It loops through each line fine and strBuffer
>>changes with each loop. But it doesn't write to the
>>Recordset. I'm missing something very simple here. What
is
>>it?!?
>
>
>Actually you've several problems with that code logic.
The
>one you're asking for is that you have to use the .Update
>method to save the new record.
>
>However, if you fix that, then you'r going to get a real
>mess because of the loop until eof. The way you have it,
>you're trying to add a new record for every record that
>you've previously added (which doesn't make sense to me).
>
>I also don't understand why you then check if the new
record
>has a Null value in a field and of course it does because
>nothing has been inserted yet.
>
>This whole approach is going to be rather slow to execute,
>so if you don't have a good reason that you haven't yet
>explained, why don't you just use TransferText to import
the
>text file into the table (and then delete the unwanted
five
>records)??
>--
>Marsh
>MVP [MS Access]
>.
>