Iff statement 2

[Imbriani]

Hi all,

I'm trying to put a value in a text box based on the values from a field on a main form and one on a subform:

=IIf([resultelisa]="Positive" And [Forms]![subform1]![subelisaresults]="Positive","Positive","In Process")

I got some of this from thread702-733634, but the above gives me the #Name? error. I know it's something about the naming, but I thought I followed proper protocols. any ideas anyone?

Kathy

 

[PaulF]

[Forms]![subform1]![subelisaresults]
needs to be

[Forms]![subform1].Form![subelisaresults]

PaulF

 

[PHV]

And what about this ?
=IIf(Me!resultelisa="Positive" And Me!subform1.Form!subelisaresults="Positive","Positive","In Process")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Imbriani]

I still get the #Name error.

When I enter either of your suggestions into the text box, Access reformats it. Here's an example using PHV's suggestion:

=IIf([Me]![resultelisa]="Positive" And [Me]![subform1].[Form]![subELISAresults]="Positive","Positive","In Process")

Does this additional bracketing that Access puts in have any effect?

 

[RoyVidar]

I don't think the Me keyword works in controlsources, use either just the control names, or a full reference (here, just control names):

[tt]=IIf([resultelisa]="Positive" And [subform1].[Form]![subELISAresults]="Positive","Positive","In Process")[/tt]

And be sure you use the correct subform control name, which can differ from the name of the subform as viewed in the database window.

Use the expression builder, doubleclick through forms, loaded forms, main form, subform and the control on the subform to get the correct reference...

Roy-Vidar

 

[Imbriani]

Still getting an error. Does the #Name error mean that the all is well except that I have a name of a control wrong?

I now have: =IIf([resultelisa]="Positive" And [Forms]![subform1].[Form]![ELISAresults]="Positive","Positive","In Process")

This is the syntax that the expression builder suggested.
The name of the subform is subform1, but on the main form, it is subtable subform. Is this right? The names of the controls are now correct (I had used the table field name of the last control instead of its name on the subform.) I'm stumped.

 

[RoyVidar]

It is very unusual for the expression builder to suggest something like this

[tt][Forms]![subform1].[Form]![ELISAresults][/tt]

it usually suggests (control names)

[tt][subform1].[Form]![ELISAresults][/tt]

or (full reference)

[tt][Forms][frmMainForm]![subform1].[Form]![ELISAresults][/tt]

#Name error can be caused by several things, one of them, that the controls have the same name as the fields they are bound to.

But do try building this incrementally (step by step). First see if

[tt]=[resultelisa][/tt]

gives a result, then

[tt]=[subform1].[Form]![ELISAresults][/tt]

then try building the Iif...

Roy-Vidar

 

[Imbriani]

I've tried your suggestions. =[resultelisa] gives the correct value, but the second step you suggested: =[subform1].[Form]![ELISAresults] give the #Name error. Putting both together gave the same error.

I ran this through the expressions builder, and Access suggests the syntax: =[Forms]![subform1]![ELISAresults], which also doesn't work. I've double checked the names of the controls. The control on the subform, [elisaresults] is a different name from the field it's bound to. I'm still stumped.

 

[RoyVidar]

When the form with the subform is open in design view, use the method i described with the expression builder (Use the expression builder, doubleclick through forms, loaded forms, main form, subform and the control on the subform to get the correct reference...)

Do not doubleclick directly to the the form you are using as subform, but go through the main form. Don't miss that step.

The reference "=[Forms]![subform1]![ELISAresults]" is only to be used when referencing a single, stand alone form, and does NOT work with a subform.

Roy-Vidar

 

[farmera]

Roy-Vidar,

I was having the same problem but your last post walked me through using the expression builder and I got the correct path! Thanks so much. You've helped make my job easier several times since I joined Tek-Tips this year!

farmera

 

[Imbriani]

Roy-Vidar,

IT WORKED!! (Snoopy dance of joy). I thought I'd tried all the combinations, but apparently not. Thank you so much. This is an important lesson for me as a lot of my present work will involve this sort of programming. Here's a star for your patience.