none
Open another form to add a 'child' record in a split form w/criteria query does not maintain its relationship? RRS feed

  • Question

  • Working on a small solution for my non-profit local genealogy group, and I've been stressed on this problem for quite a few weeks (!) and desperately needs guidance:

    It boils down to:

    Two tables TableA (KeyA) and TableB (KeyA and KeyB) with queries QueryA and QueryB, and a parameterized query QueryBparam (which has preset criteria for KeyA = get_pubKeyA, which works ok).
    Relationship defined: One to many (TableA to TableB) thru KeyA (Primary key on TableA, which also is first part of Primary key on TableB).

    FormA is the 'top' split form with Record Source: QueryA, and with a subform containing FormBsubform (with Record Source = QueryB).
    Subform Master/Child Field links: KeyA.

    FormB is a split form with Record Source: QueryBparam (as mentioned above).

    The requirements is to be able to click on a line in the subform in FormA (FormBsubform) as follows:
     a: Click on an existing record in the subform: Shall open FormB, listing ONLY records from TableB, limited by the KeyA criteria in the QueryBparam, and provide for changes on any listed RecordB.

    <Using< DoCmd.OpenForm FormB, , , , acFormEdit, , KeyBvalue

    >Status> This works as requested, positioning to the first RecordB occurence for the time being.

     b: Click on the 'new record' (last line) in the subform: Shall open FormB, listing ONLY records from TableB, limited by KeyA criteria in the QueryBparam AND opening entry for a 'new' record in formB.

    <Using< DoCmd.OpenForm FormB,,,,acFormAdd,,"000"

    >Status> This does NOT work as requested. The FormB does not list any records AT ALL from TableB (sic), AND the 'new' record in FormB shows withouth the KeyA in the KeyA field of the 'new' record entry in FormB.
      Forcing the KeyA value into the KeyA field of the 'new' record in FormB at the On Current event, still do nothing to the listing of KeyA-related records.
      Seems like the NEW overrides any criteria in the QueryBparam query!?

    So, What am I doing wrong? Are the b: alternative not feasible at all?

    Any suggestions appreciated

    Rgds Tore


    • Edited by Tore HT Monday, February 22, 2016 2:42 PM
    Monday, February 22, 2016 12:16 PM

Answers

  • Calling the OpenForm method with the acFormAdd constant as the DataMode argument will open a form at an empty new record, and will not show any other records, so the restriction on its recordset is irrelevant.  If you wish to show all records in the form, but with focus on an empty new record then you will need to open the form with the acFormEdit constant as the DataMode argument, or simply leave the argument blank, and then move the record pointer to an empty new record with further code.

    To pass a value to the second form the OpenArgs mechanism would normally be employed, passing the key of the calling form's recordset, as you appear to be doing in your first example, in which you pass the value of the KeyBvalue column or control.  This would then normally be read by code in the second form's module and assigned to a property such as the DefaultValue property of a control in the second form.  You'll find an example of the use of the OpenArgs mechanism in this way in FormsDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file, if either of the 'linked forms' options are selected, the buttons in the ensuing Clients form open linked forms passing the ClientID value in this way.  It is then assigned to the DefaultValue property of a combo box bound to the foreign key ClientID column in the called form.

    My demo includes no mechanism for passing a value from an empty new record in the calling form, as to do so would violate referential integrity.  You are passing the literal string '000'.  What you intend by this is unclear.

    Ken Sheridan, Stafford, England

    Tuesday, February 23, 2016 12:08 PM

All replies

  • Your post is difficult to follow.  To begin to clarify one should note that the 'Split' form type is a defined form whereby one has both the continuous and single view together.  In this form type, available from the ribbon - they both (the 2 views within the screen) by definition share the same record source.

    A typical main form / sub form that you construct may or may not be linked by a common cross referencing key field.  The wizard will assist in setting this up when you add the sub form into the main form.

    If there is a link, then when changing records in the main form, the sub form records will automatically change.

    Alternately if the sub form is not linked to the main form as a form property - its record source can be dependent upon a value in the main form.  In this case when changing to a different record in the main form the sub form will not automatically change and one must add vba code to force the sub form to requery.

    Monday, February 22, 2016 2:52 PM
  • "Your post is difficult to follow". Sorry about that. I tried to give all the relevant info, but guess it was an overkill to start with.

    The problem is not with the main form / sub form as such. (FormA/FormBsubform linked thru KeyA)

    The problem is neither not related to the split form part as such.

    The problem is that when I from the main forms subform (FormA's subform: FormBsubform) I tries to open FormB to insert a new RecordB, starting by clicking FormA' subforms last empty data line (indicating new record)  - which kicks off the DoCmd.OpenForm FormB,,,,acFormAdd,,"000". Seems like the criteria in the FormB's  QueryBparam source query is completly ignored!? (FormB's top datasheet part is empty!)

    Se litra b. in first posting where the problem is described.   

    Hope this clarifies a bit.

    • Edited by Tore HT Tuesday, February 23, 2016 8:28 AM
    Tuesday, February 23, 2016 7:36 AM
  • Calling the OpenForm method with the acFormAdd constant as the DataMode argument will open a form at an empty new record, and will not show any other records, so the restriction on its recordset is irrelevant.  If you wish to show all records in the form, but with focus on an empty new record then you will need to open the form with the acFormEdit constant as the DataMode argument, or simply leave the argument blank, and then move the record pointer to an empty new record with further code.

    To pass a value to the second form the OpenArgs mechanism would normally be employed, passing the key of the calling form's recordset, as you appear to be doing in your first example, in which you pass the value of the KeyBvalue column or control.  This would then normally be read by code in the second form's module and assigned to a property such as the DefaultValue property of a control in the second form.  You'll find an example of the use of the OpenArgs mechanism in this way in FormsDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file, if either of the 'linked forms' options are selected, the buttons in the ensuing Clients form open linked forms passing the ClientID value in this way.  It is then assigned to the DefaultValue property of a combo box bound to the foreign key ClientID column in the called form.

    My demo includes no mechanism for passing a value from an empty new record in the calling form, as to do so would violate referential integrity.  You are passing the literal string '000'.  What you intend by this is unclear.

    Ken Sheridan, Stafford, England

    Tuesday, February 23, 2016 12:08 PM