Validating a Form's data via calculated field

Anonymous

Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form.
Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record,
move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining
field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly
appreciated!! :-)

Thank you.

Nov 12 '05 #1

Subscribe Reply

3692

Larry Linson

Certainly, in the BeforeUpdate event, do the calculation and if the result
isn't zero, set Cancel = True before exiting the event procedure. If you
have FundsRemaining as a calculated Control on the Form, of course, you can
just test it. Be sure you requery the calculated Control in the AfterUpdate
event of the CheckAmount or AmountApplied controls.

Larry Linson
Microsoft Access MVP

"Anonymous" <an*******@some where.far> wrote in message
news:daa7b.3994 54$uu5.73774@sc crnsc04...

Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form. Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record, move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly appreciated!! :-)

Thank you.

Nov 12 '05 #2

JBrow

This works great! However, I have a me.recalc event that takes place when
other fields on the form are filled in (running realtime calculations as
funds are applied).

As a result, I get the msgbox "All Funds have not been applied" each time I
enter info on the form. I only need this message to pop up when the user
tries to move on and add a new record when there are still funds left to
apply.

More detail:

Check Amount Entered $500
Category1 Entered $100
Category2 Entered $200
Category3
FundsApplied Calc'd $300
FundsRemaining Calc'd $200

User should not be able to click "NewRecord" because FundsRemaining <>0.
Problem is I get the msgbox error message I programmed each time I enter
info in one of the categories and tab to the next field.

This is what I programmed:

If FundsRemaining <> 0 Then
Cancel = True
MsgBox "You have not applied all funds yet"
End If
I wonder how I can get around getting the message on each data input?

"Larry Linson" <la**********@n tpcug.org> wrote in message
news:1T******** ***********@nwr ddc02.gnilink.n et...

Certainly, in the BeforeUpdate event, do the calculation and if the result
isn't zero, set Cancel = True before exiting the event procedure. If you
have FundsRemaining as a calculated Control on the Form, of course, you can just test it. Be sure you requery the calculated Control in the AfterUpdate event of the CheckAmount or AmountApplied controls.

Larry Linson
Microsoft Access MVP

"Anonymous" <an*******@some where.far> wrote in message
news:daa7b.3994 54$uu5.73774@sc crnsc04...
Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the

form.
Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current

record,
move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the

FundsRemaining
field and pop a msgbox up if FundsRemaining <> 0. Any help would be

greatly
appreciated!! :-)

Thank you.

Nov 12 '05 #3

Cody Martin

"Anonymous" <an*******@some where.far> wrote in message
news:daa7b.3994 54$uu5.73774@sc crnsc04...

Hi all,

I have a form people use to enter checking data. One of the fields is
calculated based on finding the difference of two input fields on the form. Here are the fields:

CheckAmount
AmountApplied
Funds Remaining

FundsRemaining = CheckAmount - AmountApplied

Can I prevent the form from allowing the user to commit the current record, move on and create a new record until FundsRemaining = 0. If the user
clicks the New Record button, I'd like the form to check the FundsRemaining field and pop a msgbox up if FundsRemaining <> 0. Any help would be greatly appreciated!! :-)

Thank you.

Go to: http://www.mvps.org/access/
Best Regards
Cody Martin, MVP

Nov 12 '05 #4

Similar topics

3821

#Error in calculated field in MDE (not MDB)

by: Allen Browne | last post by:

Subform is based on a single-table query that contains a calculated field: Amount: Round(CCur(Nz(*,0)),2) Continuous subform displays this field in a text box named Amount. As user enters new rows into subform, all rows show #Error in this box. This only happens until they close the form, or move the main form to a different record. Worse, it only happens on the client's machine in the MDE. The identical MDB file shows the calculation...

Microsoft Access / VBA

3030

Units Conversion in Data Entry Field on a Form

by: Kevin Myers | last post by:

Hello, Have yet another problem on a form that has me stumped. In a table that I am working with, the values for one of the fields are stored in meters. However the values that are supplied for data entry are in feet. Therefore, I don't believe that I can use a bound text box to support the entry of data for this field. I could use an expression to compute the value for display in the text box as follows: *100/(12*2.54)

Microsoft Access / VBA

5379

How to edit calculated field in FORM

by: Apple | last post by:

Can I edit an calculated field in my form if needed. Thank you in advance for your help. Sincerely Apple

Microsoft Access / VBA

3848

Validating form input data

by: stathis gotsis | last post by:

Hello everyone, I am tying to come up with an elegant way to process some input data that come from a form. When the user hits the 'Submit' button, i want the form to appear again with the already entered valid data filled in and prompt the user to re-enter the non-valid data. If all data is valid, i will forward to an other .php page which enters the data into a database. I tried to do this in the following way: the form always hits...

PHP

4739

Calculated Field Shows No Value in Report

by: kelley.l.turner | last post by:

Hi all, I am very new to MS Access so please bear with me! I have created a simple calculated field in my data entry form, yet when I view my data table or try to generate a report based on this calculated field, no value is posted. How/where do I get my calculated values to also show up in the tables and reports? Thanks, Kelley

Microsoft Access / VBA

1570

Coloring a field on a Continuous form base data in Field

by: James | last post by:

I have a Continuous form in Access 2000. The form contains a date field and the user has requested( demanded nicely) that they want the date field to be yellow if there is no data in the field. Does anyone know how to do this?

Microsoft Access / VBA

5532

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

7809

Overriding a calculated field on a form

by: Haas C | last post by:

Hi all! Is there anyway I can override a value in a calculated field on a form? For example: I have a form which displays the following fields based on a query: Premium Due field has the value of 12,527.75 in it. Brokerage Due field is the calculated field which multiplies a factor

Microsoft Access / VBA

3686

Trouble updating a table using a calculated field via dateadd function...

by: Ciara9 | last post by:

I am having problems trying to update a field in a database using a field in a form. I currently have two fields, Today and Tomorrow in a table named Date. The Today field automatically defaults to the current date, and what I am trying to accomplish is if this date changes, then the second field, Tomorrow, will automatically become equivalant to the value of Today +1. Sounds simple enough, however I cannot get the table to update. I am able to...

Microsoft Access / VBA

1824

Problem with an Auto calculated field

by: Steve67 | last post by:

Wonder if I can get someone’s help with a problem I am having on where to place an IF statement in VBE. I have created a form in Access 2003 that has an automatic calculated field. Basically you enter information into other fields and the Running Total field automatic recalculates itself whenever the other data fields are updated. The problem I am having is that I need a message box to come up as soon as the Running Total reaches a certain...

Microsoft Access / VBA

8866

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++

8781

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

8550

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

6191

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

4193

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4365

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2769

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2006

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

1769

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General