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.

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

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

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.

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