Modules & VBA :: Subform Calculations Based On Parent-form Data?

Jul 17, 2013

I have a form with a subform in a navigation menu. Something like:

Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)

I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).

My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.

Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N
SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)

So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.

View Replies


ADVERTISEMENT

Forms :: Fill Datasheet Subform Based On Selection In Parent Form?

Dec 6, 2013

What I have is a form that takes in information regarding test data. Each test run can record data for multiple requirements. I am able to pull all data fine, however in order to make it easier on the user i was hoping to populate the requirements subform based on a selection of "test group" in the main form.

Commonly run together requirements can fall in to groups. I have a selection box for these groups in the main form and a table that stores these group id's and group setup. Is there a way to autofill the tables records that the subform is linked to based on the selected test group?

View 3 Replies View Related

Forms :: Delete Records In Subform Based On Check Box In Parent Form

Mar 1, 2015

I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.

What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.

View 5 Replies View Related

Forms :: Auto Populate Subform Based On Selection In Parent Form

Apr 30, 2015

I've got a Parent form (frmProspectDetails) with a subform linked (fsubProspectSkill).

This form is for users to enter general information on a prospect (names, position, height, weight, etc) on the parent form and skill levels on the subform.

Currently the subform is set so the user has to manually select the specific skill set for the prospects position, and then enter a value for each skill level(1-99).

I'm trying to get the subform to auto populate with a specific skill set based on the position selected in the parent form, so the user only needs to input the skill level without selecting each specific skill.

This data is then stored in a table (tblProspectSkill)

To add, I've created a form (frmPosition) that lists the specific skills set for each position.

I'm not sure how to get this subform (fsubProspectSkill) to auto populate with the specified skill set from frmPosition.

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

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

Calculations On Main Form Do Not Reflect Calculations On Subform

Sep 28, 2015

I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:

=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]

#Error results when the doc is put into a form mode.

Now when I input each value in the equation above separately.I still get no entry.

For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.

CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.

View 14 Replies View Related

Forms :: Pre-populating A Subform With Parent Data

Jun 2, 2015

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

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

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

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

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

Modules & VBA :: Populate Data Sheet Subform Based On Combo Box Selection ALL

Aug 20, 2013

I have an unbound combobox on a form that is used to filter the records of a subform based on who they have been assigned to. The combo box is based off of a query to the personnel table and utilizes a UNION querry to add the option "**ALL**" with a id number "111111" as the first option in the combo box.

In other words: the combo box has a list of names to choose from and the word "ALL" at the top as the first selection. The idea is that when "**ALL**" is selected, the subform should display all records regardless of who they have been assigned to. I'm trying to accomplish this with VBA. I've started developing the code to try to impliment this, however currently when "**ALL**" is selected, I'm getting a datatype missmatch that seems to be caused by my use of recordset.

Option Compare Database
Option Explicit
Private Sub cboTaskListName_AfterUpdate()
'On Error GoTo cboTaskListName_AfterUpdate_Err
Me.Refresh

[code]...

Also once the queried records are saved into the record set, how can I communicate that to the subform so that it displays them?

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

Modules & VBA :: Take Data From One Table And Insert Into Another Whilst Doing Calculations On Data

May 2, 2014

I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.

However I can't get past this:

Code:
Private Sub Test_Click()
Dim strSQL As String

strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL

End Sub

It keeps saying "Run-time error '3292': Syntax error in field definition.

View 4 Replies View Related

Forms :: Refresh Parent Form And Move To Next Record On Subform

Dec 10, 2013

I have two subforms on a "Page"/Tabs. Subform #1 is a continuous form which accepts data about dimensions of a pattern piece. The other subform needs to update its information display each time the last control of the first subform loses focus, because it tells me how much material is required for all pieces of this pattern that are entered.

My first feeble attempt at this was to add the following code to the "Lost Focus" event of the final control on the first subform:
Form.Parent.Refresh

This succeeded in forcing the second subform to re-run its calculation.

However, after the recalculation took place, I wasn't able to move to the next record on the first subform. This subform is a continuous form, and I need to be able to add another row of data and then see the second subform change based on the additional information in the first subform.

Obviously I've done this wrong.

View 1 Replies View Related

Modules & VBA :: Open Form Based On Subform ID?

Mar 7, 2014

I have a form (frmSearch), based on (I think) John Big Bootys code. On it, a search box, which filters results as you type. Works great.

Instead of presenting the search results in a listbox, I wanted it in a subform (sfmSearchresults). Also works great.

Problem was this: I wanted a button on this form, to open another form based on [CustID]. For a listbox, I could use columns(0), but not for a subform. After much trying I got this to work:

