Forcing a child form to store the ID of the parent - I think...

[cynaptic]

I have a form that has a sub form linked by companyName, what I would like to do is get the sub form (notes) to automatically store the company name of the referring record. I am just starting on the path to VBA so am not quite clear what to do.

I think I should be using the child/parent syntax but not sure how.

Any thoughts or guidance appreciated.

 

[gusbrunston]

Hi, Cynaptic:

The form / subform construct is most often used for tables with a one-to-many relationship. The table on the "1" side underlies the "main" form, and the table on the "many" side underlies the sub form. Such relationships are the basis of a "relational database."

You might link the sub with the main form by using the name of the primary key in Table1. Include a field by that name in Table2. If "companyName" is the primary key of your Table1, then put a field in Table2 named "companyName", and then link the sub form with the main form with "companyName".

If it were me, I'd use a primary key of "CompanyID" in Table1, and a primary key of "NotesID" in Table2. With a field in Table2 named "CompanyID", you can refer back to all the data in Table1 for forms and reports without storing the "Company Name" in every record of Table2. If the companyName = "The Greatest Little Pizza Parlor West of Scranton Pennsylvania and East of Salem Oregon" then that's a lot of data to store in every record!

I hope I've understood your question...if not, holler. You don't have to know much about VBA to accomplish what you're trying to do, but you might profit by looking at table relationships and query design, as well as forms and sub forms.

Oh, and anything that comes from me always has only to do with Access 2000...I've no experience with any other version.

Cheers Gus Brunston An old PICKer, using Access2000
padregus@attbi.com

 

[gusbrunston]

Heck, Cynaptic:

I was proudly reviewing my answer to your thread, and I don't think I answered your question at all! You can link a sub form to a main form using the property sheet of the subform. Select Data, and choose the fields you want to link with in the rows named "Link Child Fields" and "Link Master Fields". But still, don't your tables have to be related in order for the sub form to store any data in its underlying table?

To get to the property sheet I'm talking about, you have to click on the edge of the subform, you won't get it by clicing in the rectangular box at the upper left hand corner...

over and out Gus Brunston An old PICKer, using Access2000
padregus@attbi.com