Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set focus to subform (2000 all updates)

    My form is called frmFamilies. The subform is called fsubJoinIndividualFamily.

    When a new record is entered in frmFamilies, I need to ensure that a record is added to the subform.

    I have tried the following code on the BeforeUpdate event of the main form.
    <pre>Dim intTemp As Integer
    intTemp = DCount("IndividualID", "tblJoinIndividualFamily", "FamilyID = " & Me.FamilyID)
    If intTemp = 0 Then
    MsgBox "You need to add an Adult Householder 1.", _
    vbExclamation, "No family identified!"
    Cancel = True
    Forms!frmFamilies!fsubJoinIndividualFamily.Visible = True
    Me.Remarks.SetFocus
    Me.Form!fsubJoinIndividualFamily.SetFocus
    Me.Form!fsubJoinIndividualFamily!cboIndividual.Set Focus
    End If
    </pre>


    This results in runtime error 2110 - can't move focus to suform control.

    I have tried various formulations of the code but the same result occurs.

    The reason for the Me.Remarks.SetFocus line is that I inserted that with the hope the code would work if I made sure the focus was in the form. Probably a redundant line.

    The reason for the Forms!frmFamilies!fsubJoinIndividualFamily.Visible = True line is that the OnCurrent event for the form makes the subform invisible unless a Postal Code exists in the main form. Could be a redundant line as well, because the subform becomes visible as soon as a Postal Code exists.

    Any thoughts?

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set focus to subform (2000 all updates)

    As the name indicates, the Before Update event occurs before the current record has been saved. You cannot set focus to another form (even if it is a subform of the active form) before the record has been saved. You could put code in the After Update event to create a record in the subform (if necessary). Or use an unbound form to create a new family plus the corresponding Adult Householder 1.

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

    Re: Set focus to subform (2000 all updates)

    Why don't you add an event to save the Families form after you have added info to the first field? You can use DoCmd.Save acForm, "form_name"
    in the exit event of your postal code field or some other pre-subform field which will save the record to the table. Once it exists the rest should follow suit.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set focus to subform (2000 all updates)

    Peter
    Thanks for the insight.
    Maybe I'm missing something, but I'm not sure how that prevents what I want to prevent from happening.

    The main form, frmFamilies, has address information. But there has to be at least an "Adult Householder 1" in the subform, frmJoinIndividualFamily, in order for the user to be sure as to who lives at the address in the main form. So what I am trying to ensure that the user enters an "Adult Householder 1" in the subform so that the main form makes sense. Otherwise, somebody comes along and looks at the record and goes "Huh?, who the heck lives here?"

    Putting code on the AfterUpdate event of the main form allows the setting of focus to the subform. However, not what I need to do is add code to the subform to ensure that there is at least an "Adult Householder 1" there.

    Tom

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

    Re: Set focus to subform (2000 all updates)

    Try this: Add a command button onto the main form to save it. (You need one anyways.) This code has the button called command 56. You can use the wizard to make the save procedure. Before the save happens, the code starting with dim intRel will run and if there is no Adult 1 (relationship ID 1) it will give you the message box and set the focus to the subform. Once you add in a householder 1 it will save. This code is going into a command button's on click event so you don't have the before update error message.

    Private Sub Command56_Click()
    On Error GoTo Err_Command56_Click

    Dim intRel As Integer
    intRel = DMin("[RelationshipID]", "qryfsubJoinIndividualFamily", _
    "[FamilyID] = " & Me.FamilyID)
    If intRel <> 1 Then
    MsgBox "You must have an AdultHouseholder 1.", vbInformation + vbOKOnly, "Adult Householder 1 is needed."
    Forms!frmFamilies!fsubJoinIndividualFamily.SetFocu s

    Forms!frmFamilies!fsubJoinIndividualFamily!cboRela tionship.SetFocus
    Exit Sub
    End If
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Exit_Command56_Click:
    Exit Sub

    Err_Command56_Click:
    MsgBox Err.Description
    Resume Exit_Command56_Click

    End Sub

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set focus to subform (2000 all updates)

    Peter N
    The "Save button" code produces an "Invalid use of Null" error.

    Also, is it not the case that Access automatically saves a record? Or is having a Save button an added precautionary measure?

    Tom

  7. #7
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set focus to subform (2000 all updates)

    I see the problem.

    If an Individual exists in the Individual table, but has not been added to a Family...the code breaks down and produces an "Invalid use of null" error.

    Tom

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

    Re: Set focus to subform (2000 all updates)

    Try putting Nz around the Dmin function. You had some null values on RelationshipID in the test database you sent. Otherwise set a break point and let me know the line of code that has the null value.

  9. #9
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set focus to subform (2000 all updates)

    Using the Nz works...as long as the user presses the Save button after entering a record in the subform. If the user enters something other than "Adult Householder 1" and just navigates away, there is still an invalid record.

    Tom

Posting Permissions

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