Code:
Private Sub cmdVisaPren_Click()
Dim strWhere As String
strWhere = "[CustID] = " & Me.sfmSearchResults.Form.Controls.Item(5)
DoCmd.OpenForm "frmAddCust", acNormal, , strWhere, acFormEdit, acWindowNormal
End Sub

The part that I don't understand is the Item(5). The subform is based on a query (qrySearch), from tblCustomers. tblCustomers fields is as follows:

CustID, FirstName,LastName,Adress1,Newspaper1,Newspaper2.

Item(0) in strWhere would return Adress1 as [CustID].
Item (1) = Newspaper1, Item(2) = Newspaper2, Item(3) = FirstName, Item(4)=LastName, and (finally) Item(5)=CustID.

I don't get the logic. With listboxes, I could just count columns for columns().

Have attached my database. It's just a test database, with 4 people. The final database will have about 1200 people, and many more fields. Its .accdb (Access 2013). I tried converting to Access 2002-2003, but couldn't get it to work.

View 4 Replies View Related

Calculations Based Columns In Entry Form

Jun 8, 2012

I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.

I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)

The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.

I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.

View 8 Replies View Related

Subform Is Populated Based On Form Data?

Mar 22, 2006

** Additional Note: Could I possibly just have a field in my main form that pulls data from another table? Warning: bad with code**

I'm not sure I'm looking at this correctly and have tried to figure it out (For about two weeks now). I really hope someone can help with this.

I have to add a "Multiplier" to my form. I have tried this numerous ways but it's not working properly.

What I need is to have a main form with a subform only displaying the relevant field (that being if this month/year and location match up between both the subform table and the main form that is displayed, then show this value in the subform on the main form). With this multiplier displayed, another field in the main form is then multiplied by this value.

Is this the right way to do this or no? I'm open to basically anything. It's just that the 'multiplier' changes from month to month and rather then have people manually enter in the multiplier to the main form for every day of the month, I'd rather a field be automatically populated and therefore the other field automatically multiplying by this.

Is this possible??

View 1 Replies View Related

Populate Form And Subform With Data Based On Search ..

Mar 2, 2007

Hi,

I have a form with a subform, Both of them are based on a query.the form

displays a productid and its details ,and the subform has matching

productnames for that productid.

Now i have a search on the main form wherein the user enters ( search

for) the productid, once they click search button, the form should show

that particular prod id and details and subform loaded accordingly with

that productid matches...seems simple but how do i code this in VBA??

Basically populating the form and subform with data based on search..

i am using ODBC coonxn with backend oracle tables..

any help would be highly appreciated!

Thanks

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

Forms :: Check / Uncheck Checkbox On Parent Form If Records Exist Or Not In Subform

Jul 23, 2015

I have a parent form which has a yes/no checkbox in the form's record source. Then in that parent form I have a sub-form. If NO records exist in the sub-form I want the checkbox to be UN checked. If records DO exist in the sub-form, I want the checkbox to be CHECKED.

But I want this to happen as records are added or deleted from the sub-form. In other words, if the parent form is opened and no records exist in the subform then the checkbox should be unchecked. But as soon as the first record has been entered in the subform, the checkbox on the parent form should be checked. Likewise, as soon as the last record has been revoved from the sub-form, the checkbox on the parent form should be unchecked.

What code do I need to accomplish all of this?

View 1 Replies View Related

Modules & VBA :: Filter Subform In A Form Based On Date Range

Jul 26, 2015

So I have a search form that I created that has also a subform (a continous form, that has a query as its source) where my records are listed. I started on making a Between dates filter where i would be able to pick dates and get the results of records that are in that range.

I put two text boxes in which I set format to Short Date so that I get the button to select the date from a calendar for each text box. Then I made a button that is only for this and added a Clickon code so that it filters my subform over the recordsource of the form. The problem is is that i get an error and it doesn't filter.

Code:

Private Sub btnDatumUpis_Click()
Dim SQL As String
SQL = "SELECT qrySearchV.VID, qrySearchV.MarkVoz, qrySearchV.ModelVoz, " _
& "qrySearchV.TipMot, qrySearchV.Regist, qrySearchV.VlaVoz, " _
& "qrySearchV.KorVoz, qrySearchV.KatV, qrySearchV.DatumUVoz, " _

[Code] ....

View 5 Replies View Related

Display Data On Subform Based On Comboboxes In Main Form?

Jun 25, 2006

Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.

Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.

View 6 Replies View Related

Forms :: Based On Main Form ID Subform Data Should Be Shown

Mar 19, 2013

I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...

View 6 Replies View Related

Reports :: Button On Form To Generate Report With Parent Info As Header And Items In Subform As Detail

Jun 18, 2013

I have a main form (Parent) along with a subform(Children). I want to have a button that generates a report with the Parent information as a header and the items in the subform as details. In addition, I want the report to show only the children that were recently added not all of the children.

View 1 Replies View Related







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