Linking To Parent Form In Query?

Jun 17, 2005

I have a query which is run in a subform... however, I need a value from the parent form.

How do I link to this value in the parent form?

The query should look like:
SELECT tbl2.name, tbl.number
FROM tbl1 LEFT JOIN tbl2 ON tbl1.aID = tbl2.aID
WHERE tbl2.aID = parentform.[aID]

Now, the question is about the WHERE clause... how do I link to the parentform?

any help is appreciated
Jazz

View Replies


ADVERTISEMENT

General :: Linking 2 Subforms To Parent Form - Merging Duplicate Records

Aug 28, 2014

Users are too lazy to check whether a patient record exists or not. Consequently, we have a number of duplicate records that I would like to merge.

My problem is finding a way of linking 2 subforms to a parent that is not based upon a query.

On the parent form, I want two Comboboxes called 'Patient_to_Keep' and 'Patient_to_Discard'.

The two subforms called Frm_DUP_Patient1 and Frm_DUP_Patient2 should then display the data of the respective combo boxes.

Once I have this working, I can write the necessary code to compare and update the data as required.

View 5 Replies View Related

Linking A Query Name To A Form

Apr 22, 2008

Hey guys, I have a question for you. I have a query that gives me a list of names with info on each person. From the query I want to be able to click on the name of an individual and in turn have the form open up on that individuals page. Allowing me to enter more data and/or see more data on the individual. Thanks for any help you might give!!

Joe

View 2 Replies View Related

Parent & Sub Form Mix UP

Nov 3, 2006

I have a parent form and a subform linked to the parent.

My client decided that he wants one of the controls from the parent form to be placed in the subForm area. The source of the control is still in the parent table but he just wants to place it in the subForm.

I am having a hell of a time doing this......

I tried to do it by changing the recordsource of the subForm by joining the Parent and Child tables so I could have access to that field in the subform.

But this is very ineffecient and I am getting new errors coming up as a result.

Any ideas ????

Thank you

View 5 Replies View Related

Parent Form Vanishes When I Tab To Option Group In The Child Form?!

Apr 7, 2006

I 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.

View 2 Replies View Related

Getting Data From A Parent Form

Apr 10, 2006

I 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?

View 4 Replies View Related

Reference A Field On Parent Form From The Child Form

Dec 4, 2005

hi

how do i reference a field on the parent form from the child form e.g.

i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc

thanks

View 1 Replies View Related

Reflecting Changes On The SubForm To Parent Form

Sep 17, 2005

Hi,

Scenario:
Parent form: An order form (Order table) with data fields like Order ID and etc. In my Order Table, I have a revenue data field, which is a calculated field based on numbers of items in its subform (OrderDetails table).
Since the profit data field is based on the items of the Order Details, for each time the user add an records in the OrderDetails table, it need to be recalculated. Currently, I used a calculated field in the query of the OrderDetails subform and bind it back to the main form, again it bind it back to the profit data field in the Order table.

With this method, everytime user add a record in the subform, the calculated field in the query will reflect the changes accordingly, but I have difficulty to force the changes in the calculated field to update the profit data field in the Order Table. I most of the events (like On Changes, On Dirty ....) on both parent and subforms, it seems does detect the changes.

Please help ...

View 2 Replies View Related

Child Form Loads Before Parent?

Jan 26, 2006

Hi,

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

View 5 Replies View Related

Forms :: Next Record On Parent Form

Apr 18, 2014

I have a parent form tied to one table, and a subform in it that is tied to another table. The relationship is one-to-many, and if there is more than one record in the subform, when clicking next or previous, it scrolls through the subform items. I would like the Previous and Next buttons to move to the Previous or Next parent record, not the child records.

View 10 Replies View Related

General :: Add Another Parent Name From A Form To A Subform

Jun 27, 2012

I added a text box on my main form. I have to other parent names in a subform that I would like to add to my main form. I have this working just using one parent name "dtdrec" that is working. How would I add "flduser" and "fldstuff".

=[tblPayEstimates Subform].[Form]![dtdrec]

View 7 Replies View Related

Executing A Subforms Procedures From The Parent Form

Dec 19, 2005

is there a way i can call a sub forms procedure from the main parent form.

in other words i have a button command i want to execute

View 2 Replies View Related

Listbox Displays Data Associated With Parent Form?

Dec 23, 2006

Hey 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!

View 7 Replies View Related

Modules & VBA :: Use Parent Form Value In Subform Calculation

Nov 19, 2014

I have a parent form where I enter a value for tax rate. I need to use this value in the tax calculation in the subform fields i.e. subform.taxdue = subform.qty * subform.price * parent.taxrate.

I can get the value into the field. But I do not think I am putting it in the correct event. The parent form is a sales form and the subform is the sales items form. So there can be multiple items on the sub form.

View 5 Replies View Related

