For Each...Next loop stuck 3

[formerTexan]

Hi,

The following procedure is hihglight a user entered string within the text on a selected control. It is placed on the form's OnCurrent event.

No problems until I programmatically switch the form's recordsource. Then the FOR EACH CTL...NEXT loop gets stuck in the loop.

There are about a dozen controls in the form header, but the code repeatedly cycles through the entire collection.

Question: Doesn't anyone see a reason why ther shouldn't be just one circuit of the loop?

Thank in advance,
Bill

If Len(Me.txtFind.Value) > 0 Then ' if user has entered a search value
strSearch = Me.txtFind.Value ' search for this string
While blnFlag = False
'HERE IS WHERE THE LOOP STICKS
For Each ctl In Me.FormHeader.Controls
strTag = ctl.Tag 'set focus of search on this control
If strTag = "Search" Then
If Len(ctl.Value) > 0 Then
strMatch = ctl.Value
intWhere = InStr(strMatch, strSearch) 'Find string in text and return its position to intWhere.
If intWhere > 0 Then 'returns 0 if no match
' If found.
blnFlag = True
ctl.SetFocus
ctl.SelStart = intWhere - 1 'to adjust for zero based integer
ctl.SelLength = Len(strSearch)
End If
End If
End If
Next ctl
Wend
End If

 

[MoLaker]

No problems until I programmatically switch the form's recordsource. Then the FOR EACH CTL...NEXT loop gets stuck in the loop.

I'd suspect no match is being found in the alternate recordset and therefore intWhere is never > 0. Consequently blnFlag remains false and you are stuck, not in the For...Next loop, but in the While...Wend loop.

 

[VBslammer]

Seems to me you don't even need the While loop. You should just use the For loop until a match is found and exit directly:

    ' if user has entered a search value
    If Len(Me.txtFind.Value) > 0 Then
    
      ' search for this string
      strSearch = Me.txtFind.Value

      For Each ctl In Me.FormHeader.Controls
        If ctl.Tag = "Search" Then
          If Len(ctl.Value) > 0 Then
            intWhere = InStr(ctl.Value, strSearch)
            If intWhere > 0 Then
              'found it; select and exit.
              blnFound = True
              ctl.SetFocus
              ctl.SelStart = intWhere - 1
              ctl.SelLength = Len(strSearch)
              Exit For
            End If
          End If
        End If
      Next ctl

      If Not blnFound Then
        MsgBox "Search failed"
      End If

    End If

VBSlammer

Unemployed in Houston, Texas

 

[willir]

The boys are right...
Thw While / Wend is killing you; not the For/Next loop.

Three things must happen to successfully reach the end of the WhileWend code by resetting blnFound to True....
1) If strTag = "Search" Then...
2) If Len(ctl.Value) > 0 Then...
3) If intWhere > 0 Then...
blnFound = True

What is happening is that a) blnFound remains false, b) While/Wend condition exit condition never met, and c) you repeat the For/Next loop (a zillion times).

VBslammer's approach is perfect, just loop through once, and then test blnFound after completing the loop.

 

[MichaelRed]

on the other hand, the entire process SEEMS a lot convolouted. The tag property needs to be set to a value (possibly different for each control) and then the procedure highlights that specific 'term' in that specific control? It 'looks like' a cross check to say that if anything is in the control, then check that the assigned term is included?

Then, there is the issue / question of controls which are NOT simple text boxes? Perhaps the app is in 'concrete', and the design forbids and / all other control types -but the possability of error seems large.

MichaelRed

 

[formerTexan]

THanks to everyone,

The comments on the WHILE/WEND loop were right to the point. Eliminating it eliminates the loop problems.

Its purpose was to short cut extra searching (once a match was found, then get out). But the difference (with and without it) isn't noticeable anyway, so all is well.

A brief reply to Michael's comment about looping through controls and Tag values. I am using this procedure on several forms to provide a rudimentary search capability for the user. The targetted control for searching varies from form to form and in some cases more than one control is being searched. Setting a Tag value seems to be the neatest approach since then no code changes are needed (I didn't want to hardwire in a control name).

However I will appreciate another reply if you still see potential for simplifying this.

Thanks again
Bill

 

[MichaelRed]

I would pass the control and the string to search for. Usse of the Control (as a Control Object) permits the identification of the (source) form throught the .Parent Property. With some minor fiddling, this could be a more generic procedure.





MichaelRed

 

[formerTexan]

Michael,

Thank you for the followup.

I considered passing a control name as you suggested, but it will introduce an unnecessary step (selecting the field(s) or control(s) to search) for the user. With the user's concurrence in this case, searches will only be on predetermined fields.

Cheers,
Bill

 

[MichaelRed]

User selection wasn't my thought / issue. You could reasonably "can" the arguments in the call for a specific control. The point, for me -at least- is the "Generalization" of the procedure. Placing the Control and the search string as arguments allows the UNCHANGED procedure to be uwed throughout the specific app, and -with little additional effort- to be placed in a "Code" db, and used across the "enterprise", again w/o changes. This is the essence of object orientation. Consider the following:

Public Function basHLTerm(Ctrl As Control, Txt2Find As String)

    Dim Ctl As Control

    For Each Ctl In Ctrl.Parent.FormHeader.Controls

        With Ctl
            If (.Type <> acTexrBox) Then
                GoTo NxtCtrl
            End If

            intWhere = InStr(.Value, Txt2Find)
            If intWhere > 0 Then
              'found it; select and exit.
              blnFound = True
              .SetFocus
              .SelStart = intWhere - 1
              .SelLength = Len(strSearch)
'              Exit For     'Questionable?  What if the Term is found elsewhere?
            End If
NxtCtrl:
        End With
    Next Ctl

      If Not blnFound Then
        MsgBox "Search failed"
      End If

End Function

With ONE additional line of code (ignoring comments), the same functionality is provided throughtout at least the current app w/o re-coding. and does not require access to the specific control to set the tag value proor to use. Of course, SOME mechanisim needs to be introduced to provide the control reference and the search string, but htese need to be provided is any case,







MichaelRed

 

[formerTexan]

Hi Michael,

Your object oriented point is well made. And it follows that a next step for the function will be to pass the form section as a variable and make the function a bit more generic.

And looping through the control collection in the function is now no longer necessary as this is a bridge that gets crossed in the form's module in order to pass the Control argument.

Thank you for the prompting.

Cheers,
Bill