how to add a record to a subform

Posted on 2011-04-23
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 84
    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

    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
    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.)
    LVL 84

    Accepted Solution

    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

    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

    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 30

    Expert Comment

    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

    Thanks for the help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    758 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now