Help with calculated control on form - Microsoft Access / VBA

Tony Williams

I have a form with a tabcontrol which has a number of pages. I want to check
the value of a calculated control on one page with a calculated control on
another page. The calculated control (txtTotNbrClien ts) on page 3 is the sum
of these controls on that page
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0])
I tried using the calculated control names in my code but that didn't work
so I assumed I would have to do the calculation again in the code. So here
is my code which I have tried in the BeforeUpdate and AfterUpdate property
of txtClientsTot
If ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to accept
the error?", vbYesNo, "Calculatio n Error") = vbNo Then
Cancel = True
End If
End If

I should get a message box if the two totals don't agree but I don't.
Anyone help here?
TIA
Tony

Nov 13 '05 #1

Subscribe Reply

1811

Stefan Kowalski

"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cu******** **@sparta.btint ernet.com...

I have a form with a tabcontrol which has a number of pages. I want to
check
the value of a calculated control on one page with a calculated control on
another page. The calculated control (txtTotNbrClien ts) on page 3 is the
sum
of these controls on that page
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0])
I tried using the calculated control names in my code but that didn't work
so I assumed I would have to do the calculation again in the code. So here
is my code which I have tried in the BeforeUpdate and AfterUpdate property
of txtClientsTot
If ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000]
+
[txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" &
vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to accept
the error?", vbYesNo, "Calculatio n Error") = vbNo Then
Cancel = True
End If
End If

I should get a message box if the two totals don't agree but I don't.
Anyone help here?
TIA
Tony

Hopefully you had your rounding issues resolved last time and understand
that bit, but this time could you be falling into the null trap. Paste this
code into a module and run it:

Public Sub TestMe()

' This sub demonstrates the folly of mis-using nulls
If 2 + 3 + Null <> 13 Then
' If it isn't thirteen, then tell me it isn't
MsgBox "Two plus three plus null does not equal thirteen"
Else
' Otherwise it must equal thirteen (doh!)
MsgBox "Two plus three plus null equals thirteen"
End If

End Sub

In other words, null values in your textboxes can wreck your calculations.
You should go slowly through each textbox building up your total. Convert
each value to the datatype you are looking for. E.g. if the result was
supposed to be of single precision which is usually sufficient:

....
sngTotal = sngTotal + CSng(Nz(Me.txtC lients0,0))
sngTotal = sngTotal + CSng(Nz(Me.txtC lients500,0))

Then you can Debug.Print to see what the total actually is, instead of
saying simply "if it isn't this, do that".

Nov 13 '05 #2

Tony Williams

Thanks again Stefan. yes I did resolve the rounding issues, changed the
field type from double. I'll try out the ideas you've given me and let you
know.
Thanks again
Tony
"Stefan Kowalski" <a@b.com> wrote in message
news:cu******** **@sparta.btint ernet.com...

"Tony Williams" <tw@tcpinvalid. com> wrote in message
news:cu******** **@sparta.btint ernet.com...
I have a form with a tabcontrol which has a number of pages. I want to
check
the value of a calculated control on one page with a calculated control on another page. The calculated control (txtTotNbrClien ts) on page 3 is the
sum
of these controls on that page
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt])
The calculated control on page 4 (txtClientsTot) is the sum of these
controls on that page
([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0])
I tried using the calculated control names in my code but that didn't work so I assumed I would have to do the calculation again in the code. So here is my code which I have tried in the BeforeUpdate and AfterUpdate property of txtClientsTot
If ([txtClients0] + [txtClients500] + [txtClients1000] + [txtClients5000] +
[txtClients10000] + [txtClients50000] + [txtClients10000 0]) <>
([txtClientsdomfa csole] + [txtClientsdomid sole] + [txtClientsexpso le] +
[txtClientsimpso le] + [txtClientsdomfa cpart] + [txtClientsdomid part] +
[txtClientsexppa rt] + [txtClientsimppa rt]) Then
If MsgBox("Total does not agree with Total Number of Clients on Page 3" & vbCrLf & "It should be " & [txtTotNbrClient s] & " - Do you want to accept the error?", vbYesNo, "Calculatio n Error") = vbNo Then
Cancel = True
End If
End If

I should get a message box if the two totals don't agree but I don't.
Anyone help here?
TIA
Tony
Hopefully you had your rounding issues resolved last time and understand
that bit, but this time could you be falling into the null trap. Paste

this code into a module and run it:

Public Sub TestMe()

