Recordset 'finishing' problem

  • Thread starter Thread starter NH
  • Start date Start date
N

NH

I am using code to clean up a table.
The table is imported from a text file where some rows have been 'wrapped'
so you get data like:

FIELD1 FIELD2
123 James Bond -
0 Live And Let Die

where I want:

FIELD1 FIELD2
123 James Bond - Live And Let Die

The following code does exactly what I want, except I am not sure fow to
'finish' it. When the task is completed, I.e. there are no rows left to
clean, Access returns a 'No Current Record' error.

Do While Not rs.EOF
rs.FindFirst ("FIELD1 = '0'")
StrDATA = rs!FIELD2.Value
rs.Delete
rs.MovePrevious
rs.Edit
rs!FIELD2.Value = rs!FIELD2.Value & " " & StrDATA
rs.Update
Loop

How do I get the code to drop out properly when it has finished the task?

Thanks

Nick
 
I take that back -

It doesn't do what it is supposed to at all.

Can anyone tell me where I am going wrong?

What I want to do is:

go through the recordset, for each row with FIELD1 = '0'
'copy' the value in FIELD2
'paste' it on the end of FIELD2 in the previous record
remove the obsolete row

Thank you..

Nick
 
Try this: -
Dim TempStr As String
[Declare Your RecordSet Here]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then
TempStr= rst!Field2
rst.MovePrevious
TempStr= (rst!Field2 & " " & TempStr)
rst.Edit
rst!Field2= TempStr
rst.MoveNext
End If
rst.MoveNext
WEND
[THIS BIT OF CODE REMOVES ALL OF THE 0 RECORDS]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then rst.Delete
rst.MoveNext
WEND

Note.

This code is based on "FIELD1" being a Number Field. In
case anything goes wrong, create a copy of the Table
before attempting the Programme.

HTH

Tony C.
 
Don't forget to make sure that if the first record has Field1=0 it does not
try to go to the previouse or you will get an error.

Tony C said:
Try this: -
Dim TempStr As String
[Declare Your RecordSet Here]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then
TempStr= rst!Field2
rst.MovePrevious
TempStr= (rst!Field2 & " " & TempStr)
rst.Edit
rst!Field2= TempStr
rst.MoveNext
End If
rst.MoveNext
WEND
[THIS BIT OF CODE REMOVES ALL OF THE 0 RECORDS]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then rst.Delete
rst.MoveNext
WEND

Note.

This code is based on "FIELD1" being a Number Field. In
case anything goes wrong, create a copy of the Table
before attempting the Programme.

HTH

Tony C.
-----Original Message-----
I take that back -

It doesn't do what it is supposed to at all.

Can anyone tell me where I am going wrong?

What I want to do is:

go through the recordset, for each row with FIELD1 = '0'
'copy' the value in FIELD2
'paste' it on the end of FIELD2 in the previous record
remove the obsolete row

Thank you..

Nick


"NH" <nh> wrote in message news:[email protected]...


.
Click to expand...
Click to expand...
 
That has done the trick.

Thank you.


Tony C said:
Try this: -
Dim TempStr As String
[Declare Your RecordSet Here]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then
TempStr= rst!Field2
rst.MovePrevious
TempStr= (rst!Field2 & " " & TempStr)
rst.Edit
rst!Field2= TempStr
rst.MoveNext
End If
rst.MoveNext
WEND
[THIS BIT OF CODE REMOVES ALL OF THE 0 RECORDS]
rst.MoveFirst
While Not rst.EOF
If rst!Field1 = 0 Then rst.Delete
rst.MoveNext
WEND

Note.

This code is based on "FIELD1" being a Number Field. In
case anything goes wrong, create a copy of the Table
before attempting the Programme.

HTH

Tony C.
-----Original Message-----
I take that back -

It doesn't do what it is supposed to at all.

Can anyone tell me where I am going wrong?

What I want to do is:

go through the recordset, for each row with FIELD1 = '0'
'copy' the value in FIELD2
'paste' it on the end of FIELD2 in the previous record
remove the obsolete row

Thank you..

Nick


"NH" <nh> wrote in message news:[email protected]...


.
Click to expand...
Click to expand...
 
While this is not a solution to your problem (others have handled that), I
would like to suggest that it would be better to store "James Bond" in one
field and "Live and Let Die" in another of the same record.

By the rules of normalization (first normal form) each field in a table
should be atomic, that is it should hold ONLY one piece of information. You
are holding two. I am assuming that other movies (other than James Bond)
will have some grouping criteria, followed by a dash and then the name of
the movie.

The problem is, what if you ever want to get just part of the field, you
will have to do string manipulation to get it. This can be a real problem at
times, and it is entirely unnecessary if they are stored separately. You
can always concatenate them back together anytime you want in a query, form
or report.

If you are NEVER going to want to see part of this field, it's OK as it is,
but I dan't believe this is the case.
 
Back
Top