Problem with Event Procedure (A2K-SR1)

Topic

New Reply

In a previous thread “Odd Form Behaviour” I was discussing with Charlotte a way to have the cursor jump consistently from a main form to the first field in a subform. She suggested the following code for the OnCurrent event of the subform:

Me.Section(acDetail).Controls(0).SetFocus

On one the forms I used this on, it worked perfectly.

On the other form in the database where I need the same thing to happen, when I put this code in the OnCurrent event of its subform, I get the following error:

Runtime error 2467
The expression you entered refers to an object that is closed or doesn’t exist.

Can anyone suggest where I should look to troubleshoot. Given that the two forms are reasonably similar in design, I am a little confused, not to say frustrated, why the same genereic code will work on one and not the other.

Peter

Reply | Quote

Replies

Check your subform. Is the first control (whatever Controls(0) might be) able to accept the focus? Is it visible, for instance? You may have to resort to using the control name or else create a function that will return the first control on a form that can accept the focus. Here’s a routine I wrote some time ago to do the latter:

Public Function FirstFormControl(frm As Form, _
                                 Optional frmSection As Index = -1) As String
  'created by Charlotte Foust 9/14/99
  'last modified 9/11/2000
  Dim ctl As Control
  Dim intTab As Integer
  Dim strCtlName As String
  Dim blnSkip As Boolean
  
  'Use the count of controls as the
  'maximum tab value
  intTab = frm.Count
  
  'Loop throught all controls on form
  For Each ctl In frm
    If frmSection  -1 Then
      If ctl.Section  frmSection Then
        blnSkip = True
      End If 'ctl.Section  frmSection
    End If 'frmSection  -1
    
    If Not blnSkip Then
      Select Case ctl.ControlType
        'If control can accept value
        'test its tab value against intTab.
        Case acTextBox, acOptionGroup, acComboBox, acListBox, _
            acCheckBox, acOptionButton, acCommandButton, _
            acToggleButton
          'If tab value less than intTab
          'set intTab = tab value and
          'set strCtlName = control name.
          If ctl.TabIndex < intTab Then
            If ctl.TabStop = True Then
              intTab = ctl.TabIndex
              strCtlName = ctl.Name
            End If 'ctl.TabStop = True
          End If 'ctl.TabIndex < intTab
      End Select 'Case ctl.ControlType
    End If 'not blnSkip
  Next 'ctl In frm
  
  'Return name of control with lowest tab value
  FirstFormControl = strCtlName
End Function 'FirstFormControl(frm As Form, _
                                 Optional frmSection As Index = -1)

Reply | Quote

There are no hidden or invisible controls and the first control can accept the focus. When I have tried to explicitly name the form rather than use Me, then I get an error message that the form can’t be found (error 2450). It seems that even though the form exists, and can be opened, VBA can’t find it.

I have tried deleting and rebuilding the subform from scratch and still can’t get it to work. I’ve even gone as far as using this as the OnCurrent event (the field I want, BTW, is IndividualID):

Dim ctl As Control
Set ctl = Forms![Family Members]![IndividualID]
DoCmd.GoToControl ctl.Name

and get the same problem that the Database can’t find the form although if I use the AcessObject.AllForms, I can get the form name to show up in the Immediate Window.

So what do I do about a form that’s there, that VBA insists isn’t?

It seems rather a silly problem to have when all I want to do is something as simple as having the focus always go to the first field of a subform.

Peter

Reply | Quote

It seems to me that the code in the previous post should really look like this:
Dim ctl As Control
Set ctl = Forms![Family Members]![IndividualID]
DoCmd.GoToControl ctl

Did I parse this right?

Peter

Reply | Quote

Unless I’m missing something, if you want focus to move to first field in subform every time you move to new record (on main form) you can use code like this on main form’s On Current event:

Private Sub Form_Current()

Me.Customer_Orders_Subform1.SetFocus
Me.Customer_Orders_Subform1!OrderID.SetFocus

End Sub

This example uses Northwind.mdb Customer Orders form. OrderID is first field on Customer Orders Subform1. With subform always set focus to subform control first, then to specific control on subform. If you use the subform’s On Current event to set focus on OrderID, then if you click on another field on another record in subform, focus will move to OrderID & you’ll have to click the other field 2nd time to be able to edit it. I don’t think this is what you want. The syntax shown above should work unless there is something unusual about this subform that you haven’t explained. Also, I’d recommend using SetFocus method rather than GoToControl command.

HTH

Reply | Quote

Where is this code running from, the form or the subform? You can’t reference a subform like that, you have to go through the parent form and reference the subform as a control on the parent form to get to its controls collection. The subform is NOT open in the same sense the parent form is, and that may be where your problem is arising. If you use the Me keyword in the code, it has to be running from the subform’s code module

Reply | Quote

The code that I originally used using the Me Keyword was in the subform’s OnCurrent event as you suggested in the original thread which began with Post 214537.

My problem is that the Me keyword works fine on one of my forms and won’t work at all one of my other forms. That one hangs with the error message posted today.

As to what I am trying to achieve: This is a database to manage membership and donations for a church. All Individuals are in the Individuals table (where personal information such as Date of Birth is stored) they are assigned to Families (Where info like address is stored – Our definition of Family being at least one Individual at a single address) through an intermediate table to create the many to many join as in our definition of Family, it is possible for many Individuals to belong to many Families (children of divorced parents being one example).

When the Families form is opened, the first field is the Family Name field on the main form. When you tab through, you jump to the Individuals subform which works fine except for the problem that if you cursor all the way through the entries in the subform and leave it from the last field (Comments) that is where the cursor ends up apparently due to the way Access is designed (see the thread mentioned above). I want the cursor to always jump to the first field which is IndiviualID whenever you enter the subform by tabbing.

This is where the code problem is arising, So Mark’s solution, though appreciated is not what I’m looking for as the focus goes to Family Name when the form is opened.

Some footnotes. The subform works fine except for this refusal to be found when I try to set its OnCurrent event. There is other code in the form which also works fine (such as double clicking on the IndividualID field to be able to open that Individual’s personal info form from the families form and a not in list event to set up a new member(s) from the Families form and add the Individual info on the fly). Both of these much more complicated bits of code work fine on this subform.

Finally, the Donations entry form also has a subform where I have been able to use Charlotte’s “Me” Code in the OnCurrent event of the subform with no problems whatsoever.

So the question is: where should I be looking to troubleshoot these runtime error messages as obviously my other attempts didn’t work. I await with eager anticipation your reply and in the meantime, I’ll do some more investigating myself (although I won’t waste time trying crack further the vagaries of VBA syntax. I’ll be happy to strip down a copy of the relevant bits of the database and post it if that will help. And thanks for your suggestions so far.

Peter

Reply | Quote

Problem Solved!

It seems at one point I had set the main form’s cycle property to Current Record. On fooling around with this code in Northwind and changing the cycle property I discovered that the OnCurrent event stopped working there as well. By restoring cycle to All Records, the problem went away.

So, the question then becomes: If you want the cycle property to remain Current Record, is there something you can do to force the focus to the first field in the subform as you cycle around and around the current record? At this point it is academic, but I would be interested to know if there is an answer.

Peter

Reply | Quote

The simplest thing is to put an unbound textbox on the subform and make it the last control in the tab order. You can make it zero width and transparent and lock it so that the user can’t see it and click into it. Put code in the Enter event of that control to set the focus back to IndividualID. You will also need to trap the PageDown and PageUp keypress events and cancel them to keep the user from moving to another record that way.

Reply | Quote