Setfocus on a subform Question

[Paul7905]

I have a form with one subform. The subform is in datasheet view, nothing unusual about it.

The main form is named "OtherMain"
The subform name is "OtherSubform"
The subform source object is "OtherSubform" (same as it's name on the main form).

On the Main form, I have a control button and in it's event code I have the following statements:

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

When the code hits the .setfocus stamement, I get a run time error 2449 "there is an invalid method in an expression".

Anyone know why using a .setfocus statement on a subform won't work?

Thanks!
Paul

 

[dhookom]

Try

    Me.OtherSubform.SetFocus

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

why post twice Paul

http://tek-tips.com/viewthread.cfm?qid=1581709&page=1

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

 

[Paul7905]

Re: MazeWorx "why post twice Paul"

Two different scenarios with the same issue. The VBA code forum didn't produce a solution. I reconfigured the forms / code and got an error with a singe form with a subform so I concluded the issue may have to do wity FORMS and not necessarily VBA code. Too, who knows how many ghurus may visit the FORMS forum and never visit the VBA code forum (and visa versa). Who could argue with that logic eh? As for myself, If I have a solution for someone and they have posted two separate questions, I would give them a solution if I had one.

 

[Paul7905]

Bottom line, I have concluded that it IS possible to setfocus to a control on a subform but it is not possibe to setfocus to the Form itself within the main forms container. If it were possible, the .setfocus on the subform would not fail and the subform controls collection would be exposed but alas, it's not.

 

[dhookom]

The code both I and MazeWorx have suggested should work to set focus to a control in the subform.

I don't have a clue what you expect to do with the controls collection. It should be fully "exposed" within code.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

The subform name is "OtherSubform"
The subform source object is "OtherSubform" (same as it's name on the main form).

not a good idea. its always wise to use unique names. At some point access wont know which one you want

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

 

[MajP]

You can not set focus to a any form if the form has any controls. You can if there are no controls. That is why the forms'on got focus and on lost focus are pretty meaningless events.

So that is why it fails. However, as pointed out by Duane you can set the focus to the subform control and in turn it sets focus to the first control in the subform.

And as Duane also points out this makes no sense
"subform controls collection would be exposed but alas, it's not."

The subforms control collection is exposed at all times even if the subform is not open, you just have to reference it correctly.

 

[Paul7905]

I certainly appreciate everyones assistance.

Basically what I do have working is a small popup form on which a user can type a search argument, click a command button and execute an event which uses Findrecord to search all the data on another form (all data fields, not just the field having focus). This code starts off setting focus on the sthrough all the controls setting focus on each in turn.

What I am trying to do is get this Findrecord logic to search a subform and it will not. It will search a main form beautifully.

I have tried to use Findrecord in a subform by itself and cannot get it to search either. Maybe findrecord just won't work with subforms?

SOG is an option group the user can use to select search direction.

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
' txtfindwhat is a text box on the search form
If IsNull(Me.txtfindwhat) Then
Exit Sub
End If

'mstrFormToSearch is set in load event and is the name of the form the user wants to search on

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
'MsgBox " I = " & I & " " & strTemp

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

 

[MajP]

If I understand correctly then here is your problem

With Forms(mstrFormToSearch)

If mstrFormToSearch is the name of a subform you can not reference a subform in this manner. A subform is not a member of the forms collection only all open "main" forms.

To fix this instead of setting the name of the form set the actual form.

so in you load event of the form

Public frmFormToSearch as access.form

private sub form_load()
...
set frmFormToSearch = me.yoursubformcontrolName.form
end sub

then in the above code change to
With Forms(mstrFormToSearch)
to simply
with frmFormToSearch


you can never reference a subform by its name only by
me.subformcontrolname.form
or external to the form
forms("yourMainformName").subformcontrolname.form

Here is the reason why. Assume you have subform called subformone. This could be a subform in many open forms at one time. If you referenced it by name access would not know which instance you were referring to.

 

[Paul7905]

OK, I tried some recommendations and it fails on the .Setfocus statement following the With statement.

If the subform is opened BY ITSELF and I set mstrFormToSearch to the name of the subform it works.

I have tried all sorts of ways to set mstrFormToSearch to the subform on it's main form and the search fails every time.

Example :
Set mstrFormToSearch = Forms![OtherStuffLibrarySelect]![OtherSubform1].Form


Does anyone have an example of using FindRecord against a Subform recordset? I'm beginning to think it is not possible.

I'm using access 2000. The main form is OtherStuffLibraryselect, it has one subform named OtherSubform1 displaying the data in datasheet view.

The search logic below is contained in another small popup form. Again, the code below works great searching a main forms data but fails if trying to search data on a subform on a main form.


Option Compare Database
Option Explicit
Private strLastFind As String
'Private mstrFormToSearch As String
Public mstrFormToSearch As Access.Form

Private Sub Form_Load(Cancel As Integer)

Set mstrFormToSearch = Forms("OtherStuffLibrarySelect").OtherSubform1.Form

txtfindwhat.SetFocus

End Sub

Private Sub Find_Click()



Dim ctlFocus As Access.Control
Dim strTemp As String
Dim I As Integer

If IsNull(Me.txtfindwhat) Then
' MsgBox "txtfindwhat = null, exiting sub"
Exit Sub
Else
' MsgBox "txtfindwhat is not null, it is = " & txtfindwhat
End If

'With Forms(mstrFormToSearch) 'This works if the subform is opened by itself

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


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
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
End If
End Sub

 

[Paul7905]

Well I played around and decided to try to put the search logic on the Main form and got the search to work. I just cannot search a subform using a separate form.

I did not have much space on the main form but I was able to put the search argument text box on it along with the option group and a search command button. The following code repetitively searches all the data fields on the suibform each time the button is clicked and locates the search argument no matter where it resides in any of the data fields.

Thanks for everyone assistance, it must not be possible to do what I was attempting if the form being searched is a subform.


this works

Private Sub search_Click()
On Error GoTo Err_search_Click


Me.OtherSubform1.SetFocus


If Me.txtfindwhat = strLastFind And sogsave = SOG.Value Then
DoCmd.FindNext
Else
If SOG.Value = 1 Then
DoCmd.FindRecord _
Me.txtfindwhat.Value, _
acAnywhere, _
False, _
acSearchAll, _
False, _
acAll, _
True
strLastFind = Me.txtfindwhat
ElseIf SOG.Value = 2 Then
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
End If

Exit_search_Click:

sogsave = SOG.Value
Exit Sub

Err_search_Click:
MsgBox Err.Description
Resume Exit_search_Click

End Sub

 

[MajP]

Did you read my post?

it must not be possible to do what I was attempting if the form being searched is a subform.

Wrong! Of course it is possible. The problem as I stated is that your are referencing an subform instance by its form name.
This will not work to reference a form instantiated as a subform:
With Forms(mstrFormToSearch)

If the subform is opened BY ITSELF and I set mstrFormToSearch to the name of the subform it works.

Of course it does. I explained that if you open the subform by itself, it is just a regular form so it will be added to the forms collection.
And thus
With Forms(mstrFormToSearch)
will work.

One more time, if it is a subform inside a mainform then you can not refer to the subform BY ITS NAME. And thus
With Forms(mstrFormToSearch)
FAILS.
Because the subform is not part of the Forms collection.

So you can simply do what I suggested and save the form object as a global variable and not the form name.

 

[Paul7905]

Dear MAJP,

Thanks for trying to educate me. I tried to do exactly what you indicated would work. There are three forms involved, a little popup search form which contains all the search logic and the text field the user types the argument into and a seperate main form with a subform.

In my little search form I declared :

Public frmToSearch as access.form

In the load event I put :

Set frmToSearch = Forms!OtherStuffLibrarySelect!OtherSubform1.Form

In the Find event on the little search form, I put:

With frmToSearth

.setfocus

Findrecord logic here

End With

I tried taking out the .setfocus with just the findrecord statement and that failed too.

I tried many different variations of the set in the load event including setting the variable to the container name for the subform on the main form and nothing would work.
The code fails on the setfocus event.

I could not use the statement "set frmFormToSearch = me.yoursubformcontrolName.form" statement as you recommended because the search form containing the set statement is a separate form/subform and "me." will not work.

In any event, I have figured out how to put the search on the main form. Alas I did not want to do this because of space considerations, the main form is too crowded. I had hoped to get the little popup form to work. Actually, I'd like to see some code that did work and challenge you to come up with simple findrecord code contained in a popup form that will search a subform on another open form.

Thanks for all your help.

Paul

 

[MajP]

First, I would never write code like this. The concept of search mutliple fields for the same type of data does not make a whole lot of sense to me. If I had to I would never use form operations. It is just way to temperamental with the requirement to set focus. I would use the forms recordset and the recordset fields collection. But to show you this can be done in this manner.

1) You have to be able to determine if the form you refer to is instantiated as a subform. So you need this function

Public Function issubform(frm As Access.Form) As Boolean
  On Error GoTo errLabel
  Dim errParent As Access.Form
  Set errParent = frm.Parent
  issubform = True
  Exit Function
errLabel:
  Exit Function
End Function

2) If it is a subform then you need to return the subform control. So you need this

