How to link a SUbform

You can do this provided the main form and subform are bound to data.

Generally, when you add the form using to toolbox, the wizard will suggest to you the links - if the one you want is present select it or choose select my own, and choose the fields from these.

You will then see the parent child fields in the subforms properties after editing.


Kelvin

Ah, I've just reread the question. I'm not sure why you'd do that, but if you do, I'd add the following code to the subforms before Insert event

me.Date = Me.Parent.Date
Me.Comments = Me.Parent.Comments

Kelvin

Here is why Kelvin

Parent Form is bound to tblclaims
Subform is bound to tblcomments

When they enter a value in the subform, I need the "comment" to be linked to the parent record.  

So when I added the subform the wizard did ask me to which fields to link. I selected Invoiceid (which is on the parent form) and invoiceId (which is on the subform). So I would expect that when they enter a comment the relationship is established and I should see that specific comment linked to the Invoice ID in the parent form.  Make sense?

I think you're expecting to see more than is actually happening. When you link a parent and a child, the form will automatically store the parent value into the table for the child records - that is the link. When browsing the data you need a query to join the two tables and display the parent and child data - the basic principle of normalisation. The parent data (with the exception of the primary key) should NEVER be stored into the child table.


Kelvin

Right and I'm asking how to store the parents primary key in the child's table ?

The subform should do that for you. If the parent child relationship is recreated when the subform is placed on the main form - then it will happen.

Kelvin

You don't even need the field that you store the Parent PK on your subform.

Humm then I'm doing something wrong let me check

Ensure that the Field to store the parent ID is in the subform table, the on the main form (in design) select the subform and look at the properties. Look in the data table and ensure that the Link Master Fields and Link Child Fields are correctly populated. If not, drop the subform and add it again and take care choosing the fields to link by.

Note that both the parent and child forms MUST be bound to the tables.

Kelvin

Both have to be bound to the sane table ?

No just to their respective tables.

Main form open in design mode.
Click subform control.
In properties sheet, Data tab.
Link Master Fields: Invoice ID , check if space exists before ID.
Link Child Fields: InvoiceID

ok so I think I have done per the instructions. When I added the subform the wizard asked me to select the parent Link which I selected InvoiceId. However when I try to enter something on the subform it says the automation object Tblclaims.invoiceId can not be found???

I have attached the sample DB, if you could check it for me I would much appreciate it. There is no sensitive data contained in the DB

You will have to link the FE to the BE and the Parent form is CaseDetails and you will see on the first page the tblcomments subform that is giving me all the trouble.
DMC-Backup-Backup.accdb
DMC-Backup-Backup-be.accdb

Linked FE to BE.
Can't find Main Form CaseDetails  or sub form tblcomments  mentioned in comment.

Sorry. Open cases. Then click on any of the hyperlink records and the form will open.

Are you sure we are talking about the same database.
This is the forms available:

Shoot uploaded the wrong one.  I will upload the correct one.  So sorry

No problem, files deleted.

here we go!

Sorry
DMC-Backup-Backup.accdb
DMC-Backup-Backup-be.accdb

Hmmmm, The table tblClaims  for the sy=ubform - also appears to be in the record source for the parent form - I don't think think should be the case? The subform should have a field in the table it is bound to that (InvoiceID) that links to the primary key of the parent table. This does not appear to be the case.


Kelvin

Think I might have it. See uploaded FE. You'll need to relink the FE to your BE.
DMC-Backup-Backup.accdb

So how do I correct this?

Check kelvinsparks's comment.

One change I would recommend is to remove the field InvoiceID from your subform. You don't need to see it and it will confuse the users - it will be populated by the system in the background


Kelvin

Kelvin which subform did you fix?

I am still seeing the same error when I click on the combo box to add a comment.

I was expecting a simple database with a main form and sub form. Once you have it working, then you can expand the design.
Other thing, just add the needed tables to the database as local tables and upload.

main form is Case Details
First page is where the subform is
DMC-Backup-Backup.accdb
DMC-Backup-Backup-be.accdb

Sorry seamus9909,
Form takes long time to load with my system.
When it opens I can't navigate to subform.

Form has lots of information.

I ask you just to upload one file with 2 simple forms showing the issue. Remove all other objects but 2 forms and 2 to 3 tables.

Kelvin can you reply. I linked the changes u made to the BE and that subforn still fails

Sorry, I thought I was meant with the new uploads although not as I asked for.

I linked tblComments.subform2

To do a full test, I'd have to remove references to other accde's etc...

As Hnasr has said - we'd need a copy of your database with only the tables and objects you need remaining. As per my earlier post - I think your basic table design needs a review.


Kelvin

Ok Sorry, I didn't see where you wanted me to send a smaller more streamlined verison Kelvin. I have removed the the accde data base and removed all but the forms and queries that are needed to fix this.

Thanks Kelvin
DMC-Backup-Backup-EE.accdb
DMC-Backup-Backup.accdb

Thanks,

I'll be a couple of hours before I can get to look at this.

Kelvin

Thanks kelvin

any luck kelvin?

Sorry, didn't get back to it. Will try and look at it later today.

K

Hi

Had a quick look. The "back end" you have submitted appears to be the wrong one - there are no tables in it - only links. I cannot proceed any further

Kelvin

here you go!  Can either hnasr or Kelvin look at this today?
DMC-Backup-Backup-EE.accdb
DMC-Backup-Backup-be.accdb

The main field here has tblClaims/InvoiceID and tblDocuments.InvoiceID. Which is the Parent?

Kelvin

tblclaims.invoiceid

OK, your issue appears to come from the fact that your parent form is bound to SQL. As such tblClaims.InvoiceID ceased to be the primary key - which is necessary for a parent child relationship.

So what do I do?

I think you've made the SQL too complex. If a table is used in a subform, the table should not be part of the SQL for the main form. I think you need to review that SQL - at the end of the day, it will make your form run far more quickly.

Each child table needs to have the field for the parent ID, but the subform does not - the data will sort itself out.

Kelvin

So do you mean each CHild TAble needs the same field name as the Parent.

In this case

TblClaim has a field  InvoiceId
Tblcomments should also have a field called InvoiceID?

Yes! You don't need to add the field to the subform, as that is the parent child relationship.

I added it to the copy you sent me, but the form is not happy with the SQL of the Parent form.


Kelvin

I also found tblClaims.InvoiceID appeared twice in the SQL for the main form. Removing one of them did not help the form.

How about If I didn't add a subform but rather included a combo box bound to the tbl comments table. ?

Plus to be clear. The table tblcomments does NOT need the same field of invoiceId?

YEs I need multiple records

@seamus9909,

The database is huge. 40+ tables, 30+ queries, and 20+ forms, and your issue is limited to a main form and sub form.
Try to remove all but a table or two, a query or two and two forms. You may hit on the solution yourself.

Not necessarily me solving the issue, but my contribution may lead, even you to the solution.

As you see, my comment is now the 52nd. This is a delay in your project, and the many comments deters other experts from coming nearer to help.