Text Boxes Control Source = Calculation Can't Save Sum

Oct 7, 2005

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.

Thanks--Any help will be greatly appreciated.

View Replies


ADVERTISEMENT

Forms :: Cascading Combo Boxes Don't Show Control Source Value Until Requery

Dec 31, 2013

I have a set of cascading combo boxes. When a value is selected in Cat 1 ID, it narrows down the selections in Cat 2 ID which narrows down the selections in Cat 3 ID.

However, the values don't show up for columns Cat 2 ID or Cat 3 ID until I enter the row. Then I have it set to requery the fields because the underlying data isn't populated. But then when I leave the row, the values go away.

Why doesn't it show the underlying value from the table? Each column is bound to a field in a table - I would assume that the recorded value would show up. Did I miss something easy?

I've attached a picture of exactly what I mean.

View 2 Replies View Related

Text Box Control Source???

Jun 20, 2006

Hi everyone,

This is a very simple problem, and it seems to me like it should work but it doesn't. On my form, I have a field called [mindate], which looks up the earliest date from a table. I also have a field called [maxdate] which looks up the latest date in my table. These both work great. My third text box [difference] is supposed to find the difference between the two above dates in days, but it doesn't work.:eek: The control source of this [difference] text box is this:

=DateDiff('d',[mindate],[maxdate])

Any suggestions?
Chris

View 4 Replies View Related

Access Control Source For Text Box

Apr 8, 2007

hi,

im trying to add a text box for entering a date into the table database for my form but i cannot seem to connect the control source to the text box, the name of the box/script is not appearing in the control source list. I have already made a column in my table for this box on the form but just cannot connect the two properly?

Any quick thoughts on this?

Thanks.
Trevor.

View 2 Replies View Related

Forms :: Text Box Control Source

Jul 23, 2015

I'm trying to create an Expression as Control Source for a Text Box.An example which can be found is:

Code:
=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)

In above example the criteria is based on [ProductID] on a form.Is it possible to change it, so criteria is based on a value from a specific query, i.e. similar as above?I have tried something like this, but it doesn't work:

Code:
..... & Query![QRYORDER]!ProductID)

View 2 Replies View Related

DSum In Text Box Control Source

Oct 26, 2012

I have the following tables and am trying to populate a text box on a form, and store that value in a field

TotalSegLengths

tbl_Segments
LineNum SegLength
1 3
2 9
2 10

tbl_Lines
LineNum TotalSegLengths
1
2
3

So when I create a new record in tbl_Lines from the form (Form is bound to tbl_Lines) it should query the tbl_Segments, add up the SegLengths for the corresponding LineNum and store that value in TotalSegLengths.

So for example, with the data as per above, from the form, create a new record with LineNum = 2 and it should display 19 in a text box and store 19 in the TotalSegLengths field.

I was trying to use DSum in the text box Control Source but was just returning an #Error in the text box.

View 1 Replies View Related

Forms :: Using Unbound Text Boxes And Calculation

Nov 12, 2014

Got a database that is working great

1 table - Employees
2 Queries
1) - QEmployees Extended
2) - QyrsEmp

2 Forms
1) - Employee Details (source control is "QEmployees Extended")
2) - Employee List (source control is "QEmployees Extended")

in Form "Employee Details" there is field named "HireDate" this field is also found in the table (records) "Employees" and "QEmployees Extended".When Form "Employee Details" is ran the "HireDate" appears as it should in the "HireDate" text box.

What I want to do is add a unbound text box to Form "Employee Details" named "txtYrsEmp" (Years Employed). and perform a calculation that will take the "HireDate" (date) and compare it to "Todays Date" and come up with the number of years employed rounded by 2 and show this result in the unbound text box called "txtYrsEmp"

