Filter Parent Data By Child Data?
Jul 11, 2006All,
Is it possible to filter parent records by child records (or subdata) without showing multiple instances of the parent data in the form/query?
Cheers,
Bobadopolis
ADVERTISEMENT
Modules & VBA :: Can Filter Child Subform From Parent Form?
Jun 11, 2013I am updating an existing database and I have a parent/child form and I want to filter the subform for the current parent form record? The two forms are linked together by woWorkOrderID/deWorkOrderID.
View 1 Replies View RelatedParent/child Relationships
Oct 12, 2006Hi all
I seem to be having a problem with acess 2003 in that when ever I set the parent/child relationships and go back to the front end the simple disappear?
any thoughts would be grate
Thanks
James
Parent-Child Cascading Deletes
Nov 4, 2007I have a simple Family table:
PersonID
Name
ParentID
where, in the case of a child, its ParentID contains the PersonID of its parent.
In the Relataionships window I put two such tables, Family and Family_1 and I joined Family.ParentID to Family_1.PersonID. I set referential integrity, cascading updates and cascade deletes.
I made a Parents form (by setting the ParentID criteria to Is Null), and added a Children subform (linking the ParentID of the subform to the PersonID of the main form).
The cascading updates works when I add a new child but when I try to delete a parent I get the error: Could not update; currently locked.
Why doesn't the cascade delete work?
Update Subforms - No Parent/Child
Dec 24, 2005I have a main form with 2 subforms. Subform 1 shows the results of query 1, subform 2 shows the results of query 2.
Query 2 uses query 1 as a starting point. Query 1 is updated with an option group on the main form. In other words, when I select an option on the main form, qdef for query 1 is updated and query 2 will also be affected.
If I select an option the subforms don’t update. If I close and reopen the main form, the subforms show the correct information so the queries are being updated but the subforms are not showing the updated information.
I’ve been trying to requery the subforms using the following code in the OptionGroup_Click() event:
Forms![MainForm].[Subform1].Requery
Forms![MainForm].[Subform1].Requery
By the way, I don’t have any parent/child relationships.
Am I missing something simple.
Child Form Loads Before Parent?
Jan 26, 2006Hi,
I've got a parent/sub-form set up, both of which derive their data from linked tables connecting to a central database. As a result I've had to add coding to the NewRecord events to fetch the next primary keys from the database.
Unfortunately I'm finding that one loading the new record, the child form seems to be loading before the parent, ie the child form_Current event occurs before the parent form_Current event. Is this how it should be? It seems intuitive that the parent should load first.
I'm sure I can develop some cunning logic to get around this problem but I'm sure there must be a better way. Has anyone any experience of this parent/child set-up with linked, central database tables?
thanks!
Dave
Limiting Number Of Child Fields Per Parent
Sep 23, 2005I have a field in the parent table which indicates how many child tables there should be at the most. I'm uncertain about how to enforce this. I"m using access as the database to a Vb.net app. Should I do it in access or Vb.net or both.
View 6 Replies View RelatedQuery On Parent-Child Based Table
Oct 16, 2007Hi
I have a parent-child base table (accounts) with the following fields:
- id
- parentid
- name
every record's parentid is either 0 ( which means it is in the most upper level) or another record's id (which means it is the other record's child)
different levels of records are being used. I mean some accounts are used in the most 1st level with no childs, some in the second level, ....
I need a query to list the accounts which are not parent to any othe records, regardless of what level they are defined.
Thanks again for the help.
Duplicating Parent And Related Child Records
Dec 18, 2006I am creating a purchase request form for my company. The main form contains the PO Request # (autonumber) and general vendor and shipping information. The subform contains the item numbers on the order, the quantity and costs. What I am trying to do is make it so that users can click a "duplicate" button on the main form of an existing PO Request and have all the data replicated into a new PO Request. A lot of what we order is repetitious and it would time saving if users could just duplicate an order and modify as necessary. Can anyone help me with this one...
Thanks!:confused:
General :: Passing Values From Child To Parent?
Nov 13, 2013I have a variable tempnum on a subform which I want to pass into a textbox on the main parent form. I cant seem to get it right. Here's my code
Me.Parent.Parent![List num].Value = tempnum
How To Add New Record From A Form To All Parent And Child Tables
Jul 27, 2015I have one parent table with 6 child tables (all child tables are linked to the parent table in the relationship through the BoardID autonumber field, and they are all one-to-one relationships). All of the tables feed into one form. I have added the "add new record" button to the form, but when I click on it, it only adds a record to the parent table. The only way I have found to solve this is to go into each individual table and add a new record. Is it possible to have a new record added to every table when I click the "add new record" button on the form?
View 7 Replies View RelatedLink Parent To Child In Master And Subform
Aug 28, 2013When trying to link parent to child in master and subform, I am getting a pop-up:
Subform Field Linker
"Object variable or With block variable not set"
Forms :: Creating Parent / Child Relationship Within Form
Apr 3, 2013I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
Trouble Updating Parent Tables With Child Table/form
Jul 6, 2006Sorry this is so long but I am trying to make it as detailed as possible...
The Setup
My database is similar to the sample "Service Call" db. I have a main table called "tblTT" which has an autonumber primary key. The table also contains several foreign keys such as UserID (not an autonumber) from "tblUser", TechID (also not an autonumber) from "tblTech", etc. I have the relationships setup with "Enforced Referential Integrity" for both updating and deleting records. Each relationship has a RIGHT OUTER JOIN ("Join 3" in access) so that all records from child (tblTT) and only equal from parent (tblUser, tblTech, etc.) will be included. I have a form for nearly every table which serves different purposes but the main function of the DB is to create new Trouble Tickets (TT's), a.k.a. service calls. Therefore the main form used is my "frmTT" form in add mode. The form contains all the fields from my "tblTT" table and contains (directly) no fields from any other table (I guess indirectly it contains fields from all the parent tables...).
The Problem
When I pull up "frmTT" and try to create a new Trouble Ticket for a user that does not yet exist in the table "tblUser" I get the error "You cannot add or change a record bcause a related record is required in table 'tblUser'".
What I want is for my users (the "Techs") to be able to create a new Trouble Ticket without having to worry about populating the "tblUser" table (and other parent tables) first.
My Solution
I was going to (and unless someone can find an answer for me still will) fix this using VB script by setting up a query to check all the parent tables for the values in their corresponding fields in the form. If the query returns no results an "INSERT INTO" statement will run to populate the parents tables so that the form will save itself into the Trouble Ticket table ("tblTT").
I feel that this is a huge work around and not the proper fix. I would prefer to do this the right way both to have a correctly setup DB and for future reference. Can anyone help me with this?
Thanks,
Andy
Queries :: Parent Child Query - Sum Images In Main Record
Oct 15, 2014I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.
Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).
So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?
General :: Link Parent / Child Node To A Control On Main Form In Treeview
Oct 7, 2012I downloaded a sample treeview application I have modified most of its to suit my need, except one thing. I want link parent/child node the main form to the main form through a subform on the CategoryID.
The following is the original code where when a child not is clicked only pops up a "linked" msgbox. I need to modify the part of the code on the Select Case "C" in red:
Code:
'----------------------------
Sub DisplayForm(i As Integer)
'----------------------------
' for this Treeview I do not want a double-click on a parent node
' to open the applicable Category form, but pressing the shift key
' should open the marriage form applicable to the parent node. Shift
' or double-click on a child node should open the applicable
' offence form.
Dim strKey As String
Dim strTag As String
Dim strFilter As String
[Code] ....
Getting Data From A Parent Form
Apr 10, 2006I have been having alot of trouble with this and just can't figure it out, I am trying to get data from a parent form into a subform so I can use it in equations. Specifically I have a DOB (Date of Birth) field on the main form, and want to have a =DateDiff("yyyy",[DOB],Date()) to find their age.
I need this in the subform because of some other information that relys on it. The way I have it right now you have to manualy put the DOB in the subform, and it can be different from the value in the main form. Could any of you tell me what I am doing wrong here?
Tables :: Developing Related Multi Level Child Parent Tables?
Jan 14, 2013I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
Forms :: Selecting Record In Parent Subform From Child Continuous Subform
Jan 26, 2014I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
Forms :: Subform Control Event - Identify Parent And Child Forms
Jun 20, 2013I have a listbox on a subform (or a subform within a subform).
When it is clicked I want all other listboxes to unselect.
There may be listboxes on the mainform, on other subforms, on other subforms of subforms.
I imagine I'm looking at a recursive function of some sort, but I'm not entirely sure of syntax to identify parent and children forms...
pseudocode so far:
loop all controls
if control = listbox, unselect all
if control = subform - recurse: loop all subform controls
if control = parent... err... Fail.
Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table
Sep 8, 2014I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
Listbox Displays Data Associated With Parent Form?
Dec 23, 2006Hey guys,
I was wondering... is there a way to have a listbox display values associated with a parent form ID? in other words... say I have a customer with invoices associated with their name, I want to display a form for that customer with a listbox (or maybe even subform) containing the invoice IDs associated with their name.
Right now, I've got:
SELECT qryClientData.InvoiceID, qryClientData.InvoiceDate FROM qryClientData ORDER BY [InvoiceDate]; to display information in the listbox. How do I modify this to display ONLY Invoices associated with the Clientname on the parent form?
I hope this makes sense. I can clarify if need be, I am just completely stumped. thanks and happy holidays!
Forms :: Pre-populating A Subform With Parent Data
Jun 2, 2015I have a parent and sub form in dataset view.
What I would like to do is prepopulate the subform with data from the parent form when I create a new item of data..
i.e.
When I click the "+" button Field1 on my subform is populated by Field1 on the parent.
I have tried beforeupdate on both the subform properties and the subform.Field1 properties.
Parent Form Vanishes When I Tab To Option Group In The Child Form?!
Apr 7, 2006I have a rather annoying and frustrating problem which I cannot find a solution for through searching the forum.
The situation is that I have a parent form with 1 unbound subform, the source of which is determined by the part number selected in the parent form (diffetrent parts have different fields of the source table displayed)
This all functions perfectly well in all but one case which is when the source of the subform is a form with an option group.
There is only 1 form like this, and the problem I have is that when tabbing through the fields, as soon as I tab into the option group the parent form disappears, and I am left with just the subform on screen! Clicking into the group is fine, just tabbing to it causes the problem, and if I remove the option group then tabbing through all the rest of the fields is fine...
I could powssibly change the option group for a set of option buttons and code them to function a bit like an option group, but this would mean changing the code for numerous reports etc. and I don't really want to do that if I can help it.
Queries :: Filter Data From A Table Using Query (from Data Input Form)
Dec 30, 2014I would like to filter data from a table using a query (from an data input form). The objective is to output all results if input form field is empty and to output results higher or equal to the type in the field if field is not Null. The query code is as follows:
IIf(IsNull([Forms]![Form2]![MaxDiffInput]);[Maximum operational pressure (bar)];[Maximum operational pressure (bar)]>=[Forms]![Form2]![MaxDiffInput])
However, is not providing any result when the input field (MaxDiffInput) as a value.
Help: Pass Argument From Child Window To Parent Window?
Feb 15, 2005Hi,
I have a parent window which upon clicking on a button will pop-up a child window containing a listbox. The listbox recordsource is a subset of the parent window. I want the user to select a record from the listbox which will load the selected record onto the parent window.
How does one pass argument back from child window to parent window?
From parent window to child window, I used
docmd.openform ,,,,,,[argument] and me.openargs in the child window
thanks in advance.