Forms :: Subform Needs Values From Parent Form

Jan 10, 2014

In the detail of the parent form, there is a tab control block and each has subforms on them that are exactly alike for WorkOrder Notes, History, Specs When the Work Order subform is accessed (connected by SerialNumber, WorkOrderID and CertDate) the default WorkOrderID should default from the parent form, the SerialNumber should default from the parent form, and the CertDate should also default from the parent form.

What is the syntax to get these fields to default to the values parent form BUT not specifically referencing the name of the parent (because there are 5 differnt parent forms)?

View 6 Replies View Related

How To Add New Record From A Form To All Parent And Child Tables

Jul 27, 2015

I 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 Related

Event On Parent Form After The Subform Loads?

Jan 7, 2013

I have parent form and child subform. one field in the parentform is calculated on sum of records on the childsubform when the parent form loads initially the value in the calculatedfield is 0 then it shows the correct value when the childsubform value is populated i have another-field i want to change the property of the onotherfield.backcolor= RGB(0,0,255) when calculatedfield.value<0 but its taking the initial value(0) not the calculated onewhich event shall i invoke on the form so that it waits the subform to complete then fires ... i tried current, load, activate events .. with no success.

View 2 Replies View Related

Query On Parent-Child Based Table

Oct 16, 2007

Hi
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.

View 3 Replies View Related

Forms :: Creating Parent / Child Relationship Within Form

Apr 3, 2013

I 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.

View 4 Replies View Related

Forms :: Update Parent Form After Subform Updated?

Mar 30, 2015

Parent form/table, with a subform (separate table, 1:M link on "ID") The parent form data stays fairly constant (occasional change/update), with multiple entries in the subform for each parent record which are added to more frequently The master table has a field "last interaction" which I need to update whenever a new entry is made in the subform/subtable It updates fine when I change an entry in the parent table/form, but not when I add a new subentry for that parent

View 12 Replies View Related

Forms :: Refresh / Save Field On Parent Form

Sep 4, 2013

I need to refresh listbox field (Total_price) situated on parent form. My sub form is called "Lines_form". I want to sum values of all lines for column "price". Result of calculation I want see in parent form in field "Total_price". My parent form is called "Main_form".

On "price" field settings I used AfterUpdate event : Me.Parent!Total_price.Requery

I see result in parent form, but value isn't saved to table and I can't use it in next calculations.

I need refresh/requery/save patern form. I try this code:

Me.Parent.Form.SetFocus
Me!Parent.Form!Total_price.SetFocus
DoCmd.RunCommand acCmdSaveRecord

but it doesn't work.

View 3 Replies View Related

Modules & VBA :: Can Filter Child Subform From Parent Form?

Jun 11, 2013

I 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 Related

Forms :: SQL For Combo Using Text Control From Parent Form

Jun 22, 2014

Private Sub cboConsulta_GotFocus()
Dim strsql As String
strsql = "SELECT ID_Verificacao,Verificacao FROM tblVerificacao WHERE ID_Peca = '" & Parent!TxTId_Peca & "' ORDER BY Verificacao;"
Me!CboConsulta.RowSource = strsql
End Sub

Message Box: Data type mismatch in criteria with data

field ID_Peca = number

TxtID_Peca = receives from field = number

View 2 Replies View Related

DataMacro - Calculate And Display Totals On Parent Form

Apr 16, 2012

I have a form, with a sub-form, from which a call a datamacro to calculate totals(from the same table as the control source of the sub-form) and then want to display the totals on the parent form!! The RETURNVARS all have the correct total values, but i cant assign them to a control on the parent form!! none of the controls are recognized when i try and set the PROPERTY VALUE to the totals? (spellings are definitely correct)...

View 2 Replies View Related

Trouble Updating Parent Tables With Child Table/form

Jul 6, 2006

Sorry 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

View 3 Replies View Related

Modules & VBA :: Referencing Parent Form On Requery - Error 2450

Apr 3, 2014

In short, here is what I am trying to accomplish:

1. I have a two-tier, tabbed Navigation form (Menu-Main).

2. One of the second-tier tabs opens a form (VendorMain) which displays a Datasheet based on [Query.Vendor_Query]

3. VendorMain contains a Command Button (EditButton), which calls a subform (VendorForm) used for editing.

4. Upon saving the edits, VendorForm's OnClose event is supposed to Requery the datasheet on VendorMain, so the edits are reflected when the user returns.

I have tried over a dozen different permutations of the following, but nothing seems to work:

Me!Parent.Requery
Forms!VendorMain.Requery
Forms![Vendor_Query].Requery
Forms!Main-Menu!VendorMain.Requery
Etc...

VendorForm opens, edits, saves and closes perfectly. However, I consistently get the following error and the parent form is not requeried:

Runtime Error 2450 - Cannot find the referenced form VendorMain

Code available upon request.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved