I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
View RepliesTrying to get a total on main form from records from two subforms.
I'm coming up with Run-Time error 2450 Microsoft Access cannot find the referenced form 'tblitemlist subform'.
=([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....
In my database main form with subform. subform have query as recordsource.total of one of field in subform shown on main form. all is ok and show total correctly but when subform have no records then total field on main form shows #error. How to convert this value either into null string or zero(0).
View 2 Replies View RelatedI believe I did this before awhile ago but for some reason I keep getting an error.
I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No).
So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work.
I get Run-time Error 2427 "You enetered an experssion that has no value"
Code:
Private Sub Form_Open(Cancel As Integer)
If Me!frmSub.Form!strCom1.Value = "Read Only" Then
Me!frmSub.Form!strCom2.Visible = True
Else
Me!frmSub.Form!strCom2.Visible = False
End If
End Sub
I have a query, and I want a field that shows the current balance as of that transaction, eg:__________________________________________________ _______|__Transaction ID__|__Type_______|__Amount__|__Balance__||__1______________|__Deposit_____|__£10______|__£1 0_______||__2______________|__Deposit_____|__£30______|__£4 0_______||__3______________|__Withdrawal__|__£15______|__£2 5_______|So far, I have this:SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS BalanceFROM tblTransactionsWHERE [Account ID]=1;Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.Thanks in advance.
View 11 Replies View RelatedI have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
I need calculated field (in a query) that executes a running cummulative total for three conditions: "="&Project Name, "="&Service and "<="&Date.
I can do it in Excel using SUMIFS(), but have no clue on how its done in Access 2010 !
The "Balance" field should be the end result:
Project Service Date Amount Balance
A Welding 1/1/2012 100 100
A Welding 1/2/2012 120 220
A Wiring 1/3/2012 150 150
B Welding 1/1/2012 103 103
B Painting 1/2/2012 124 124
B Painting 1/3/2012 155 278
C Welding 1/1/2012 106 106
C Wiring 1/2/2012 127 127
C Wiring 1/3/2012 159 286
I have a table ("tbl_idq_all") with a text field for product codes ("scode"), a date field (dd/mm/yyyy) and a quantity field ("po_qty"). This table therefore holds future receipts of stock for products.
What I am having trouble doing is create a running total of [po_qty] based on [scode] and [Date].
A good example is stock code 10254. This has a quantity of 40,032 arriving 01/06/2013 and a quantity of 30,096 arriving 01/09/2013.
Therefore the running totals should read:
scode | Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 40032
10254 | 01/09/2013 | 30096 | 70128
As you can see from the attached DB I have 70128 repeated twice in the RunningTotal column.
hi
i have a stock control db. when new stock is ordered it updates 'units on orders' field, then when goods are recieved the subform field is updated and this automatically updates the 'units on hand field'.
i then have another subform for stock going in and out, i would like to be able to have the units on hand field as above (which is the main form) automatically updated like is when using the the goods recieved sub form??
not sure if i explained it properly but i can send the structure if that helps?
many thanks
Hi everyone
I use Access 2003 and have created a form with several subforms which I've placed onto a number of tabs. I'd like to have some of the most recently entered data from the subforms displayed on the main form.
For instance - if one of the subforms details the repairs to my car, in terms of date, items repaired, name of the mechanic and the cost, I'd like the most recent date of repair to be displayed on the main form. I know that I can see it be clicking the "Car Repairs" tab then scrolling down the information, but I'd like to have it displayed for easy viewing. I'm not sure if I'd need to have some VBA to do this or if it can be achieved by, for instance, creating a query to populate the appropriate textbox on the main form.
Thanks for your time and patience!
Juan
I have two subforms. They are in nice neat grids as access setup for me. Both of these forms should have similar data, based on a upc. What I need to do is turn the text of an entire row to red when there is no matching upc in the other subform. The two problems I cannot figure out is as follows.
1. How can I access the forecolor property of a text box in a subform from the main form?
2. How do I access only one row of data, in the default grid?
Any help would be greatly appriciated.
Hi All,
Im hoping someone can help a very confused newbie :confused:
While I have knowledge in using MSAccess I can honestly say I am well and truly stuck. I have searched the forum, but cannot find anything to help me out if there is anything I am sorry for re-posting. While there may be helpful responses, they mostly involve code, which I do not know.
I have a Main Form (frmPreApprovedTravel) and numerous Subforms (sfrmFlightRail, sfrmHotel, etc). I have designed my Main Form with tabs and each tab contains staff details, flight, hotel, taxi, etc information.
My problem is that when I go to the next record in my Main Form I want the Subforms (all of them) to go to the next record as well. I know this is possible but having tried to figure it out myself for the last 2 hours, but know Im still missing something!
I would appreciate any help and thank you in advance for the help and taking the time to read my problem.
I have a problem in requery function i cant requery a subform from main form.
I have a table say table1 and a tab control say tab1 and in this tab control i have four pages on each there is a subform.
I want to refresh one of these sub-forms field.
I have a main form with a combo which lists all forms in my database. There is also a button which open all the forms depending on this which I will choose from my combo.
Private Sub Command2_Click()
DoCmd.OpenForm Me.cmbforms
End Sub
Is there any way to open the forms in a subform in my main form?
My main form was working fine (I could key in data) until I added two subforms. Now the main form will not accept data, but the two sub forms do accept data. I have searched properties of all forms and queries, and I cannot find any suspects.
View 1 Replies View RelatedI have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1
WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated
FROM [OTMissing]
ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
Hello, I have 4 input field boxes in a form: "basic hours", "overtime hours", "holiday hours" and "total hours" i would like it so that when the user enters number(s) into the other field(s) the total field will calculate (add togther the other fields). So for example if the user puts "10" in the "basic hours" input field and "5" in the overtime hours" field then the "toal field" will display the number "15". i would like it so that the "total hours" field calculates the total progressively after each number in the other fields is input (ie a running total is displayed). The user will always fill in basic hours but 1 or more of the other fields can be left blank. I think i need to do some VB on the after update procedure for each field entry but not sure what the code is?, any help would be brilliant.
View 7 Replies View RelatedI am trying to link two unrelated sub forms to a main form so I am able to query data all at once and make a report that displays all this data at once. I do not know if this is possible. I will tell you to the best of my ability about what I have going on.
My main form is a shift report. The primary key is a auto number ID. The rest of the fields are date, name, shift, vehicle. etc.
The first sub form is area attendance. Field are as follows auto number ID (primary key), report ID(which comes from the main form, linked), the area, and the area attendance.
The second sub form is the event log. Fields are as follows auto number ID (primary key), report ID(which comes from the main form, linked), time in, and events.
My relationship now is simply primary key from the shift report (the autonumber) going to the first and second subforms report ID's.
Problem is I can not query two distinct subforms like this (I realized).
I have only 1 table that im working with I simply want to have a form that runs a search on one of the fields and returns the results.
This is what I have:
1 Table
ID : Autonumber
ProblemType : Text
Technician : Text
I want to create a form that has a main form with a text field that i can input a string of text and run a search on the problemtype field. And return all the results in the subform with based on keyword that was inputed in the form above. I want the results to display Problem type and Technician fields. What is the best what to do this?
I have created a database in microsoft access 2010 to show invoices for different customers in different countries. In doing so, I created using a two subforms in a main form. I have used the "country name" to link the subforms to the main form. When I enter new records into the subforms for a specific country, I realized that a duplicate record of an old record are being created in the subforms. What can I do to prevent this from happening? I tried to change the query link between the main form and subform to "invoice number" but the same problem has occurred.
I am novice to microsoft access 2010 ...
I have a main form and four subforms all of which were created from the tables without queries. The main form pulls employees personal data (never changes). The subforms pull Events attended, Lodging at the event, Family that attended the event and Employee minutia which changes per event. I'd like to have a button on the main form that changes the records in view for all of the subforms at once. This way it is easier for the users and I know that I am view all the pertainent info for event A instead of viewing event A's lodging info with event B's family info.
View 3 Replies View RelatedHi ALl,
I have a subform that accounts and adds all the expenses for a certain project (for example, total is $27,000). I would like to know how to duplicate the final cost in the subform and show it on an unbound textbox of that linked main form (the unbound textbox in the main form shows $27,000 also).
Thanks in advance, guys...
Caliboi