Now I did a Qurey just to see if I could calculate what I wanted- "QYrsEmp" where in the first column I entered in the Field row (top) Expr1: EmployeeID from table Employee and in the second column I entered in the Field row (top) txtYrsEmp: Round(DateDiff("d"'[HireDate],NOW()/365.25,2))

Now the query returns the exact results I want so I know the calculation is possible at least here in the query.

View 7 Replies View Related

Modules & VBA :: Text Control Source Using CASE

Feb 2, 2014

I have two queries called "Query_match_AND" and "Query_match_OR". I want to show the total number of records for either of these queries in a form called "form_candidates_result" depending upon the combo box value (either "AND" or "OR") in a form called "form_match".If I put

Code:

=DCount("*","Query_match_AND")

in the control source of [candidatecount] in "form_candidates_result", the value is shown in the form and it works fine.If I use the following code in either of the "on load" or "on open" events in "form_candidates_result"...

Code:

Select Case [Forms]![form_match]![ANDOR]

Case "AND"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_AND")
Case "OR"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_OR")
End Select

it doesn't work and I get

Code:

#NAME?

View 7 Replies View Related

Forms :: Setting Control Source Of Text Box

Jul 7, 2014

I have an unbound form which has 2 unbound combo boxes on it, both based on the same table, but I have used a query for each one to created the sort order, one alphabetically by item name (cmbA) and the other numerically by the item number (cmbN) . I also have a text field (ID) upon which is based a report when the user clicks a button. Currently the text box (ID) has a control source of column 1 of cmbA and I have the report working perfectly if one selects from the name combo box (cmbA) What I want to do is allow the user to select from cmbN and have the control source of the ID text box equal the number the selected so the report button will work then also.

I have tried to set the control source after update of cmbN but all that does is blank out the ID text box. I have not done this before so I am sure I have done it incorrectly. I tried again this morning using me.ID.ControlSource = Me.cmbN and stepping through the code shows that the cmbN ControlSource is equal to ID but I can't make the code that follows use the results.

View 8 Replies View Related

Reports :: Text Box Control Source With Conditions

Dec 24, 2013

I built an expression for the control source in Access 2007 report as follows:

=Sum([Weighted Value])/(Sum([CourseInfo.Credit])-Sum(IIf([StudentClassRecord].[Grade]="P",[CourseInfo].[Credit],0)))

This is to calculate the GPA. However, sometimes the denominator can become 0 and an error us returned. I would like to get "NA" in return instead.

All the field names are derived from table or query. I tried to aggregate the IIF function but ran into error message.

View 3 Replies View Related

Forms :: DLookUp In Text Box Control Source

Feb 7, 2014

I currently have a DLookUp formula in a Text Box Control Source. It returns the value I am looking for, however, the value will not add to the record. I have tried putting the DLookUp formula in the Default Property, but it is not working.It is a multi-criteria DLookUp, where the criteria is entered into the form.

=DLookUp("[TBA CUSIPs]","[TBA CUSIPs]","[Settlement Month]=" & [Forms]![Enter Trade]![Settlement Month Text Box] & " AND [Coupon]=" & [Forms]![Enter Trade]![Coupon Text Box] & " AND [Instrument] = '" & [Forms]![Enter Trade]![Instrument Combo Box] & "'")

View 3 Replies View Related

Forms :: Criteria For Text Box With Control Source?

Mar 19, 2013

My form is based on a table (SOP TBL) and has several text boxes with control sources of the records in the table. I need to add a criteria to one field (SOP Number) so that only current versions of the number show up. I have the SQL for a query that I use in another form for a combo box as shown below. What this criteria does is find the version number which is the last 2 digits of the SOP Number and displays only that record for the SOP Number in the combo box. How can I apply this code to the text box with the control source directly from the table so only the current records are displayed on the form?

Code:
In (select max([SOP Number]) from [SOP TBL] group by left([SOP Number], len([SOP Number]) - 2))

View 1 Replies View Related

Forms :: Vba Assign Text Box Control Source

May 30, 2013

I am working with textbox [DispCD] (a date field) on form [Contract]. There is a dropdown on combobox [Searchbox] for selecting the desired record. I am writing VBA in the [Searchbox] OnChange event. The control source for [DispCD] would normally be "=Searchbox.column(11)". If "column(11)" has a date, I want [DispCD] to show that date and then be locked. If "column(11)" does NOT have a date, I want [DispCD] to allow entry of a date with the date picker showing. I've tried to assign the control source to [DispCD] with this:

CSVal = Format(Forms![Contract]!SearchBox.Column(11), "ddd m/d/yyyy") (this works)
If CSVal = "" Then _
Forms![Contract]!DispCD.countrolsource = "=SearchBox.Column(11)"

I get "Object doesn't support this property or method" error.

View 4 Replies View Related

Forms :: Two Text Boxes / Save The First Two Letters Of Each Input?

Jan 10, 2014

I want to be able to have two text boxes on my form that firstly concatenate the values inputted and then saves this string to a field.

It's a "new record" form that generates a new record in table "tblUsers".

txtB1 & txtB2 and the text boxes would represent First Name and Last Name.

But it can't be allowed to be saved as the acctual names. - Insead I'm looking for a way to take the first two or three letters of the name and concatenate these into one string of text and save that string to the field tblUsers.ConcatenatedName.

I.E.txtB1:"Carl"
txtB2:"Chapelle"

would generate "CarCha" and save that from the form to the field ConcatenatedName in tblUsers.

Btw, the values form txtB1 and txtB2 (Carl & Chapelle) cant be saved anywhere either!

Found the answer myself;

create a txtB3

And set that source to Left([txtB1];3) & " " & Left([txtB2];3)

View 1 Replies View Related

Forms :: Can A Query Be Used As Control Source For Text Box On A Form

Jun 7, 2013

Can a query be used as a control source for a text box on a form?

=[QueryLibrary]![Kit] is the control source do I need to change anything else?

View 3 Replies View Related

DLookup In Control Source Of Text Box Only Returns Value When Clicked On

Apr 23, 2015

I have 3 text boxes on a form each of which gets their values from different queries using DLookup this is functional but only when I click on the text box its self, I have tried using the "On load" & "On current" to requery the textbox (Text38 as control name) but no luck,, another strange thing i've noticed, when I have the form open in access and i click on my taskbar, thus switching windows from access to the desktop but keeping access in view, all text boxes are calculated immediately.

View 2 Replies View Related

Forms :: Select Query Error In Control Source Of A Text Box

Jul 16, 2015

is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.

I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.

R_P_Data_P = Table
approvalNosys = Field in the table
status = Field in the table
score = Field in the table
cmrOverview = Form
[approvalNoSys] = field in form
1 is the criteria

Code:
SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore
FROM R_P_Data_P
GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status
HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));

