Using "FindRecord" to Search a Sub Form

[Paul7905]

I have a form with code that I use to find records containing a search argument typed by the user.

The code resides in a small separate form used to type the search argument in and it works well. It will find any data in any control on a form that matches what the user typed. The form being searched can be in "form" or "datasheet" view, makes no difference.

Now I want to use this search form to search a subform on another form and cannot get it to work. If I open the subform by itself and set the "WITH" in the code to the subform name it will search the subform, but when the subform is opened on the main form it will not (I get errors and I am pretty sure it has something to do with exposing the subform and it's controls to the code.)

I'm hoping someone can help in setting up the reference to the subform when it is open on the main form so this code in the search form will work.

I have tried stuff like :

With Forms!OtherMain.OtherSubform.Form
With Forms("OtherMain).Controls("OtherSubform").Form
etc. etc.

Sometimes I get and error in the .Setfocus statement, Other times I get an error in the FindRecord statement. In either case I am sure the code just does not have access to the subform when it is open on the main form and need some guidance as to why it doesn't.

Here is the code.

Option Compare Database
Option Explicit
Private strLastFind As String
Private mstrFormToSearch As String

Private Sub Find_Click()
'Static strLastFind As String
Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer

Private Sub Find_Click()


'Static strLastFind As String
Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer

If IsNull(Me.txtfindwhat) Then 'txtfindwhat ' text box on form that users type search argument in
Exit Sub
End If

'with (mstrFormToSearch) 'normally a form ID set at form open

With Forms!OtherSubform 'OtherSubform is a form displaying data in datasheet view

.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)


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
If SOG.Value = 1 Then 'SOG is a option box that the user selects search options(fwd/back) etc.
DoCmd.FindRecord _
Me.txtfindwhat.Value, _
acAnywhere, _
False, _
acSearchAll, _
False, _
acAll, _
True
strLastFind = Me.txtfindwhat
ElseIf SOG.Value = 3 Then
DoCmd.FindRecord _
Me.txtfindwhat.Value, _
acAnywhere, _
False, _
acUp, _
False, _
acAll, _
False
strLastFind = Me.txtfindwhat
ElseIf SOG.Value = 4 Then
DoCmd.FindRecord _
Me.txtfindwhat.Value, _
acAnywhere, _
False, _
acDown, _
False, _
acAll, _
False
strLastFind = Me.txtfindwhat
End If
'MsgBox "just set strLastFind = txtfindwhat"
End If
End Sub

 

[MazeWorX]

check out

http://tek-tips.com/viewthread.cfm?qid=1562961

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Paul7905]

Thanks MazeWorX,

I looked over the thread you referred me to and came up with someting to put in the "With" statement but I still get the error on the .Setfocus command.

This is what I used:

With Forms![OtherMain]![OtherSubform].Form
.Setfocus


The .Setfocus command results in the following error:

Runtime Error 2449
There is an invalid method in an expression.


The code is in a form that is nothing more than a text box in which the user can type a search argument. It has a command button that triggers the code to search another forms subform. It works great if the subform is opened by itself but not when the subform is opened on it's main form.

I can't figure out why the .setfocus won't work when it's open as a subform and it will when it's open by itself.

Any other ideas?

 

[MazeWorX]

Me.OtherSubform.SetFocus << Set focus to subform
Me.OtherSubform!txtTextBoxOnSubform.SetFocus << set focus to a control on the subform

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work