Subforms : Creating Parent and Child Forms

A subform is a form that is placed in a parent form, called the main form. Subforms are particularly useful to display data from tables and queries that have one-to-many relationships. Learn: What is a subform? How to create a form and subform at once, how to use subform wizard and Drag-and-drop method.

Sponsored Links:

Related Topics

Parent/Child subform
I have 2 tables(1 parent 1 child). I would like to have the the conents of the parent and child side by side on a form. So I have two sub forms each bound to the parent and child tables. My problem is I only want the child table to display the data based on the selected row from the parent table. I can't put the child subform within the parent subform and I still have to be able to add new rows two both tables through this interface


A2007 ADP/SQL Parent Child form
Access 2007 ADP/ SQL Server 2005 BE / Win7 x64

Most of my app will have a lot of parent/child forms. My question is what is the best preferred method of first creating the parent record and obtain the ID and pass to the child datasheet record.

Just curious to see what I need to do before I spend a lot of wasted time on a wrong method.


MDI Parent - Child Form?
I'm working on a 3 tier payroll application.

I am suppose to open every child form into the parent form .I.e. if I click on any button to open a form, a new parent window with complete menus should open and in the middle the child form will be displayed. This should happen in case of opening any forms.

If I open an employee form, parent window should be displayed with it. likewise with all other forms.


Refreshing Parent Form when new record added via SubForm
I have a Parent-form containing two Sub-forms. Each sub-form updates records in their respective table as well as a shared table. The two ID fields in each sub-form are linked as "Child fields" to the Parent-form's "Master Fields".

The Parent form gets it's "Master Field" values from a Query. This query is based on the records updated by either of the two subforms. After I add a new record via one of the subforms, the Master fields on the Parent form do not update. The only way to get it to update is to close the Parent form altogether and then open it again.

I think what I need to be doing is adding something to "On Mouse Up" of the button, or even better, the "After Update" on one field in each of the subforms.


Need help with parent child query
I have a table as below. It only has one child level

ID | Name | | Parent
1 | KSP | | 0
2 | PSP | | 0
3 | HJI | | 1
4 | MXM | | 2

I want the output like : basically all parent should follow their child and order on parent name

ID | Name | | Parent
1 | KSP | | 0
3 | HJI | | 1


How do I query multiple fields, and sum them all up?
I have a table with transactions. The transactions are for both Parent companies and Child Companies.

I created another table with the Parent in one field, and all the child companies as other fields of the Parent record.

Can I use a query to sum all of the transactions for all of the child companies and the parent company, and combine them.

My goal: To sum all transactions from both parent and child companies within a data range.

I can do them all seperately, because I have all the company records(child and parent) as seperate records, in a Customer table, however I am having difficulty determining the best method for combining the parent and child company totals together


Set subform AllowEdits property from another form
When my form that contains two subforms is opened from certain other forms, I want to disallow edits on one of the subforms and on the parent.

Docmd.openForm "ParentName"
Forms!ParentName.AllowEdits=False
This only works for the parent-the subforms still allow edits (something that affects totals on the Parent)

I added:
Forms!ParentName.Form.Child2Name.AllowEdits=False

which gives me an error message.

I am using A2007 with W7.


Set subform AllowEdits property from another form
When my form that contains two subforms is opened from certain other forms, I want to disallow edits on one of the subforms and on the parent.
My code:

Docmd.openForm "ParentName"
Forms!ParentName.AllowEdits=False
This only works for the parent-the subforms still allow edits (something that affects totals on the Parent)

I added:
Forms!ParentName.Form.Child2Name.AllowEdits=False

which gives me an error message.

I am using A2007 with W7.

I know that I can open the whole form as read-only, but would like child1 to be available for editing. I have searched, and found things that I thought would apply, but am missing something.


On child form create bound list box to show related records from parent form
I have two tables with a many-to-many relationship which is causing many problems. My parent and child forms are linked via a junction table. I want to make it possible to create aread-only version of the child table so that you don't have to link to it from the parent table.

A form linking the two together has been created with a combobox where you can pick and choose which records relate to each other.

I'd like on the child form there to be a list box or combo box which shows the records from the parent table linked to that record from what is picked within the comboboxes of the "junction form".

In essense it would mean that people reading the child form would know which names the record is based on if they didn't want to go via the parent form to read it.


How to find the most recent inserted auto number is?
In one of the forms is a Parent / Child situation. The program inserts the parent then allows the children to be inserted or updated. The parent and children are related by an autonumber database table column called CustomerID. After a parent is inserted into the database I would like to find out what that autonumber ID is so I can use it for the child data.


