1

Still learning, apologies in advance.

I have two tables Parenttbl and Childtbl, linked in a one to many relationship.

I have a ParentForm and a ChildForm. I would like a button on my ParentForm that opens ChildForm with a new record, where the foreign key is automatically populated, but cannot for the life of me do this.

I have played around with the Macro Editor. With OpenForm I can create a new record, but the foreign key is blank.

I have tried to follow the instructions here and here, but could not get it to work.

Thanks in advance.

randouser
  • 97
  • 10
  • 1
    Simplest thing to do is a form/subform arrangement and no code needed. Otherwise, use macro or VBA (I only use VBA). The second link you show looks appropriate. Here is another [Link a popup form with a master form](https://stackoverflow.com/questions/58786474/link-a-popup-form-with-a-master-form). This is a common topic. What does 'could not get it to work' mean - error message, wrong result, nothing happens? You should provide your attempted code. Edit question. – June7 Sep 18 '20 at 05:36
  • 1
    I created a subform, followed the solution in the link you provided, but the new record the button creates still has the foreign key set to '0'. – randouser Sep 18 '20 at 06:16

2 Answers2

1

If you have set up the subform control and its properties LinkMasterFields and LinkChildFields, the FK will be filled in automatically. Zero code is needed.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I tried a subform, with both both those properties set, then created a button in the subform. But the entry it creates the ForeignKey is always set to 0. – randouser Sep 18 '20 at 06:09
  • 1
    Then, most likely, you haven't defined [referential integrity](https://learn.microsoft.com/en-us/office/troubleshoot/access/define-table-relationships?WT.mc_id=M365-MVP-5002361) between the two tables. – Gustav Sep 18 '20 at 06:31
  • Thanks for the reply. I read that link, and can confirm I have followed each step in creating a one to many link from my parent's primary key to the child's foreign key. – randouser Sep 18 '20 at 06:54
  • Then something else is going on. This is so basic a feature of Access, and I have never seen it fail. – Gustav Sep 18 '20 at 07:50
  • 1
    If you truly have a form/subform arrangement then there should be no need for a button for creating foreign key. As Gustav said, this is basic Access functionality. Setting Relationships is not even required for building a form/subform. Have you studied an introductory tutorial book? – June7 Sep 18 '20 at 16:03
1

As noted, if you drop the 2nd child form into the main form, then no add button is required, and you can even edit all the past/existing records.

However, if you really want two separate forms?

The the button code to launch the 2nd form can be this:

if me.dirty = true then me.dirty = false ' save our data in form
docmd.OpenForm "MyChildForm",,,,acFormAdd
forms("MyChildForm")!MyFKColumnName = me!id

So the above will launch the form to a new new blank reocrd, and then we set the column used to relate back to the main form. Of course you replace !MyFKColumn with the actual name of the column used in this relationship. And same goes for the often used "ID", so repalce "ID" in me!ID with the name of your PK column in the main form.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51