We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

how to add a record to a subform

Medium Priority
360 Views
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.
je
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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

Commented:
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.
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
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.)
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
je

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


===========================================================

Sub-form
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

Commented:
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;
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
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

Commented:
Thanks for the help!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.