Public Function getSubFormControl(frm As Access.Form) As SubForm
  Dim mainFrm As Access.Form
  Dim ctl As Access.Control
  Set mainFrm = frm.Parent
  For Each ctl In mainFrm.Controls
    If ctl.ControlType = acSubform Then
      If ctl.Form.Name = frm.Name Then
        Set getSubFormControl = ctl
      End If
    End If
  Next ctl
End Function

3) If the form has focus but no control has focus then the findrecord command fails with error 2162. I think you can only get into this case be forcibly clicking on the record selector. But here is the check. I assume this was what you were trying to do.

Public Function checkCtlFocus(frm As Access.Form) As Boolean
    On Error Resume Next
    Dim ctl As Access.Control
    For Each ctl In frm.Controls
      If ctl.ControlType = acListBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
        ctl.SetFocus
        checkCtlFocus = True
        Exit Function
      End If
    Next ctl
End Function

4) The original code was kind of a mess with trapping different errors. Not really sure what the intent was. Here is my best guess.

Public Sub searchForm(frm As Access.Form, findWhat As Variant, srchType As Integer)
   
   Dim direction As Long
   Dim blnFindFirst
   Dim subFrmCtl As Access.SubForm
   
   On Error GoTo errLabel
   
   If Trim(findWhat & " ") = 0 Then
      Exit Sub
   End If
   
   Select Case srchType
     Case 1
       direction = acSearchAll
       blnFindFirst = True
     Case 3
       direction = acUp
       blnFindFirst = False
     Case 4
       direction = acDown
       blnFindFirst = False
     Case Else
        Exit Sub
     End Select
   
     If issubform(frm) Then
        frm.Parent.SetFocus
        getSubFormControl(frm).SetFocus
     Else
       frm.SetFocus
     End If
    
