Status on status line

[Guest]

Hello,

I have had huge help on the following code. I would like to see what record
the computer is "working" on as the it makes its way through the tables,
~70,000 records. I saw it once where the status line displayed information
that would tell the user the record it was operating on, thus eleviate fears
that the coputer has locked up. How could this code be modified to show this
on the status line?

Sub FixPrefix()
'this code requires a reference set to the MS DAO object library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNCData, Number " & "FROM
tblDNCRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNCData")
'If Len(strData) = 10 Then
If Len(strData) = 14 Then
strPrefix = Mid(strData, 2, 3)
'strNumber = strData
strNumber = strPrefix & Mid(strData, 7, 3) & Mid(strData,
11, 4)
Else
'strNumber = strPrefix & strData
strNumber = strPrefix & Left(strData, 3) & Mid(strData, 5, 4)
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub

Thanks much!

 

[Marshall Barton]

I have had huge help on the following code. I would like to see what record
the computer is "working" on as the it makes its way through the tables,
~70,000 records. I saw it once where the status line displayed information
that would tell the user the record it was operating on, thus eleviate fears
that the coputer has locked up. How could this code be modified to show this
on the status line?

Sub FixPrefix()
'this code requires a reference set to the MS DAO object library
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strPrefix As String
Dim strNumber As String
Dim strData As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT RecOrder, DNCData, Number " & "FROM
tblDNCRawData ORDER BY RecOrder")
With rs
.MoveFirst
Do Until .EOF
strData = .Fields("DNCData")
'If Len(strData) = 10 Then
If Len(strData) = 14 Then
strPrefix = Mid(strData, 2, 3)
'strNumber = strData
strNumber = strPrefix & Mid(strData, 7, 3) & Mid(strData,
11, 4)
Else
'strNumber = strPrefix & strData
strNumber = strPrefix & Left(strData, 3) & Mid(strData, 5, 4)
End If
.Edit
.Fields("Number") = strNumber
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub

I think a progress bar would be better than displaying a
record sumber the changes so fast you can's read its right
most digits.

Check VBA Help for the SysCmd method. You code should be
somethng like:

. . .
With rs
.MpveLast
SysCmd acSysCmdInitMeter, "Updating Number", .RecordCount
.MoveFirst
. . .
.MoveNext
If .AbsolutePosition Mod 11 = 0 Then
SysCmd acSysCmdInitMeter, .AbsolutePosition
End If
. . .

Now that your question has been answere, I think it would be
way faster if you did all this in one or two Update queries.

. . .
Set db = CurrentDb
db.Execute "UPDATE tblDNCRawData SET [Number] = " _
& "Mid(Number,2,3) & Left(Number,3) & Mid(Number,5,4)) _
& " WHERE Len(DNCData) = 10"

db.Execute "UPDATE tblDNCRawData SET [Number] = " _
& "Mid(Number,2,3) & Mid(Number,7,3) & Mid(Number,11,4) _
& " WHERE Len(DNCData) <> 14"
Set db = Nothing
. . .

I really think the WHERE clauses are inadequate in that you
can not tell if a record was already processed. If you run
that code more than once, the number field will be garbled.

I also think that your len = 10 case is rather odd.

Regardless of what approach you use, be sure to test it on a
copy of the table so you can quickly restore the original
data when you don't get the hoped for results.