none
Moving to a new record in Parent-Child form structure RRS feed

  • Question

  • I have a form that i use to record training events and a subform that lists all the related activities to each training event . I have a save button that works correctly (I assume).  But I want to be able to move to a new record without the jeopardizing the data integrity of the parent child data.  Using VBA code how can I ensure that a child record is created for every Parent record, as well as give the end user,  the ability to move to a new record on the parent form.  

    N.B. I have a "Add Activity button" on the main form to create the activities.   I need some advice on the proper sequence to use in this kind of structure. Specifically how to move to a new record.


    • Edited by osolaru71 Thursday, March 28, 2019 7:08 PM
    Thursday, March 28, 2019 7:04 PM

Answers

  • Yes, I think so. Again, you can do almost anything you want to happen using code. If the activities are mandatory, you can automatically create them as soon as an event is created. It they're not mandatory, you can assist the user to create them automatically, if they agree, or let them create it manually themselves. As for moving to a new record, if an event is not mandatory, then no problem, just go to a new record. However, if they're mandatory, then force the creation of the activities first before moving to a new record.

    • Edited by .theDBguy Friday, March 29, 2019 7:16 PM
    • Marked as answer by osolaru71 Thursday, April 4, 2019 8:00 PM
    Friday, March 29, 2019 7:16 PM
  • The number of columns in the table is immaterial; you simply need to look up its primary key.  The criteria for this, assigned to the conCRITERIA constant, would include a subquery in which the WHERE clause compares the foreign key in the referencing table with the primary key of the referenced table.

    PS:  Later in the code, when the FindFirst method of the RecordsetClone is called, the value of the key only needs to be wrapped in literal quotes characters , as in my demo, if it is of text data type.  If its a number data type the literal quotes characters can be omitted.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, April 2, 2019 5:56 PM Postscript added.
    • Marked as answer by osolaru71 Thursday, April 4, 2019 8:00 PM
    Tuesday, April 2, 2019 5:51 PM

