Searching the captions not the source

[eme126]

Hello all,

Does anyone know how I can make this code search the captions instead of the Control Sources:

Option Compare Database

Option Explicit





Dim mstrFormToSearch As String





Private Sub cmdFind_Click()





Static strLastFind As String

Dim ctlFocus As Access.Control

Dim strTemp As String

Dim I As Integer





If IsNull(Me.TxtFindWhat) Then Exit Sub





With Forms(mstrFormToSearch)





.SetFocus





Set ctlFocus = .ActiveControl





On Error Resume Next

strTemp = ctlFocus.ControlSource

If Err.Number <> 0 Then

For I = 0 To .Controls.Count - 1

Set ctlFocus = .Controls(I)

If ctlFocus.Enabled = True Then

Err.Clear

strTemp = ctlFocus.ControlSource

If Err.Number = 0 Then

Exit For

End If

End If

Next I

End If

ctlFocus.SetFocus

On Error GoTo 0





End With





If Me.TxtFindWhat = strLastFind Then

DoCmd.FindNext

Else





DoCmd.FindRecord _

Me.TxtFindWhat.Value, _

acAnywhere, _

False, _

acSearchAll, _

False, _

acAll, _

True





strLastFind = Me.TxtFindWhat





End If





End Sub





Private Sub Form_Open(Cancel As Integer)





On Error Resume Next

mstrFormToSearch = Screen.ActiveForm.Name





If Len(mstrFormToSearch) = 0 Then

MsgBox "There's no active form to search!"

DoCmd.Close acForm, Me.Name, acSaveNo

End If





End Sub


Thanks

 

[eme126]

After I change ControlSource to Caption, it gives me an error here:

DoCmd.FindRecord _
Me.TxtFindWhat.Value, _
acAnywhere, _
False, _
acSearchAll, _
False, _
acAll, _
True

Why?? help

 

[eme126]

This is the code I tried:

Option Compare Database
Option Explicit


Dim mstrFormToSearch As String


Private Sub cmdFind_Click()


Static strLastFind As String
Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer
Dim strSQL As String
Dim oSQL As DAO.QueryDef


If IsNull(Me.TxtFindWhat) Then Exit Sub


With Forms(mstrFormToSearch)


.SetFocus


Set ctlFocus = .ActiveControl


On Error Resume Next
strTemp = ctlFocus.Caption
If Err.Number <> 0 Then
For I = 0 To .Controls.Count - 1
Set ctlFocus = .Controls(I)
If ctlFocus.Enabled = True Then
Err.Clear
strTemp = ctlFocus.Caption
If Err.Number = 0 Then
Exit For
End If
End If
Next I
End If
ctlFocus.SetFocus
On Error GoTo 0


End With


If Me.TxtFindWhat = strLastFind Then
DoCmd.FindNext
Else

strSQL = "SELECT * FROM TableInfo WHERE Caption Like '" & strTarget & "'* "

oSQL.SQL = strSQL

' DoCmd.FindRecord _
' Me.TxtFindWhat.Value, _
' acAnywhere, _
' False, _
' acSearchAll, _
'False, _
'acAll, _
'True


strLastFind = Me.TxtFindWhat


End If


End Sub


Private Sub Form_Open(Cancel As Integer)


On Error Resume Next
mstrFormToSearch = Screen.ActiveForm.Name


If Len(mstrFormToSearch) = 0 Then
MsgBox "There's no active form to search!"
DoCmd.Close acForm, Me.Name, acSaveNo
End If


End Sub
'----- end of code -----