Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 8th, 2004, 01:15 PM
Registered User
 
Join Date: Mar 2004
Location: London, , United Kingdom.
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Validate calculated field in sub form

Hi,

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.

Thanks.
Reply With Quote
  #2 (permalink)  
Old March 8th, 2004, 01:40 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
    MsgBox...
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
    MsgBox...
End if


Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
  #3 (permalink)  
Old March 8th, 2004, 01:42 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
        msgbox
   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.].

Reply With Quote
  #4 (permalink)  
Old March 12th, 2004, 03:18 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
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.
Reply With Quote
  #5 (permalink)  
Old March 18th, 2004, 04:26 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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.

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.