Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


how to add a record to a subform

Posted on 2011-04-23
Medium Priority
Last Modified: 2012-08-14
Dear Experts,

I have a form with a subform linked by a project ID.  It's a one-many relationship.  How can I automatically populate the sub-form ID field when adding records in the sub-form?

Thanks for the help.
Question by:aeolianje
LVL 85
ID: 35454900
Your Subform should be "linked" to the master table via the Master/Child link properties of the Subform control. If you set that value correctly, Access will handle the linking for you.

If you're referring to the ID value of the Subform's recordset, then please respond back and we'll dig a little deeper.

Author Comment

ID: 35454926
The main form has ID as the autonumber key.
The subform also has an autonumber key - but is linked to the main from using the Master-Child link.  The Master ID is linked to the sub-forms fkProject field (which is a number field).

It looks like it saves the information to the sub-form table when I close the form.  But when I reopen the form, I don't see the corresponding sub-form information showing along with the Master data.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35456175
Are you saying that the foreign key field in the subform data is not being populated?  Or that it's populated, but then not used to correctly display the related child records when it next loads?

Have you made that foreign key field control visible in the subform so that you can see if being populated as you fill in rows in the subform?

How do you load the data into the subform?  Simply using a RecordSource property to a local or linked table?
(Assigned recordsets don't adhere to the Master/Child link field functionality.)
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 750 total points
ID: 35458930
Your Master/Child links should be set as such:

Master = Mainform.ID
Child = Subform.TheFieldThatHoldTheIDValueAbove

In other words - your subform is based on a table/query. That table/query must somewhere store the value of Mainform.ID in order to "relate" those records. If you set the Master/Child links correctly, Access will properly store and show the right records.


Author Comment

ID: 35470290
sorry for the delay -- I'm out of town -- but still trying to resolve this issue.... Here's some more info.

Thanks again for your help.

Main form
Record Source: SELECT tbl_Projects.*, tbl_ProjMgr.ProjMgr FROM tbl_ProjMgr RIGHT JOIN tbl_Projects ON tbl_ProjMgr.ProjMgrId=tbl_Projects.[Project Manager] ORDER BY tbl_Projects.[Project Name];
Master key: ID


Source Object: frmResourceRequest
Link Child Fields: frmResourceRequest.fkProject
Link Master Fields: ID

frmResourceRequest    Record Source: tbl_ResourceRequestAllocation2
Control source:  fkProject    has a source type of TableQuery

fkProject is a number field that has a lookup set to TableQuery with Bound Column as 1.
But nothing listed as the Row Source

Author Comment

ID: 35470304
correction -- fkProject is a number field that has a lookup set to TableQuery with Bound Column as 1. with row source as :
SELECT tbl_Projects.ID, tbl_Projects.[Project Name], tbl_Projects.[Project Status], tbl_Projects.[Project Manager], tbl_Projects.[Project Start Date], tbl_Projects.[Project End Date], tbl_Projects.[Project Effort] FROM tbl_Projects;
LVL 31

Expert Comment

ID: 35809573
In general:
Main form-----------------can be bound or unbound---------------
===F1    form field name used as link Master field
can be a bound or unbound form field name, its value can be an expression

SubForm ------------------bound form---------------
===Fx    form field name used as link child field.
It is a bound field

Author Closing Comment

ID: 35841805
Thanks for the help!

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question