Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with Event Procedure (A2K-SR1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with Event Procedure (A2K-SR1)

    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:

    <pre>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)</pre>

    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Event Procedure (A2K-SR1)

    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

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Event Procedure (A2K-SR1)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with Event Procedure (A2K-SR1)

    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
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Problem with Event Procedure (A2K-SR1)

    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

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Event Procedure (A2K-SR1)

    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

  8. #8
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with Event Procedure (A2K-SR1)

    Problem Solved! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    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

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Problem with Event Procedure (A2K-SR1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •