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

    need to update Combo Box (Access 2000 all updates)

    A many-to-many relationship setup. tblFamily, tblIndividual, with a linking table tblJoinIndividualFamily

    I have a form, frmFamilies, which is based on the following SQL
    <code>SELECT qryForFamilyForm.FamilyID, qryForFamilyForm.HouseNbr, qryForFamilyForm.Street, qryForFamilyForm.SecondAddress, qryForFamilyForm.City, qryForFamilyForm.Province, qryForFamilyForm.Code, qryForFamilyForm.[911Address], qryForFamilyForm.Remarks, qryForFamilyForm.Discontinued, qryForFamilyForm.DateDiscontinued, qryForFamilyForm.FullName, qryForFamilyForm.RelationshipID, qryForFamilyForm.[Primary Contact], qryForFamilyForm.Congregation, qryForFamilyForm.LastName, qryForFamilyForm.FirstName, qryForFamilyForm.[Both Adults]
    FROM qryForFamilyForm
    WHERE (((qryForFamilyForm.Discontinued)=No));</code>

    Individuals are added to the family through a subform, fsubJoinIndividualFamily, which has the following SQL
    <code>SELECT tblJoinIndividualFamily.FamilyID, tblJoinIndividualFamily.IndividualID, tblJoinIndividualFamily.RelationshipID, tblIndividual.FirstName, tblIndividual.LastName, [LastName] & ", " & [FirstName] AS FullName, tblIndividual.RemoveDate, tblIndividual.DOB, DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd")) AS FixAgeYears
    FROM tblIndividual LEFT JOIN tblJoinIndividualFamily ON tblIndividual.IndividualID = tblJoinIndividualFamily.IndividualID
    ORDER BY tblJoinIndividualFamily.FamilyID, tblJoinIndividualFamily.RelationshipID;</code>

    Another subform on frmFamilies is fsubContactData, where phone #s and e-mail addresses can be added. This subform is based on tblContactData which has 4 fields (FamilyID linked to tblFamily, ContactTypeID linked to tblContactType, IndividualID linked to tblIndividual, ContactData ... the first 3 of these fields comprise the PrimaryKey)

    On this subform, fsubContactData, the control with which I am having difficulty is cboIndividualID. It is a combo box that has the row source
    <code>SELECT tblJoinIndividualFamily.IndividualID, [FirstName] & " " & [LastName] AS FullName, tblJoinIndividualFamily.FamilyID
    FROM tblIndividual INNER JOIN tblJoinIndividualFamily ON tblIndividual.IndividualID = tblJoinIndividualFamily.IndividualID
    WHERE (((tblJoinIndividualFamily.FamilyID)=[Forms]![frmFamilies]![fsubContactData].[Form]![FamilyID]));</code>
    I want to restrict the options in that combo box to the names of the individuals in the family, those that are in fsubJoinIndividualFamily.
    Where the difficulty arises is that the names that display in that combo box are always those from the first record....unless I add Me.cboIndividualID.Requery to the Current event for the subform.

    This works. But it seems to me there should be a way for that combo box to refresh automatically as I cycle from record to record.

    Tom

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

    Re: need to update Combo Box (Access 2000 all updates)

    Nope. You do need to requery the combo box.

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

    Re: need to update Combo Box (Access 2000 all upda

    Thanks, Hans.

    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
  •