' This sub demonstrates the folly of mis-using nulls
If 2 + 3 + Null <> 13 Then
' If it isn't thirteen, then tell me it isn't
MsgBox "Two plus three plus null does not equal thirteen"
Else
' Otherwise it must equal thirteen (doh!)
MsgBox "Two plus three plus null equals thirteen"
End If

End Sub

In other words, null values in your textboxes can wreck your calculations.
You should go slowly through each textbox building up your total. Convert
each value to the datatype you are looking for. E.g. if the result was
supposed to be of single precision which is usually sufficient:

...
sngTotal = sngTotal + CSng(Nz(Me.txtC lients0,0))
sngTotal = sngTotal + CSng(Nz(Me.txtC lients500,0))

Then you can Debug.Print to see what the total actually is, instead of
saying simply "if it isn't this, do that".

Nov 13 '05 #3

Similar topics

5330

saving calculated values

by: Jeremy Weiss | last post by:

I've got a temp table that contains the fields: amountowed, amountpaid, and balanced. I've got a form that shows this information and I've set it up so that when the amountpaid field is changed it subtracts the amountpaid from the amountowed and display's it in the balanced field. Unfortunately it doesn't save what is displayed back to the table. It does, however, save the changes to the amount paid field. I'm assuming my problem is...

Microsoft Access / VBA

2137

Help Needed With Bizarre Database Behaviour In Access 2003

by: Wayne Aprato | last post by:

I have a simple database which was originally written in Access 97. When converted to Access 2000 file format it ran flawlessly in Access 2002. I've just tried to run it in Access 2003 and I am seeing the following behaviour: Some of the fields on the continuous main form which is a list of jobs with their related details "flicker". While this is going on the form seems to function as it should and isn't locked up. If I click and hold...

Microsoft Access / VBA

2666

HELP.... How to avoid updating selling prices?

by: Colin | last post by:

I have a query that calculates the selling price of products on customer orders. Selling prices are calculated based on the average cost of the items when purchased. As I make new purchases, the product cost changes (as it should) to reflect the new average cost. However, the problem is that the selling price of previously sold items also change when new purchases are made. Keep in mind that I'm not storing any calculated values anywhere....

Microsoft Access / VBA

2056

calculated form

by: Paolo | last post by:

Friends, I have a table with a field named Initials which has its record source to another table named Initials. I would like to add on a form named Welcome two controls: A combo box, which displays the initials of all my database users and a text box which calculated the number of records associated to the selected user. I would like to use the OnChange event of the combo to run the query or the calculation code.

Microsoft Access / VBA

2313

Help with controls on a form

by: Tony Williams | last post by:

I have two tables 1.tblmonth which holds two fields txtmonth and txtqtrlabel and 2. tblmain which holds a number of fields but in particular a field called txtqtrlabel2. The two tables are linked on txtqtrlabel and txtqtrlabel2. I have a main form based on tblmonth which has a subform based on tblmain, the two forms are also linked on the same fields. The data entered into txtmonth could be "30/09/04" and the txtqtrlabel data would be...

Microsoft Access / VBA

8911

Calculated total on subform will not display on main form

by: Shannan Casteel via AccessMonster.com | last post by:

I have a subform named "sbfrmParts" with a list of parts along with the quantity and price. I have used a text box in the subform's footer and set the control source to "=Sum(*)". I set the format to display currency. This text box is called "SubformTotal", and is visible property is set to "No". On the main form I have made another text box and set its control source to "=.Form!SubformTotal". When I enter some parts everything works...

Microsoft Access / VBA

5884

form/subform and calculated control

by: dhowell | last post by:

I have a "form" and "subform" where I would like a calculated control on the form which sums the values of a datasheet column of the subform. (datasheet on subform may have a variable number of entries) I can't seem to get the calculated control on main form to do what I want...... Is this possible, if so, how? Help!

Microsoft Access / VBA

2660

calculated caption on tab control

by: Jimmy Stewart | last post by:

I want to use a calculated function for the caption on my tab controls. I used the following code: Me.Page28.Caption = "Expenses & "]" This should display the following: " Expenses " where 10 is the number of expenses on that page. On my form I have a calculated control named 'ExpenseEntryCount' that correctly displayd this information. If I place this code in the forms OnOpen, OnActivate or OnCurrent event,

Microsoft Access / VBA

5545

filter by form on a calculated control

by: jcf378 | last post by:

hi all. I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields and ). However, when I click "Filter by Form" in order to search for records based on this form, I would like to be able to enter a value in this "days" control so that I can filter records on the form based on this calculated interval (i.e., i want to search for records specifically where...

Microsoft Access / VBA

9212

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

9973

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9790

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9779

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9645

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8645

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7186

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6473

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

3276

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP