i have a total in an invoice with is a tabulated form with totals in the detail footer I want to post the calculated total (control) to a table. How ia this done.
I have a form which calculates alot of numbers. Im trying to figure out how to save the calculation to a table field. Is this possible? Can someone help me with a solution please
I am trying to use DLOOKUp and Trim in a calculated control on a form - D look up works but when i add Left trim to trim a telephone number to just the prefix - it wont work
=DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form]![Data] & "'") - Works OK =DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form],7) & "'") - appears to put square brackets around Left ??
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
In my DB i would like to make a form that displays different calculated values based on other forms and queries like income running cost etc., but i do not know how to refer in an unbound control to value of a control in another form. I get always #names? error. However it worked when loaded that form i refered to. Is there a way to do it without loading the refered form?
Hello everyone, I have been trying to calculate the number of working days a person has off sick between two dates.I was aware of how to calculate the difference between two dates using datediff, this was not quite hat i needed. after much experimentation and a little searching I came across this site that has I would recommend to other beginners to use as a query and a module.
http://www.mvps.org/access/datetime/date0006.htm
I have a calculated field that uses this function to work out the number of days, My question is, Is it worth me saving this calculated field physically in my table instead of in the query?
The reason for this is that im only with company for limited time and others will be building reports in the future and would prob prefer to build reports on tables rather than queries. If so, how can I go about setting the field value to that of the query result?
I need to make a calculated field show up on my table. My database is for technicians at work to log their hourly health checks. I have a field on form "Health Check" called "HC_ID" that I automatically bring over to the next form called "Channel_Scan_Form" with the control source property. I would like the data from this field to copy to the accompanying table called "ChannelScan_Table" from this "Channel_Scan_Form". I have tried to place the code in several different events on the property sheet but haven't had any luck bringing the data over to the table. I'm just starting to play with databases, plan on being here for a while and hopefully contributing in the future.
What I'm trying to do: I have created an unbound field within a subform's footer to calculate the average of the displayed record values. The subform is in datasheet view. The records are returned based on a query with a relationship between two tables. I need the calculated data (which I currently have displayed on the main form) to populate within the master table.
I can't figure out how to automatically do this. I created a simple command to get it there, but I'd like the user not to need a button to display a calculation.
Main form based on TableA; subform based on TableB.
Here's how I have it set up (Btw, I suck at SQL I just figure this is easiest to read):
Query SQL looks like this: SELECT tblB.Field1, tblB.Field2 FROM tblB.Field1 INNER JOIN tblA ON tblB.Field1 = tblA.Field1
Main form: unbound txtbox = [Forms]![frmA]![subfrmA].Form.[txtAvg]
The field that is averaged is tblB.Field2. There are potentially a bunch of records displayed in the subform.
I need that [txtAvg] field to save to TableA. My command button is simply Me.tblA.FieldName = Me.unboundtxtbox. I'd like to do away with that.
I tried to use the on current or on load event for the main form; but the calculated field value is 0 until it calculates it. It seems like there is a short delay before the value shows up on the form at which point the on current or on load events don't pick up the calculated value; just the 0 that is initially there.
Here is the SQL that A2K gives me for the query that has the actions I want it to perform. But I can't get it to give me a count of the number of records returned. I would love to use DCOUNT but I am having a tough time working it out. Also in the final soultion, the numbers 2 and 23 are fields that I have 2 = weekday and 23 = [weekday] + [tt] which are in a different table. If someone could help me out with the DCOUNT formula, I would be forever appreciative.
SELECT DISTINCTROW noweekend.noweekday, noweekend.dayid FROM noweekend GROUP BY noweekend.noweekday, noweekend.dayid HAVING (((noweekend.noweekday)=1) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23)) OR (((noweekend.noweekday)=7) AND ((noweekend.dayid)>=2 And (noweekend.dayid)<23));
Is it possible to use an input mask on a calculated control? I have a field which will return "Home Phone:" [HomePhone] and I'd like it to actually display the phone number as (000) 000-0000. Is there a way to do this? Thank you!
Hello, I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form. My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table. Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.
Calculation in the text box control source= [Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
I have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
Eventhough i know calculated control are for queries, I still need to store a calculated value of a sum. I have a form with customers, dates, locations. That form has a subform that contains the services done and materials used. This subform is in datasheet view. In the footer i have a control with =Sum([Price]).
So I need the value of this control to be stored in the table the main form is based on. I did an unbound control in the main form where the =Me!Subform.Form!.Textx will bring the value "over" Then for the on focus event the vba code Me.Total=Me.Textx stores the value. That is fine. The same works with a command button with the same vba code.
However i am asking if there is a more automatic way to get the value over without the unbound control/command button.
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
I have a simple application where a user clicks on the name of the person he wants to update from a drop down box.
The record is then retrieved and the fields are filled on the form. The user will then enter a value in the # in household field. The user is then required to click on the save button to write the record for this person.
I do not have strong users and they invariably forget to save the record and go on to the next person, resulting in the record not being updated.
I coded a message box as part of the save button which displays "person was successfully updated" and then instructed the user to make sure that they see this message before they go on to the next person. They still fail to update correctly.
My question:
Is there a way when the user selects a person from the drop down box and it is loaded on the form, to warn the user that they have NOT updated this person, before they go to the next person?
I don't know what else I can do with these users. They are all volunteers and elderly and the application is for a Food Pantry.
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
So I have this relatively simple problem: I need to create a button that once clicked will open the Save As dialog box and allow the user to save a copy of the current database where he wishes. The filename should contain todays date in DDMM format along with some pre-set text e.g. DDMM PresetText.
I have a few selected reports on an Access 2007 database that users can run. Is there a way for users to view the report, save as a PDF and automatically save a copy to a shared drive by modules/vba coding as an On Click event procedure?
I have a split form that's like a list of pending tasks. The data source is a linked SharePoint 2013 list where users submit requests. The user takes the information from each record and performs an action. When it is done, the user presses a button and the task status changes from "Pending" to "Processed". The form record source is based on a query that finds only records with a status of "Pending" so when the user changes the status of the task, it is removed from the list. It works fine except when there is only one task left in the work list. If the user processes the last task, the form refreshes and it goes to a new empty record and I get an error message that says I must enter a value into one of the required fields. I tried making the field non-required but it just creates an empty record in the table.
