Error in calculated fields dependent upon another field in a form

[John134]

I have an unbound form with several calculated fields. These fields depend
upon the value in a combo box on the form. However, when the form is opened
all the fields show up as errors before I can even enter the combo box value.

If I switch to Design mode and then back to Form mode and re-enter the combo
box value, then all the calculated fields are displayed correctly.

I'm not sure what is happening and would appreciate any assistance regarding
how to display the proper fields after I enter the combo box value.

John134

 

[Allen Browne]

You will need to identify what is actually wrong in each expression that
yields #Error.

For example:
a) Some might be dividing by zero.
b) Some may be using functions that cannot handle Null.
c) Some expressions may be mal-formed if the combo is Null.

As an example of the last one consider this:
=DLookup("ID", "Table1", "[SomeField] = " & [Combo0])
If the combo is Null, the 3rd argument resolves to:
[SomeField] =
which is clearly not correct. More help on DLookup():
http://allenbrowne.com/casu-07.html

 

[John134]

Thank you, Allen, for the reply.

Yes, my combo box is null when the form is opened and that is causing the
error messages in the calculated fields. I was able to get around this by
setting the calculated fields visibility to false, entering the combo box
value, and adding the code "Me!Recalc" to the combo box Update event, and
then coding the Visiblity of the fields to true.

John134

You will need to identify what is actually wrong in each expression that
yields #Error.

For example:
a) Some might be dividing by zero.
b) Some may be using functions that cannot handle Null.
c) Some expressions may be mal-formed if the combo is Null.

As an example of the last one consider this:
=DLookup("ID", "Table1", "[SomeField] = " & [Combo0])
If the combo is Null, the 3rd argument resolves to:
[SomeField] =
which is clearly not correct. More help on DLookup():
http://allenbrowne.com/casu-07.html

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

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

I have an unbound form with several calculated fields. These fields depend
upon the value in a combo box on the form. However, when the form is
opened
all the fields show up as errors before I can even enter the combo box
value.

If I switch to Design mode and then back to Form mode and re-enter the
combo
box value, then all the calculated fields are displayed correctly.

I'm not sure what is happening and would appreciate any assistance
regarding
how to display the proper fields after I enter the combo box value.

John134

 

[John W. Vinson]

I have an unbound form with several calculated fields. These fields depend
upon the value in a combo box on the form. However, when the form is opened
all the fields show up as errors before I can even enter the combo box value.

Well... yes. When the form is opened the combo box does not contain a value,
so any expression based on it will be (at best) Null and quite possibly
#Error. If you enter something in the combo box do the controls recover? If
not, consider putting a Me.Recalc in the combo's AfterUpdate event.

If I switch to Design mode and then back to Form mode and re-enter the combo
box value, then all the calculated fields are displayed correctly.

I'm not sure what is happening and would appreciate any assistance regarding
how to display the proper fields after I enter the combo box value.

Perhaps you could post some of the expressions you're using; it may be
possible to have the expression detect the absence of data in the combo and
display something appropriate.