resumeSub:
   If strLastFind = findWhat And direction = acSearchAll Then
     If issubform(frm) Then
       direction = acDown
       blnFindFirst = False
       DoCmd.FindRecord findWhat, acAnywhere, False, direction, False, acAll, blnFindFirst
     Else
       DoCmd.FindNext
     End If
   Else
     DoCmd.FindRecord findWhat, acAnywhere, False, direction, False, acAll, blnFindFirst
   End If
   strLastFind = findWhat
   mSrchType = srchType
   
   Exit Sub
errLabel:
    'This is the case where you force the focus manually to a form from the control
    'You then have to force it back.  This is kind of sloppy, but if you force the focuse to a control
    'every time then it messes up the find next for the case 3 and 4
    If Err.Number = 2162 Then
       If Not checkCtlFocus(frm) Then
         Exit Sub
       Else
         Resume resumeSub
       End If
    Else
      MsgBox Err.Number & "  " & Err.Description
    End If
End Sub

This works fine for any form and form instantiated as a subform.

Example call:

Private strLastFind As String
Public mFormToSearch As Access.Form
Private mSrchType As Integer

Private Sub cmdSearch_Click()
  searchForm mFormToSearch, Me.txtFindWhat, Me.optSrchType
End Sub

Private Sub Form_Load()
  Set mFormToSearch = Forms("frmLinkedSubforms").subFormCtlOrders.Form
End Sub

 

[Paul7905]

MAJP, thankyou so much, I am going to try your example tomorrow when I get to the office.

Re:First, I would never write code like this. The concept of search mutliple fields for the same type of data does not make a whole lot of sense to me.

The requirement is to be able to search an inventory file. The file consists of Nursery product with Botanical and Common names. We have a workforce that has not memorized all the Botanical names for a particular plant and or the same for the Common name. They may know snippets of the Common or Botanical name and thus the search is extremely handy. For example, they may know they are looking for a Palm tree but cannot recall the botanical name for the particular Palm they are looking for until they see it. Thus they can type in Palm and search on the file and get a hit on Queen Palm with the Botanical name "Arecastrum romanzoffianum" Or, on the other hand they may be looking for a Botanical Name of "Agave palmeri" and all they recollect is that the name has "palm" in it with a common name of "PALMER'S AGAVE".

In any event, this sort of search on multible fields work wonders and saves time (they don't always have to run and find a botanical/common name ghuru and can use the little knowlege thay might have to find what they are looking for).

The basic searches already in place are on the beginning of the Botanical and Common name fields but often what they remember is the second word in the name etc so the snippet search of all fields give them a search tool that is more comprehensive.

As far as the code itself, I am self taught, using what I have learned from some manuals and what assistance I can obtain from professionals such as yourself here on the message boards. My background was mainframs stuff with EDS and Eddie Bauer with ALC, Cobol, CICS etc. and so the PC arena is new to me. I really appreciate your help! Thanks

Paul

 

[MajP]

That supports my point. You have specific fields that you want to search. Common name and botanical. No one want to randomly search all fields. So make a search form that is focused to the users needs. I understand the need to make it easy to find a record, I just see this as a slopy approach.

If it was me, I would pop open a search form with two comboboxes. One for common name and one for botanical name. As I type in the combos it would narrow the records to the ones matching the critieria.
Ex.
Type P in the common name box: returns all records with an "P".
Then type A: Returns all records with "PA"
Then type L: returns all records with "PAl"
....

http://www.4shared.com/file/167004490/99ff0798/SearchInventory.html

Once you find what you want select it and go to that record.

 

[Paul7905]

Great idea, I certainly don't want to be "sloppy". I'll give that a try and see how the users like it. I guess I can get it to search both fields concurrently (because they won't know necessarily which of the two fields what they are searching for exists in.

 

[MajP]

http://www.4shared.com/file/167475098/120de15/Demo2fieldFAYTlistBox_and_Sear.html

This is a 2 column find as you type listbox. Imagine that column one is common name, column 2 the botanical name.

It returns, as you type matches in either field and automatically goes to that record.

 

[MajP]

wrong link

http://www.4shared.com/file/167502824/1da2d41/SearchInventory2.html