How to link a SUbform

I want to add a subform to a main form and when I enter a value in the subform it will link to the main form.

The Main form has a field called Invoice ID

The subforms record source contains:
     a.  ID
     B.  InvoiceID
     C.  Comments
     D.  Date

So when I enter something in the Subform I want to store the comments and date in the record source with the Invoice ID stored from the main form.  How can I do this?
seamus9909Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
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
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Kelvin SparksCommented:
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
0
seamus9909Author Commented:
Right and I'm asking how to store the parents primary key in the child's table ?
0
Kelvin SparksCommented:
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
0
Kelvin SparksCommented:
You don't even need the field that you store the Parent PK on your subform.
0
seamus9909Author Commented:
Humm then I'm doing something wrong let me check
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
Both have to be bound to the sane table ?
0
Kelvin SparksCommented:
No just to their respective tables.
0
hnasrCommented:
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
0
seamus9909Author Commented:
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
0
hnasrCommented:
Linked FE to BE.
Can't find Main Form CaseDetails  or sub form tblcomments  mentioned in comment.
0
seamus9909Author Commented:
Sorry. Open cases. Then click on any of the hyperlink records and the form will open.
0
hnasrCommented:
Are you sure we are talking about the same database.
This is the forms available:

forms in db
0
seamus9909Author Commented:
Shoot uploaded the wrong one.  I will upload the correct one.  So sorry
0
hnasrCommented:
No problem, files deleted.
0
seamus9909Author Commented:
0
Kelvin SparksCommented:
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
0
Kelvin SparksCommented:
Think I might have it. See uploaded FE. You'll need to relink the FE to your BE.
DMC-Backup-Backup.accdb
0
seamus9909Author Commented:
So how do I correct this?
0
hnasrCommented:
Check kelvinsparks's comment.
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
Kelvin which subform did you fix?
0
seamus9909Author Commented:
I am still seeing the same error when I click on the combo box to add a comment.
0
hnasrCommented:
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.
0
seamus9909Author Commented:
main form is Case Details
First page is where the subform is
DMC-Backup-Backup.accdb
DMC-Backup-Backup-be.accdb
0
hnasrCommented:
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.
0
seamus9909Author Commented:
Kelvin can you reply. I linked the changes u made to the BE and that subforn still fails
0
hnasrCommented:
Sorry, I thought I was meant with the new uploads although not as I asked for.
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
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
0
Kelvin SparksCommented:
Thanks,

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

Kelvin
0
seamus9909Author Commented:
Thanks kelvin
0
seamus9909Author Commented:
any luck kelvin?
0
Kelvin SparksCommented:
Sorry, didn't get back to it. Will try and look at it later today.

K
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
here you go!  Can either hnasr or Kelvin look at this today?
DMC-Backup-Backup-EE.accdb
DMC-Backup-Backup-be.accdb
0
Kelvin SparksCommented:
The main field here has tblClaims/InvoiceID and tblDocuments.InvoiceID. Which is the Parent?

Kelvin
0
seamus9909Author Commented:
tblclaims.invoiceid
0
Kelvin SparksCommented:
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.
0
seamus9909Author Commented:
So what do I do?
0
Kelvin SparksCommented:
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
0
seamus9909Author Commented:
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?
0
Kelvin SparksCommented:
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
0
Kelvin SparksCommented:
I also found tblClaims.InvoiceID appeared twice in the SQL for the main form. Removing one of them did not help the form.
0
seamus9909Author Commented:
How about If I didn't add a subform but rather included a combo box bound to the tbl comments table. ?
0
seamus9909Author Commented:
Plus to be clear. The table tblcomments does NOT need the same field of invoiceId?
0
Kelvin SparksCommented:
Using a combo limits you to one value per main record. The purpose of a subform is to allow multiple records. Which do you need.

Yes the table needs to have the field InvoiceID, but the subform does not.


Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
seamus9909Author Commented:
YEs I need multiple records
0
hnasrCommented:
@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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.