Do/Loop Dilemma

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intCount As Integer
Dim strA As String
Dim strB As String
Dim strC As String
Dim intI As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblDescriptions")
rst.MoveLast
rst.MoveFirst
With rst
If rst.RecordCount > 0 Then
.MoveFirst
Do
Debug.Print .RecordCount
Debug.Print descID ' pk of underlying table
strA = Me.txtDescription.Value
Debug.Print strA
For intI = 1 To Len(strA)
strB = Mid(strA, intI, 1)
If strB Like "[A-Z,a-z,0-9, ]" Then
strC = strC & strB
End If
Next intI
Debug.Print strC
.MoveNext
Loop Until .EOF
End If
.Close
Set rst = Nothing
End With
End Sub

Thank you as always.
 
TeeSee said:
Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intCount As Integer
Dim strA As String
Dim strB As String
Dim strC As String
Dim intI As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblDescriptions")
rst.MoveLast
rst.MoveFirst
With rst
If rst.RecordCount > 0 Then
.MoveFirst
Do
Debug.Print .RecordCount
Debug.Print descID ' pk of underlying table
strA = Me.txtDescription.Value
Debug.Print strA
For intI = 1 To Len(strA)
strB = Mid(strA, intI, 1)
If strB Like "[A-Z,a-z,0-9, ]" Then
strC = strC & strB
End If
Next intI
Debug.Print strC
.MoveNext
Loop Until .EOF
End If
.Close
Set rst = Nothing
End With
End Sub
Click to expand...


I don't see any reason for that code not to loop through all the records.
What makes you think it isn't? I notice this line:
Debug.Print descID ' pk of underlying table
Click to expand...

.... which you say should be printing the pk of the table, but I see that it
isn't pulling a from the recordset. If "descID" is the name of a field in
the recordset, the line should be:

Debug.Print !descID ' pk of underlying table

I also see this line:
strA = Me.txtDescription.Value
Click to expand...

Is that line, also, supposed to be getting a value from the recordset?
Because it isn't; "Me.txtDescription" must be a control on your form.

Incidentally, these lines:
rst.MoveLast
rst.MoveFirst
With rst
If rst.RecordCount > 0 Then
.MoveFirst
Click to expand...

Should be replaced by these:

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
 
On Fri, 13 Nov 2009 09:40:29 -0500, "Dirk Goldgar"

And moreover, if descID is not declared anywhere, then you don't have:
Option Explicit
at the top of EVERY module in your application, and also set as
default in Tools > Options. Do it today. Then fix the problems, if
any, after you select Debug > Compile.
Without option explicit such problems will continue to bite you.

-Tom.
Microsoft Access MVP

TeeSee said:
Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim intCount As Integer
Dim strA As String
Dim strB As String
Dim strC As String
Dim intI As Integer
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblDescriptions")
rst.MoveLast
rst.MoveFirst
With rst
If rst.RecordCount > 0 Then
.MoveFirst
Do
Debug.Print .RecordCount
Debug.Print descID ' pk of underlying table
strA = Me.txtDescription.Value
Debug.Print strA
For intI = 1 To Len(strA)
strB = Mid(strA, intI, 1)
If strB Like "[A-Z,a-z,0-9, ]" Then
strC = strC & strB
End If
Next intI
Debug.Print strC
.MoveNext
Loop Until .EOF
End If
.Close
Set rst = Nothing
End With
End Sub
Click to expand...


I don't see any reason for that code not to loop through all the records.
What makes you think it isn't? I notice this line:
Debug.Print descID ' pk of underlying table
Click to expand...

... which you say should be printing the pk of the table, but I see that it
isn't pulling a from the recordset. If "descID" is the name of a field in
the recordset, the line should be:

Debug.Print !descID ' pk of underlying table

I also see this line:
strA = Me.txtDescription.Value
Click to expand...

Is that line, also, supposed to be getting a value from the recordset?
Because it isn't; "Me.txtDescription" must be a control on your form.

Incidentally, these lines:
rst.MoveLast
rst.MoveFirst
With rst
If rst.RecordCount > 0 Then
.MoveFirst
Click to expand...

Should be replaced by these:

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
Click to expand...
 
hi,
Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.

With rst
End With
Click to expand...
Remove the With block and ensure that you have an

Option Compare Database
Option Compare Explicit

as the first two lines in your code module.


mfG
--> stefan <--
 
TeeSee said:
Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.
Click to expand...
Do
Debug.Print .RecordCount
Debug.Print descID ' pk of underlying table
strA = Me.txtDescription.Value
Debug.Print strA
For intI = 1 To Len(strA)
strB = Mid(strA, intI, 1)
If strB Like "[A-Z,a-z,0-9, ]" Then
strC = strC & strB
End If
Next intI
Debug.Print strC
.MoveNext
Loop Until .EOF
Click to expand...
It probably does but strA never changes because it comes from a form so StrC
will always be the same.
You probably want
strA = rst.SomeFieldName
and
rst.someothefield = strC
rst.update

If true making that section a subroutine and running an update query will be
faster.
 
TeeSee said:
Could someone please tell me why this loop doesn't get past the first
record. Even after the .MoveNext the second record is not accessed. I
haven't done anything with the outut (strC) as yet.
Click to expand...

  >            Do>                Debug.Print .RecordCount
               Debug.Print descID          ' pk of underlying table
               strA = Me.txtDescription.Value
               Debug.Print strA
               For intI = 1 To Len(strA)
                   strB = Mid(strA, intI, 1)
                   If strB Like "[A-Z,a-z,0-9, ]" Then
                       strC = strC & strB
                   End If
               Next intI
               Debug.Print strC
               .MoveNext
           Loop Until .EOF
Click to expand...

It probably does but strA never changes because it comes from a form so StrC
will always be the same.
You probably want
strA = rst.SomeFieldName
and
rst.someothefield = strC
rst.update

If true making that section a subroutine and running an update query willbe
faster.
Click to expand...

Thanks to all responses. The problem was that I was referencing the
form fields rather than the recordset fields. I do always have
Option Compare Database
Option Compare Explicit

as the first two lines but compile did not show anything. At any rate
I have learned and the little app works as I would expect.

Mike painter suggested this "If true making that section a subroutine
and running an update query will be faster."

I would never have thought of doing that with a query but would like
to learn. Could you please explain briefly how and where this could be
done?

Thanks to all again.
 
TeeSee said:
Mike painter suggested this "If true making that section a subroutine
and running an update query will be faster."

I would never have thought of doing that with a query but would like
to learn. Could you please explain briefly how and where this could be
done?
Click to expand...
Open a module and create a function from what I left in above.
StrCFunction( SomeValue as Text)
Dim strA as Text, strB as Text
Dim I as Number
strA = SomeValue
Debug.Print strA
For I = 1 To Len(strA)
strB = Mid(strA, I, 1)
If strB Like "[A-Z,a-z,0-9, ]" Then
strC = strC & strB
Next I
End Function

The above is close.

Create an update query with strCFunction(SomeTextField) in the "update to"
section of the field on the query.

The difference may be nominal for small tables but I once saved a big
project with queries.
Looping through the test file took about 20 seconds to open a form. Doing it
with queries took less than a second and it stayed that way for years of
additional data.
The queries used even include a Cartesian set.
 
Back
Top