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