inconsistent behavior of form / subform link

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • banderson
    New Member
    • Aug 2007
    • 59

    inconsistent behavior of form / subform link

    Hello all,
    I am hoping that you can help me with a problem that has occurred numerous times on various forms in the two databases that I am working on. When I first add a subform to a form linking child and master fields (primary key and foreign key), the subform will automatically update when the master field on the main form is changed (it is in a combo box). However, when I work more on the form - making it a bit more complicated, adding code, search boxes, etc. The subform stops updating, but rather stays on one record even when the master field is changed (when a new value is selected from the combo box that has the master field as its bound column).

    I have tried using Me.Requery in the AfterUpdate Event of the combo box, but this requeries the entire form's contents, resetting it to the first record. I have also tried Me.[SUBFORMNAME].Requery, but this does not seem to work.

    Does anyone know why this feature – the automatic link between child and master fields does not always work? Or why requerying the subform itself does not work? I would appreciate any suggestions.

    Many thanks, in advance.
    Bridget
  • NeoPa
    Recognized Expert Moderator MVP
    • Oct 2006
    • 32633

    #2
    I'm not very experienced in this area I'm afraid Bridget, but I think you should probably be looking more closely at axactly what you are doing when you think it's no longer working. My guess is that what you are doing makes it stop working, rather than it being a flaw in the system itself. Pay particular attention when it next happens and find out what exactly is occurring. That way someone may be able to explain why for you.

    Comment

    • Stewart Ross
      Recognized Expert Moderator Specialist
      • Feb 2008
      • 2545

      #3
      Hi Bridget. I agree entirely with NeoPa - this is something that has been introduced by the actions you are taking. In extensive use of subforms I have never had issues relating to incorrect synchronisation of master-detail records the way you describe.

      Could you clarify what you mean when you talk about selecting a new primary key value for the master field? It is most unusual to have to change the primary key for the one-side record at all, particularly when there are already many-side records in existence. It is not the job of a subform to change secondary keys in such circumstances. Even with Cascade Updates on for your 1-many relationship concerned such a change will not be reflected correctly into many-side records automatically, as Cascade Updates handles modifications to existing key values, not wholesale replacement of those values.

      The parent-child relationship of a main-form sub-form combination takes care of the creation of related secondary keys automatically. However, if you change the primary key to a different one subforms cannot of themselves change the secondary key values to something else - and it is not reasonable to expect them to.

      In any event, if you have relational integrity checking set for the relationship concerned (and you should) Access will simply not allow you to replace a primary key if to do so would leave 'orphaned' many-side records that no longer match to a one-side record.

      -Stewart

      Comment

      • banderson
        New Member
        • Aug 2007
        • 59

        #4
        Thank you NeoPa and Stewart for your responses. I think this may reveal a flaw in my understanding of how forms can /should connect. Let me give a specific example and see if that helps to shed some more light on it.

        I have two forms “frmAddSites” and “sfrmAddr1” that are linked in a form/subform relationship:

        “frmAddSites” has a control source, tblSites.
        The table, tblSites, includes:
        SiteID – primary key, autonumber
        SiteName – text
        Addr1LKUP – number, foreign key to an address table called “tluAddr1”.
        (and a few other descriptive fields)

        The table, tluAddr1, includes the fields:
        Addr1ID – primary key, autonumber
        Addr1 – text
        ZipCodeLKUP – number, foreign key to a zipcode table tluZipCode.

        The table, tluZipCode, holds the city and state information for each zip code:
        Zipcode – primary key
        PostalCityLKUP – number, foreign key to tluPostalCity
        StateLKUP – number, foreign key to tluState

        “sfrmAddr1” has a control source, qryAddr1.
        The query, qryAddr1, holds all of the address information from tluAddr1 and associated lookup tables (see above):
        Addr1ID
        Addr1
        PostalCity
        State
        ZipCode

        “frmAddSites” has the following controls:
        txtSiteID - text box for SiteID
        txtSiteNm - text box for SiteName
        cboAddr1 - combo box for Addr1LKUP.
        The row source for the cboAddr1 is qryAddr1 (bound column is Addr1ID, visible column is Addr1)

        “sfrmAddr1” has the following controls:
        txtAddr1ID - text box for Addr1ID
        txtCity - text box for PostalCity
        txtState - text box for State
        txtZipCode - text box for ZipCode

        The two forms are linked by
        Master Field Addr1LKUP
        Child Field Addr1ID

        The functionality I am hoping for is to be able to choose an address from the cboAddr1 on the main form (frmAddSites) and have the subform (sfrmAddr1) automatically display the associated City, State and ZipCode.

        The code Me.Requery in the AfterUpdate Event of cboAddr1 requeries the entire form, but also jumps to the first record.
        The code Me.sfrmAddr1.Re query does not seem to do anything.
        If I change the selection in cboAddr1 on a record, then move to another record, and go back to the record where I changed the combo box selection, the correct record on the subform is displayed.

        Does this make more sense?
        Is this inherently a problematic way to link a form and subform?
        Is there a way to make the subform display the correct record when the linked field changes in the main form?

        Thanks again,
        Bridget

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32633

          #5
          That's a lot to go through Bridget, but I did see your .Requery request for the subform (which failed) and I think that's related to referencing the item correctly. Check out (Referring to Items on a Sub-Form).

          Comment

          • banderson
            New Member
            • Aug 2007
            • 59

            #6
            Thanks, NeoPa. Sorry for all the details before, I was trying to be thorough, but maybe it was too much...

            I have looked at the "referring to items on a sub-form" and other postings about properly referencing subforms. Using these, I have tried many iterations of the requery code without success. I would like to requery the entire subform, and so my understanding is that I want to reference the form as a control of the main form, rather than a control within the subform. Here is what I have tried, without success.
            Me.sfrmAddr1.re query
            Me!sfrmAddr1.re query
            Forms![frmAddSites]![sfrmAddr1].requery
            Forms![frmAddSites]![sfrmAddr1].Form.requery

            Do you see an obvious mistake in my referencing of the subform for requery?

            I found the posting: http://bytes.com/forum/thread628289.ht ml
            in which someone has similar problems requerying a subform and he ends up scrapping requery altogether and instead resets the recordset clone. His code is more complicated than I need (and frankly than I understand.), though I have had success using code to set a recordset clone for other purposes (i.e. using a combo box to filter a form). I am going to try this tactic to see if it works better than the requery option.

            Thanks again for any help.
            Bridget



            Originally posted by NeoPa
            That's a lot to go through Bridget, but I did see your .Requery request for the subform (which failed) and I think that's related to referencing the item correctly. Check out (Referring to Items on a Sub-Form).

            Comment

            • NeoPa
              Recognized Expert Moderator MVP
              • Oct 2006
              • 32633

              #7
              Originally posted by banderson
              ...
              Forms![frmAddSites]![sfrmAddr1].Form.requery
              ...
              This would be correct if the SubForm/SubReport control on your form were named thus. I suspect this is actually the name of the form that you put IN the subform control.

              Find the name of your SubForm/SubReport control on [frmAddSites] and use that instead.

              Comment

              • banderson
                New Member
                • Aug 2007
                • 59

                #8
                Thanks, NeoPa.
                Sigh. I just think I'm getting the hang of Access/vba and then realize how much I don't really get...
                Thanks again
                Bridget

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32633

                  #9
                  Did you manage to get it to work Bridget?

                  Comment

                  • banderson
                    New Member
                    • Aug 2007
                    • 59

                    #10
                    Thanks for asking.
                    My subform and the name of the subform control are both "sfrmAddr1" . Is it problematic to name the subform control the same as the subform itself?

                    I am making some progress in understanding requerying, but this issue is still a mystery: how to call a requery of a subform from the AfterUpdate_Eve nt of a control in the main form.

                    If I requery a subform from the subform itself (using Me.Requery), it works fine. However, I cannot get it to work by calling it from the main form.

                    I welcome more suggestions and will post if/when I find a solution.
                    Bridget

                    Comment

                    • NeoPa
                      Recognized Expert Moderator MVP
                      • Oct 2006
                      • 32633

                      #11
                      Originally posted by banderson
                      My subform and the name of the subform control are both "sfrmAddr1" . Is it problematic to name the subform control the same as the subform itself?
                      I'm only aware of the problem of following (understanding) the code. If it's easier for you I can't think it's too much of an issue. Generally it's a bad idea (using a name for an object that already has a meaning), but I can see that for something like a subform it makes a sort of sense.
                      Originally posted by banderson
                      I am making some progress in understanding requerying, but this issue is still a mystery: how to call a requery of a subform from the AfterUpdate_Eve nt of a control in the main form.

                      If I requery a subform from the subform itself (using Me.Requery), it works fine. However, I cannot get it to work by calling it from the main form.
                      Have you tried :
                      Code:
                      Call Me.sfrmAddr1.Form.Requery

                      Comment

                      • banderson
                        New Member
                        • Aug 2007
                        • 59

                        #12
                        Thanks, NeoPa.
                        Unfortunately using "Call" didn't work either... I have ended up doing a Requery of the whole form. Then because the whole form Requery results in the form jumping to the first record, I have added a bookmark to return to the record that was being edited. It is frustrating that it cannot be more straightforward , but this works, so I'm going with it!

                        Here is my code to Requery subforms from the AfterUpdate Event of a combo box in the main form. (Really it Requeries the whole form and all of its objects.)

                        Code:
                        Private Sub cboAddr1LKUP_AfterUpdate()
                        'start error check.
                        On Error GoTo Err_cboAddr1LKUP_Click
                        
                        Dim intSiteID As Integer
                        
                          intSiteID = txtSiteID
                        
                          Me.Requery
                        
                          'set recordset clone on main form
                          Set Rs = Me.Recordset.Clone
                          'find record that matches stored ID
                          Rs.FindFirst "[SiteID] = " & intSiteID
                          'set bookmark on main form to the record that matches the stored record.
                          Me.Bookmark = Rs.Bookmark
                        
                        'Exit the sub
                        Exit_cboAddr1LKUP_Click:
                            Exit Sub
                            
                        'Exit the error check.
                        Err_cboAddr1LKUP_Click:
                            MsgBox Err.Description
                            Resume Exit_cboAddr1LKUP_Click
                        
                        End Sub

                        Oh, and good tip about having the subform object name be different from the subform itself to avoid confustion. I plan to change that for my subform object names. Is there a naming convention typically used for subform objects?

                        Thanks again,
                        Bridget

                        Comment

                        • NeoPa
                          Recognized Expert Moderator MVP
                          • Oct 2006
                          • 32633

                          #13
                          I have to admit at this stage that I have very little experience with subforms.

                          I would think that 'sfm' (or 'sfrm') would be a good prefix. I tend to use 'frm' for all forms, whether or not they are intended as subform forms. If that doesn't suit you, then what about 'sfc' for Sub Form Control?

                          Comment

                          Working...