All replies

  • Hi. Can you post some screenshots of your form? To move the record pointer to a new record using VBA, you could use the GoToRecord method of the DoCmd object using the argument acNewRec. But without being familiar with your form setup, I am not sure this is all you need.
    Thursday, March 28, 2019 7:12 PM
  • For some reason the forum is not allowing me to post the screenshot of my form.   

    

    Thursday, March 28, 2019 8:12 PM
  • For some reason the forum is not allowing me to post the screenshot of my form.   

    


    You could try posting it to a fileshare and then post a link here. I think that's allowed.
    Thursday, March 28, 2019 8:26 PM

  • olu Solaru

    Friday, March 29, 2019 1:34 PM
  • I currently have no code behind the Add New Button. Not sure if it makes sense to enforce that the user must create an Activity before they can save the Event record.  I may be making this more complex that it needs to be

    olu Solaru

    Friday, March 29, 2019 1:36 PM

  • olu Solaru

    Friday, March 29, 2019 2:35 PM
  • Hi. Depending on your business rules, you could make it mandatory or optional to have at least one activity per event. If you want it done automatically, then in the AfterUpdate event of the main form, you could execute an APPEND query to the Activities table to create a child record for the newly created event.
    Friday, March 29, 2019 3:14 PM
  • Not sure if doing it automatically will work, but if  to ensure there is at least one activity will work perfectly.  How will i go about that from VBA code.  I am thinking about disabling the AddNew Record button the main form, and using the  code as condition for enabling the Add New record button.   Any thoughts?

    olu Solaru

    Friday, March 29, 2019 5:30 PM
  • Hi. As I said, you could use the main form's AfterUpdate event to execute an APPEND query to create the child table. You can check if the current record is a New Record and/or if the child table is empty first.
    Friday, March 29, 2019 6:12 PM
  • My point is that If that I am not sure if the clients would want the creation of the activities to be automated.   Hope I'm making sense.

    olu Solaru

    Friday, March 29, 2019 6:25 PM
  • My point is that If that I am not sure if the clients would want the creation of the activities to be automated.   Hope I'm making sense.

    olu Solaru


    Hi. As I said earlier, it depends on your business process. Your business rules should dictate what the clients/users can/cannot do. You then just code/design to accommodate it. For example, if the business process allows the user to optionally create an event, then you could prompt them to confirm an auto-create and offer a cancel button.

    • Edited by .theDBguy Friday, March 29, 2019 6:34 PM
    Friday, March 29, 2019 6:34 PM
  • Sorry not automated, I meant to say automatic.  They would want that process to be manual because some of the data would be pulled from another source.

    olu Solaru

    Friday, March 29, 2019 6:45 PM
  • So, was what I was saying make any sense? You can either auto-create, ask user if they want to create, or do nothing. It's all up to you.
    Friday, March 29, 2019 6:54 PM
  • Let me just clear up some confusion that I may be causing.   Every training event has to be recorded in the database, and each training event can have one or multiple activities.  Now,  based on the customer's requirement, and business rules, an or multiple activities are mandatory for every event.  I already have code setup to add the activities  to the event, but nothing to ensure that the activity is mandantory. And I also want to be able to move to a new record window without running the risk of creating any orphan records.  SO are you saying that your suggestion will fix that? At least the part about ensuring that the activities are created?

    olu Solaru

    Friday, March 29, 2019 6:57 PM
  • Yes, I think so. Again, you can do almost anything you want to happen using code. If the activities are mandatory, you can automatically create them as soon as an event is created. It they're not mandatory, you can assist the user to create them automatically, if they agree, or let them create it manually themselves. As for moving to a new record, if an event is not mandatory, then no problem, just go to a new record. However, if they're mandatory, then force the creation of the activities first before moving to a new record.

    • Edited by .theDBguy Friday, March 29, 2019 7:16 PM
    • Marked as answer by osolaru71 Thursday, April 4, 2019 8:00 PM
    Friday, March 29, 2019 7:16 PM
  • The code for the Click event procedure of a button to go to a new record in a form is:

        DoCmd.GoToRecord acForm,Me.Name,acNewRec

    Using a bound form, it is impossible to prevent a record in the parent form being saved before one or more rows are created in the activities table.  Referential integrity requires that a row must exist in the referenced table before a row can be inserted into the referencing table.  What you have to do is, when the parent form's record is saved by the user attempting to move to another record, including a new one, or when the user attempts to close the form, immediately move back to the newly entered record if no rows have been inserted in the referencing table via the subform.

    You'll find an example of this in Families.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.

    The relevant code is in the parent form's Current and Unload event procedures.

    You say '…..based on the customer's requirement, and business rules, an or multiple activities are mandatory for every event.'  Does this mean that for each customer/event there is a fixed subset of mandatory activities?  If so you should model this in a table like this:

    MandatoryActivities
    ….CustomerID  (FK)
    ….EventID  (FK)
    ….ActivityID  (FK)

    This models a ternary relationship type between customers, events and activities by resolving the many-to-many relationship type into three one-to-many relationship types.  The table is 'all key'  i.e. its primary key is a composite of the three foreign keys.

    With this table it is then easy to execute an INSERT INTO statement ('append' query) in the AfterInsert event procedure of the parent form and requery the subform to show the records, ready for the user to insert missing data into the other columns now or later.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, April 2, 2019 10:56 AM Hyperlink added.
    Saturday, March 30, 2019 1:59 PM
  • Ken,

    I wouldn't say they are fixed, But there should be at least one activity in their.


    olu Solaru

    Tuesday, April 2, 2019 12:51 AM
  • I wouldn't say they are fixed, But there should be at least one activity in their.
    My Families demo, to which I referred you earlier, demonstrates how to enforce at least one row in a referencing table for each row in a referenced table.

    Ken Sheridan, Stafford, England

    Tuesday, April 2, 2019 10:55 AM
  • Ken,

    On this particular line - varFamilyName = DLookup("FamilyName", "Families", conCRITERIA), I see you are referencing the parent table., which has just one column, but in my situation I have numerous columns.  How can I use the same syntax?  My parent table is called tblTraining, and EventID is the PK, and it is a FK in the activities table.   Do  I just chose the column that is the primary key in the syntax used above.?


    olu Solaru

    Tuesday, April 2, 2019 1:23 PM
  • The number of columns in the table is immaterial; you simply need to look up its primary key.  The criteria for this, assigned to the conCRITERIA constant, would include a subquery in which the WHERE clause compares the foreign key in the referencing table with the primary key of the referenced table.

    PS:  Later in the code, when the FindFirst method of the RecordsetClone is called, the value of the key only needs to be wrapped in literal quotes characters , as in my demo, if it is of text data type.  If its a number data type the literal quotes characters can be omitted.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, April 2, 2019 5:56 PM Postscript added.
    • Marked as answer by osolaru71 Thursday, April 4, 2019 8:00 PM
    Tuesday, April 2, 2019 5:51 PM