Translating Excel Formula to Access
I am new to this forum and extremely new to access so please forgive my faults. I am currently in the process of taking an excel spreadsheet and creating an access query that mimics it and its formulas.

I have been successful in replicating the formulas within access so far, until recently. I have a formula in excel that adds up the quantities of children items and displaysit on the parent item

Example of child/parent

Parent
8-8-12 3041535 DriftCat_P | (child) 8-8-12 3041535 DriftCat_P | (child)
8-8-12 3041535 DriftCat_P | (child)

My formula would display the quantity of the children on the parent item line

Here is the formula:


Wizard picks parent child Form-Sub-Form hierarchy
What determines whether Access Wizard chooses a table as the parent or child in form-subform setup

I would assume the form on the one side of the one to many relationship would be the parent form.

But the table I want as the parent is being subjugated to child. It's not giving me the option to switch it around


parent child link query
I have a query which has a parent and child link tables and would like to have all the records in the parent even no child for this parent.

It seems MS Access gives the link only the parent has child. I try to use outer join, but MS Access gives me it contains ambiguous outer joins.


Master/Child between Subforms
Let's say that A is the mainform. B and C are subforms each within A. Neither B nor C are subforms within the other. Is it possible to create a Master/Child relationship in which afield within B is the Master and a field within C is the child?

If so, is it possible to accomplish this if both B and C are displayed in Datasheet view?


Child/Parent relationship
I'm working in Access 2007. Some how I deleted a relationship. When I put it back it appears to have changed the parent and/or child relationship.

How do I edit the parent/child relationship. I found this once before but can't find how to do it again.


How To Find A Parent Their Children (If They Have Any) Using VBA?
I am using Excel (2007) VBA and I want to retrieve data from an Access (2007) database. My Excel VBA skills are intermediate but my Access VBA skills are novice.

This database has one parent table (PARENT) and one related child table (CHILD). The parent record may have no child records or anywhere from 1 to 5 child records. Each parent and childrecord has 3 fields, respectively.

When I click a command button (Form Control, not ActiveX) on the worksheet, I want to retrieve all of the fields for one particular parent record (it would be specified in the WHEREcriteria of the SQL statement) and any associated child records, if applicable, and save all of these values to a range of cells in my worksheet.

The desired outcome on the worksheet would look like:

Cell Value
A1 Parent record, field 1
...

Explanatory Notes:
1. If a parent record had no child records, it would simply leave cells A4:A18 empty.
2. If a parent record had 1 child record, it would leave cells A7:A18 empty.
etc.

QUESTION: How would I accomplish this in VBA using an ADO connection and what would the SQL statement look like?


Recursive Report!
Just a bit ago I figured out how to "tier" Records in a Table so that the Table knows that certain Records are "children" of another Record. Basically, you just reference the ID of the immediate parent Record in the same table.

RecordID|ParentID|Description
1|0|Parent Record
2|1|Child Record 1
...

Basically, I want to "tab in" child records, like so:

* Parent Record
* Child Record 1
* Sub-child Record 1.1
* Sub-child Record 1.2
...
* Child Record 1
* Sub-child Record 1.1
* Sub-child Record 1.2
...


Master/Child between Subforms
Lets say that A is the mainform. B and C are subforms each within A. Neither B nor C are subforms within the other. Is it possible to create a Master/Child relationship in which a field within B is the Master and a field within C is the child?
If so, is it possible to accomplish this if both B and C are displayed in Datasheet view


Conditional Back Color
We have a form with many subforms. On one of the subforms, there is a single text box (form is set to Continuous Form). When the form is loaded, that single textbox turns into many based onhow many rows are in the table populating it.

The problem is that we want to change the back/fore color based on the value in each of the text boxes. If we just use the following:

if Forms![parent form]![child form].Form![child form control name] = x then

....backcolor = vbgreen
....forecolor = vbgreen

end if

it looks at the first value, while ignoring all others and assigns a color. What we want is for it to instead look at each individual record and assign a color.

I have never used a For Each loop, The problem is that I haven't ever used one lol. I am not sure if it will even work.


2 Subforms (parent-child) on a Main Form
I have a main form with two sub forms.
Main Form - Single Form
Subform 1 - Datasheet
Subform 2 - Datasheet

I have the Main Form parent to Subform 1 child.

Subform 2 is also on the Main Form.

Can I, and how, make subform 2 a child of subform