inconsistent behavior of form / subform link

[banderson]

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]

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.

[Stewart Ross]

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

[banderson]

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

[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).

[banderson]

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.requery
Me!sfrmAddr1.requery
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.html
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

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

[NeoPa]

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

[banderson]

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

[NeoPa]

Did you manage to get it to work Bridget?

[banderson]

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_Event 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

[NeoPa]

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.

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_Event 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 :

Call Me.sfrmAddr1.Form.Requery

[banderson]

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

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

[NeoPa]

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?