Calculating a control based on other calculated controls

[Guest]

I have a control on a form/sub-form. I am trying to calculate a control based on the values of other controls on the form. I can not use expression builder because there is some complex if logic. I have my code in the form_current event

Here are some of the various things I have tried

maxfieldname < (Me![fieldname] + 0
maxfieldname < (Me![fieldname].value + 0
maxfieldname < forms!frmMain!sfrmSub.form!fieldnam
maxfieldname < forms!frmMain!sfrmSub.form!fieldname.valu

When I run any of these combinations I get a run-time error You entered an expression that has no value. I suspect I should be putting this code in a different event. Also, all the fields on the form are read only. Any help would be greatly appreciated. Thank you

David

 

[Wayne Morgan]

If what you have entered here is all that you have, then there is a problem.
What you have appears to be part of an If statement, such as:

If maxfieldname < forms!frmMain!sfrmSub.form!fieldname Then
'do something here
End If

What is maxfieldname, is it a field or are you trying to find the maximum
value in a field?

--
Wayne Morgan
Microsoft Access MVP

I have a control on a form/sub-form. I am trying to calculate a control

based on the values of other controls on the form. I can not use expression
builder because there is some complex if logic. I have my code in the
form_current event.

Here are some of the various things I have tried:

maxfieldname < (Me![fieldname] + 0)
maxfieldname < (Me![fieldname].value + 0)
maxfieldname < forms!frmMain!sfrmSub.form!fieldname
maxfieldname < forms!frmMain!sfrmSub.form!fieldname.value

When I run any of these combinations I get a run-time error You entered an

expression that has no value. I suspect I should be putting this code in a
different event. Also, all the fields on the form are read only. Any help
would be greatly appreciated. Thank you.

 

[Allen Browne]

David, you may need to tell us what you are trying to do here.

The 4 lines you gave are expressions that return the value True, False, or
Null, depending on what is in the text boxes. Is maxfieldname a control? a
variable? something else? Are these expressions used in an If statement?
They are not complete statments on their own.

I also did not understand the bit about adding zero to the value in the
control. Are you expecting the value to be text (and therefore trying to
concatenate a zero character onto the end of the string) or numeric (why add
zero?) or Null (still yields Null)?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a control on a form/sub-form. I am trying to calculate a control

based on the values of other controls on the form. I can not use expression
builder because there is some complex if logic. I have my code in the
form_current event.

Here are some of the various things I have tried:

maxfieldname < (Me![fieldname] + 0)
maxfieldname < (Me![fieldname].value + 0)
maxfieldname < forms!frmMain!sfrmSub.form!fieldname
maxfieldname < forms!frmMain!sfrmSub.form!fieldname.value

When I run any of these combinations I get a run-time error You entered an

expression that has no value. I suspect I should be putting this code in a
different event. Also, all the fields on the form are read only. Any help
would be greatly appreciated. Thank you.

 

[Guest]

Allen

Thanks for getting back to me. The expressions were 4 different attempts to get the if logic right. Depending on the result of the comparison I may use part of the if logic to assign a value to a variable. Maxfieldname is a variable that is successfully populated (I verified it with a quick watch). You are quite correct that the statements were not complete statements. I just wanted to highlight the control name I am referencing. The +0 was there because I have another field with the same forms!frmMain!sfrmSub.form!fieldname syntax. I wanted to isolate getting one variable right before I tackled the second

I suspect the problem is I am placing the calculation in the wrong event (current) because some of the other controls are populating after the form is painted

To restate what I am trying to do is to find out where I can place my logic to have a form control calculate based on other form controls with VBA code. If I have to I will try to figure out an expression, but I am hoping to avoid that route if possible. Thanks again for your help

Davi
----- Allen Browne wrote: ----

David, you may need to tell us what you are trying to do here

The 4 lines you gave are expressions that return the value True, False, o
Null, depending on what is in the text boxes. Is maxfieldname a control?
variable? something else? Are these expressions used in an If statement
They are not complete statments on their own

I also did not understand the bit about adding zero to the value in th
control. Are you expecting the value to be text (and therefore trying t
concatenate a zero character onto the end of the string) or numeric (why ad
zero?) or Null (still yields Null)

 

[Allen Browne]

So MaxFieldName is a variable (not a text box).
I assume it is Dim'd in Form_Current.
You want to assign a value to the variable, and do something with it in the
event (since it will go out of scope when the event ends.

This example declares the variable, assigns it whatever value is in

Private Sub Form_Current()
Dim Maxfieldname as Variant

'Assign the value in the "fieldname" control to the variable.
Maxfieldname = Me!fieldname

If MaxfieldName < Me.sfmSub.Form!fieldname Then
MsgBox "The main's form value is less than the subform's."
ElseIf MaxfieldName > Me.sfmSub.Form!fieldname Then
MsgBox "The main's form value is greater than the subform's."
Else
MsgBox "Equal or null"
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for getting back to me. The expressions were 4 different attempts

to get the if logic right. Depending on the result of the comparison I may
use part of the if logic to assign a value to a variable. Maxfieldname is a
variable that is successfully populated (I verified it with a quick watch).
You are quite correct that the statements were not complete statements. I
just wanted to highlight the control name I am referencing. The +0 was there
because I have another field with the same
forms!frmMain!sfrmSub.form!fieldname syntax. I wanted to isolate getting one
variable right before I tackled the second.

I suspect the problem is I am placing the calculation in the wrong event

(current) because some of the other controls are populating after the form
is painted.

To restate what I am trying to do is to find out where I can place my

logic to have a form control calculate based on other form controls with VBA
code. If I have to I will try to figure out an expression, but I am hoping
to avoid that route if possible. Thanks again for your help.

 

[Guest]

Allen

Reluctantly, I abandoned my efforts at coding using events. It appeared the values in controls were not set until after the Form_Current event was fired, so I resorted to the tried and true hidden field approach. I made the two fields I look up in the database hidden controls and then had a nested IIF calculate the value of the control.

Thanks
David