View 7 Replies View Related

Forms :: Save Values Entered Into Unbound Text Boxes To Fields In Table

Mar 6, 2013

how can i to save the values entered into unbound text boxes to fields in table

View 2 Replies View Related

Forms :: VBA Calendar Control With Two Text Boxes

May 14, 2013

I want the calendar to show the date in the field [Next Payment Due] - whenever this value changes, the calendar date should change accordingly.

I want the calendar to be assigned to a text box (txtCalDate), so this would also contain the date of [Next Payment Due].

However, If the calendar date is changed (on the calendar), I want txtCalDate to show this date, but [Next Payment Due] to stay as it's original value. Next Payment Due can only change the value, but not be changed itself.

So I presume VBA would be needed to say when [Next Payment Due] is changed, make calendar this date. And then to simply assign the calendar to txtCalDate so if calendar is changed, this will change with the new date.

View 3 Replies View Related

Forms :: Unbound Text Box Calculation Using Two Other Unbound Text Boxes

Jul 25, 2013

I am running in to a brick wall with this. I have an unbound text box with the control source set to =IIF([text42]=0,0,[text42]/[text44])*100 and in continues to return a #name? error.

I am not sure how to get this expression to work. I have even tried to put =[text42]/[text44] and I still get the #name? error.

View 3 Replies View Related

Control Array In Access / Text Boxes As Objects

Apr 13, 2005

Hi,

Is it possible to simulate a control array in MS Access?

I’m using a restricted version of Access 2000. I can use all normal features but some Active X controls are not licensed and it will not be possible to licence them in time I have available for my project.

I have a form which has 14 combo boxes and 24 text boxes to allow user to choose shift type and enter start and end times of shift. I know in VB 6 I have used control arrays which has vastly simplified the whole code.

I have experimented with treating the text boxes as objects and trying to create a string with the first part of the name and using numbers to differentiate between the textboxes but Access does not seem to like this.

The code I have tried is below:

Dim obTextBox As TextBox
Dim str As String

str = "Forms!frm_Shift_Entry_3!txtFST2"

‘Set obTextBox = Forms!frm_Shift_Entry_3!txtFST
'Set obTextBox = str

obTextBox.Name = "txtFST2"
obTextBox.Value = Format("12:35", "Short Time")
'b = 7
'obTextBox.Name = "txtFST" & b
'obTextBox.Value = Format("17:12", "Short Time")

commented out sections are other options I have tried.

Can anyone point out any mistakes I’m making or advise whether this is possible in Access?

Thanks

Frozbie

View 8 Replies View Related

Remove Record Source/Control Source

Sep 19, 2005

I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.

I'm trying to do a

Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""


but this does not seem to work in actualy removeing the record source and control source.

View 14 Replies View Related

Forms :: Invalid Control Property - Control Source

Sep 24, 2013

I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?

View 1 Replies View Related

New Field Does Not Show In Control Source For Form Control

May 28, 2015

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.

View 2 Replies View Related

How Do I Save The Data From A Calculation.

Apr 11, 2005

On my form I have calculated fields based on information from my sub forms. I have the calculations all working fine however I can seam to figure out how to save them in the table that my form is linked to as my control source is the calculation. Should I be using my calculations in an area other than the control source?

View 2 Replies View Related

Save Result Of A Calculation Or Function Into A Table

Apr 1, 2006

Hi there,

I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.

But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?

Thanks

View 3 Replies View Related







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