Forms :: Pulling Totals From Total Row Into Form Textbox?
Dec 13, 2013
The new database that I am building will be used as a point system based on attendance. This means that I will have a main form that a few users will utilize to look up an employee's score. So far, I have a combobox (cmbEmployeeDropDown) that lists the query for each employee using the following:
Row Source:
Code:
SELECT [Name] FROM MSysObjects WHERE [Type]=5 AND [Flags]=0 ORDER BY [Name];
Row Source Type: Table/Query
From this combobox selection I would like the totals from the Total row in the query to appear in textboxes on the form (txtTotalPoints, for example).
Is this possible to do or is there a better way to do this?
View Replies
ADVERTISEMENT
Dec 8, 2014
I have a textbox & button that when entered & pushed - run a query for the top values randomly depending on the value entered into the textbox.
My code is...
strSQL = "SELECT TOP " & Me.Text140.Value & " [CAN - NAME].Name, [CAN - CPT/VOUCHER].Voucher_Number, " & _
"[CAN - CPT/VOUCHER].Procedure_Code, [CAN - CPT/VOUCHER].Service_Date_From, [CAN - CPT/VOUCHER].Patient_ID, [CAN - CPT/VOUCHER].service_id, Rnd([service_id]) AS RandomNum " & _
"FROM [CAN - CPT/VOUCHER], [CAN - NAME] WHERE [CAN - CPT/VOUCHER].Service_Date_From Between " & Me.StartDate.Value & " And " & Me.EndDate.Value & " ORDER BY Rnd([service_id]) DESC "
It runs like it should but doesn't populate any records...that is until I go into the design view, deselect one of the fields and then reselect the same one - only then does it populate.
Do I need to open the query first and then update/append instead?
View 5 Replies
View Related
Jun 22, 2006
I've got a form for a volunteer tracking database I am trying to create that has all the voluneer's infomation and has a subform in it that shows the volunteer's activies and hours. I have a text box (txtHours) on the subform with the following Control Source "=Sum([hours])", with the visible property set to no. This sums the total hours volunteered by the individual.
On the main form I have another text box (txtTotal) with the following control source "=Hours.Form!txtHours" where:
Hours is the Name of the subform and the Form!txtHours references the control on the subform.
I am trying to create another textbox that will display the total hours for all records in the form, but not having any success.
Any suggestions?
View 4 Replies
View Related
Jan 18, 2015
I have a timesheet table (tblHourEnter) which has a staff field (Staff_ID), week number field (Week_No) and hours field (Hours_Worked).
The data is entered through a form (frmHourEnter) which has a staff textbox (Staff_ID), week number textbox (Week_No) and hours textbox (Hours_Worked).
I wish to add another textbox (HrsTotal) that would show the sum of all the hours (from the table) which is dependent upon both the Staff_ID and Week_No entries showing on the current form. I.e. the sum of hours is restricted to both staff member and week number showing on frmHourEnter.
This total can be generated from a command button.
View 4 Replies
View Related
Mar 8, 2013
I have 2 table as follow.
Productmaster
-Productname
-Opening date
Transaction
-Productname (linked with master productname)
-qtyin
-qtyout
I am working on Form transaction1 linked with table(transaction) on which I have combobox therein I have name of Products which are coming from PRODUCTMASTER.PRODUCTNAME. everything going well all data commiting accurately in Transaction table.
Now, What I want when I select comboxbox value i.e. productname then sum of qtyin+qtyout from table(transaction) should come into textbox(total) as according to the productname selected
View 9 Replies
View Related
May 17, 2013
I currently have two froms, "add record" and "add record cont." The reason I have two seperate forms is because when clients create a new record information needs to be saved to two different tables and when creating one from with fields from both tables I ran into many problems. The two tables are named : tblMain, tblFileLoc Currently there is a textbox on both forms named "fileID" the FileID in the first form is from tblMain and is the primary key for that table, the FileID on the second form "Add Record Cont." is just a normal field. When clients enter in the new FileID in the first form "Add Record" and then move onto the next form "Add Record Cont." i need access to bring the entered FileID from the first form and Fill it in the FileID field in the second form. Currently I have tried making the control source for the textbox on the second form = the textbox on the first form but it brought up an error.
View 1 Replies
View Related
Apr 17, 2015
How to update unbound textbox on main form from unbound textbox in subform afterupdate.
that is when amount paid is updated it automatically updates total paid, balance etc.
View 2 Replies
View Related
Dec 26, 2014
I have a table/form [tblStdAdv] [frmStdAdv] that contains all advisers a student had during their program. that form pulls from refAdv which has:
Type
1- initial
2- current
3- previous
and then an Adviser field, per record
I need to pull the adviser onto the student's main page [tblStdInfo][frmStdInfo] either "current" (if they have had changes) or "initial" (if they went through without an adviser change) .
I would like to pull the info into the main page so that I only have to change, or add to, the one form able and the main form stays up to date.
i tried using a query and was able to pull either "2" or "1" or "both" but could not make it look for "2" Current first and if not found then use "1" Initial.
View 2 Replies
View Related
Apr 25, 2014
I have several (about 10-15) queries that I have designed to run monthly reports. Various pieces of the criteria for each query need to change regularly. For example, in design view, I currently have a 'Where' field for each of our eight products. Where 1= include the product in the query analysis. Where 0= do not include the product in the query analysis. We would like to have a form that has entry boxes for each product where the person doing the analysis could type '1' or '0' and then the query could update the criteria entered before running.
We also need dates to update via the form. I currently have [Current Period End Date] and [Prior Period End Date] pop up boxes on each query that allow the person running them to enter their desired dates. But again, my boss wants to limit their involvement with the actual queries. Plus, you end up entering the dates for all 10-15 queries, which is a hassle. We would like to have entry boxes for the desired dates in the form and have the queries pull date criteria from the form.
If we can pull query criteria directly from a form, is it possible to create a table from a form and then pull the query criteria from the table?
View 2 Replies
View Related
Jul 18, 2013
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
View 4 Replies
View Related
Jan 24, 2015
I have a database for billing. In my database, I have a form that consists of a main form "Order" and 2 subforms "OrderDetails" and "Customer" OrderDetails are to enter the products to be connected to the Order. All function super, but I want to have some information from one of the forms "copied" over to on of the others.
Here is what I would like
In the subform "OrderDetails" I have made a textbox that summarize all prices to a total, his tekstbox i called "Tekst31". I would like the amount in this textbox to appear in a field "Bel�b" in the main form "Order".
I have tried some different commands, but nothing has worked, also I have made a query which dose the same ting as the tekstboks, as the information in that tekstbox it not stored anywhere.
View 2 Replies
View Related
Sep 21, 2014
I have a contributor tracking table that is linked to a form of the same name. I created a make table from a query that calculates the total to date for each contributor (based on their contributor ID in the tracking table). I want to place this sum to date, in read only mode, on each contribution record for each contributor in the tracking table and on each master record in another table with the contact information for each contributor.
The contact table is in the one and the contributor tracking table is the many. If this isn't clear, I can upload the database. I essentially want to link a field from one table to a form with a different table source. The sum to date should only show for the record with a matching contributor ID.
View 9 Replies
View Related
Jul 17, 2013
I'm trying to update a record in a table, from a query that is run as part of an event from a command button on a form.
I have a table called 'Assets', a table called 'Disposals', and a form called 'Disposal Entry'. I would like the user to select an Asset ID from a combobox on the form, then when the button is clicked it adds a record to the 'Disposals' table, and updates the Status for that specific Asset in the 'Assets' table to "Disposed".
It adds to the Disposal table fine, but I can't get it to update the Asset table.
My query looks like:
Field: Status Asset ID
Table: Assets Assets
Update To: "Disposed"
Criteria: [Forms]![Disposals Entry]![Asset ID]
I've checked the spelling and everything looks ok.
The [Asset ID] control on the form is bound to the Assets table. If I edit the control and clear out what is in Control Source, then it updates the table and works fine.
However, I want to keep it bound as I have a subform on my home page showing the latest disposals.
How I can get the query to use the Asset ID on the form as the Criteria?
View 14 Replies
View Related
Feb 5, 2014
I am having trouble on the Work Orders Form to get the SubTotal to calculate correctly.
The SubTotal Control Source is:
Code:
=DLookUp("[Services Total]","[Services Total]","[WOrderID] = '" & [txtWOrderID] & "'")+DLookUp("[Parts Total]","[Parts Total]","[WOrderID] = '" & [txtWOrderID] & "'")
The Form Record Source is:
Code:
SELECT DISTINCT [Work Order].*, [Payment Total].[Payment Total], [Services Total].[Services Total], [Parts Total].[Parts Total] FROM (([Work Order] LEFT JOIN [Parts Total] ON [Work Order].WOrderID = [Parts Total].WOrderID) LEFT JOIN [Payment Total] ON [Work Order].WOrderID = [Payment Total].WOrderID) LEFT JOIN [Services Total] ON [Work Order].WOrderID = [Services Total].WOrderID;
why my form won't calculate totals?
View 2 Replies
View Related
Apr 30, 2013
I have created a form to input invoice details with a sub form to input the products with the prices etc.
This all works fine but now I want to create totals such as Total Goods, Total VAT and Invoice Total.
After many searches of the internet, I found out how to do this and put in the calculations which works fine and displays on the main form without a problem.
Now I have a problem as these need to be saved to one of my tables.
As these are unbound text boxes with only the calculation on, the data is not being recorded onto the form which is what I want.
Do I have to create a macro of some sort to do this or?
I have included a few images of what I have done but if you would like the database file I can upload that (I will take away all the information that is sensitive to me if that is okay?)
View 4 Replies
View Related
Mar 22, 2013
I have converted an old Access 97 database to Access 2010. Mostly it works fine but I have a major issue with the invoicing forms. It was working in the old database but I cannot get it to work in this version.
I have the usual invoicing option where the lines of the invoice are displayed in a sub form for that customer and line totals calculated. This works fine. I have a sub form total text box in the footer of the sub form which I want to pick up from the main form so that I can add the delivery charge and VAT.
View 6 Replies
View Related
Feb 13, 2015
I have a music database. It's somewhat over the top in terms of the information stored in it, but I like it. My issue is with getting the total number of tracks, as well as the total duration, for a given album (and for each disc, but if I can solve one that should also solve the other).
I have a lot of tables and forms, but for this problem I only need to deal with two of each. They are the tables for albums, the table for tracks, and their associated forms.
In my album table I have information such as album title, artist, release date, and album length. The tracks table contains information on each song - track title, track number, parent album, and track length.
My main form is for the album itself, and contains a subform for the tracks on said album. They correspond to the relevant tables.
What I want to do is obtain the value of the album length by calculating the sum of the track lengths for that album, and the number of tracks. Easy enough - Sum(TrackLength) and Count(TrackID) respectively, both of which can be stored in the footer of the subform.
My problem is that, rather than simply copying those values into text boxes on the main form, I want to copy them into actual fields in the album table. Furthermore, I want them to update as I add or edit the track lengths in the subform.
View 4 Replies
View Related
Aug 11, 2014
I have a database that tracks workorders and then the associated invoices that result froma workorder. The process is that the user creates a workorder and when an invoice related to the workorder comes in, they register the invoice and select the workorder it belongs to.I have a form (DFRM_PayrollWorkorders) that allows the user to display the workorder. This form contains a sub form DFRM_PayrollInvoices_Summary that displays any invoices that have been registered against the workorder id. Sample screen attached. In the sub form, I have added two expressions ,
=Count([InvoiceNumber]) and =Sum([InvoiceValueTotal]).
In the work order form I have added two expressions, =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count] and =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Sum].
The above simply displays the total invoice count and total invoice amount for any invoices associated with the work order being dsiplayed on screen.
My issue is, whenever there are no invoices for the work order, I get #Error in the =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Count] and =[DTBL_PayrollInvoices_Summary].[Form]![Invoice_Sum] expressions.
Ideally, I'd just like the count and sum expressions to show blank or 0.
View 2 Replies
View Related
Jul 1, 2015
I have a continious form with textfields as records. Its about total course hours that are calculated. There is a field in the records that holds and calculates the hourse that one have done and another field that holds the max hours they may follow for one year.
Every record has h different year.
When the amount of hours in the transmission textfield is negative, they can transmit the hours to te next year. Therefore i have to calculate the Max hours per Year + the transmission hours.
So the calculation is not a problem. Though i don't actually know where i can trigger the calculation. I have tried it with a button on the form, but then the user always have to click to see the right amount of hours for the next year. That's not handy. I am looking for a way to automatically calculate the field Max hours per year. Given that the Max hours per year is a bound field, its bound to a table where i stock the Max hours that i have calculated.
I also tried it with the button that's used for inserting new course hours, but then it calculates with the old values instead of the new ones.
How can i trigger the calculation automatically without using a button.
View 1 Replies
View Related
Aug 8, 2013
I am creating a database to track gifts to employees. The admin person will fill out a form for each gift and create a report with a total $ value for those gifts for the history of the employee.
I would like to create a box on the form that totals all of the $'s for the employee selected, so the admin can see what has been gifted before they start the next entry. They will select the employee from a drop-down list (Physician_ID) and when that selection is made, I would like to populate the Total_to_Date box on the form with a total of $ gifted for that employee from the NMC_Detail_Records table.
View 5 Replies
View Related
Aug 27, 2014
I have a query that has a total sum , my problem is when i create form all the fields in the query is visible in the form but my total sum doesnt show up, but when i view it in query it works properly.
I don't really know what the symbol really is called i just called it total sum, the E icon in query ....
View 7 Replies
View Related
May 4, 2015
I have three tables (Group, Customer, Savings).
Every member of a group do savings once every 15 days and each group has maximum 20 members, group has one to many relationship with customer and customer having one to many relationship with Savings (there is no direct relationship between group and savings) i have created a continuous form for saving entry. in this form i select a group which is unbound and based on that group, its members are listed in the combo box named CusID, after selecting customer other saving details are entered.
What I want, is that for every group i want a total saving for all that specific group's members selected in the mentioned unbound combo box in that continuous form, this total should be based on the group selected in the mentioned unbound combo box, in case i select another group the total should also be based on newly selected group.
View 12 Replies
View Related
Jul 8, 2013
I am trying to create a time sheet that enables my workers to fill out separate work orders and the hours from those Work orders are auto transferred to a sub form within their daily time sheet. I have gotten all this done and I have the total hours transferring to the main form but I need to use that transferred number in a new sum on the main form. I know that this cannot be easily done just by using the expression builder.
View 10 Replies
View Related
Jul 15, 2015
=([frmservices_2013].[Form]![sub2013])
I have this expression in a field on a form. It's been a really long time since I worked in Access. Is sub2013 a field on a form, in a table?
It's doing a running total of a field from a form. I can't find a subform with this name, or a field anywhere, I'm drawing a blank.
using Access 2007....
View 4 Replies
View Related
Jan 8, 2014
I am looking for displaying the totals from a query onto a form.
I have a query which has a total line showing a count of the number of entry's in each column. I would like to be able to display these totals on one of my forms in text boxes (or any other way). Is this possible and if so how do I do it ?
View 3 Replies
View Related
Jun 16, 2015
I wanted to take the values from the frmInvoiveItems' [LineTotal] field, subtotal all of them for a given invoice ([InvoiceApplied]), and place them in a new field under frmInvoiceInfo that I haven't created yet.
(I.E. I have three different items under frmInvoiceItems for one invoice. They all have a different price. I want to add all of their prices together, and display them in a "Total" field in the frmInvoiceInfo table. This new "Total" field should be the total amount due minus the "Deposit" value and anything in tblTransactions [I still have to figure out how to assemble that too... another question for another time]).
Is all of this possible without me having to store the information in a query? How?
View 11 Replies
View Related
