Trouble with .AddNew and .Edit

[M]

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

 

[Cheryl Fischer]

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,

 

[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

 

[Marshall Barton]

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)??

 

[M]

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

Thanks for your advice.

M