Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 8th, 2004, 01:15 PM
Registered User
Join Date: Mar 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Validate calculated field in sub form


I have a total in the form footer of a sub form which I would like to compare with a value on my main form -- then flash up a simple msgbox if the sub form value is greater than the main form value.

The problem seems to be comparing against a calculated value.

Any help appreciated.

Old March 8th, 2004, 01:40 PM
Authorized User
Join Date: Feb 2004
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

Assuming that the values are displayed in a control like a label or a textbox, you can compare the values or the values of the captions. Most likely you have these figures as the Value property of textboxes. This is the default property so you need to be able to reference the controls by their names.

The bigger question is when do you want this message to display? Your options are likely the afterupdate event of the parent control value, assuming it is entered by a user, or the current event of the parent form. Alternatively, if there are line items that you are adding in the sub form, you may want to use the afterupdate event of the subform. Essentially, you need to choose one or more events that are triggered when the record changes or the values to be compared will change.

Assuming you are calling it from a parent form event, the syntax looks like:

If Me.txtBoxValue < Me.subFormControlName.Form.txtCalculated Then
End if

where txtBoxValue is the name of the control on the parent form and subFormControlName is the name of the subform control, not the name of the form embedded as a subform, and txtCalculated is the name of the control containing the calculated value on the subform.

To call it from an event on the subform, the syntax is a bit simpler:

If Me.txtCalculated > Me.Parent.txtBoxValue Then
End if

Jürgen Welz
Edmonton AB Canada
Old March 8th, 2004, 01:42 PM
Registered User
Join Date: Mar 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts

I'm not sure if I exactly understand your problem. If you are having trouble figuring out the syntax to address controls on another subform, join the club. But if you want to see at any given time whether one value is greater than another, then I think the simple answer is to create 2 variables in your module [Public intFirstValue as Integer, Public intSecondValue as Integer] and then assign the two numbers you are trying to compare to these variables.

From that point, you need a little procedure that you call whenever you want to compare these numbers:

sub Check Values ()
   If intSecondValue <> intFirstValue then
   end if
end sub

The only problem left is where to put the call, and that involves figuring out an event to stick it in [form open, form change, control before update, etc.].

Old March 12th, 2004, 03:18 PM
Ben Ben is offline
Authorized User
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

quote:If you are having trouble figuring out the syntax to address controls on another subform, join the club.
Enter the below in the ControlSource of a Control on your form:

=[Forms]![Form Name]![SubForm Name].[Form]![SubForm Control Name]

Only thing to remember is the SubForm Name is the name defined for the SubForm Control on your Form, not the name of the SubForm as defined in the database.
Old March 18th, 2004, 04:26 PM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

I haven't tried this but perhaps Conditional Formatting would work here. If the value in the one field was greater than or equal to the value in another field, then display Red Bold. I am not sure if this will work between subforms and forms, but I do exactly what you are asking on Reports using CF. I know it also works with Forms.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping with calculated field in report jack123 Access 3 August 9th, 2007 06:46 AM
How to create a Calculated Field fdtoo SQL Server 2000 0 April 11th, 2006 08:34 PM
Automatically select value from a calculated field jimmy0305 Access 0 June 21st, 2005 10:43 AM
Auto Calculated Field in Access anubhav.kumar Access 1 March 15th, 2005 08:37 AM
how to added a calculated field in pivot table ndenhere BOOK: Access 2003 VBA Programmer's Reference 0 May 27th, 2004 02:45 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.