using IIF to determine form state

[aft3rgl0w]

hey all, I have a query that displays the content of a cd. on the main form
i work with, the media form can be displayed as a subform of either
Forms!frmMain!subfrm or
Forms!frmMain!subfrmflt
In the criteria for one of the fields, it references a value on the media
form ([Text60])
this is how i'm trying to set the criteria:
"[Forms]![frmMain]!["
&iif(forms!frmmain!subfrmflt.visible=true,"subfrmflt","subfrm") & "]![Text60]"
basically throwing an iif in the middle to determine whether the floating
form is visible. I know it will work if i start with iif(...); however I am
trying to keep things as compact as possible. i don't get any errors using
the criteria above, however, nothing displays either. Is there a "correct"
syntax to how I want the criteria layed out, or do i need to use the "long"
form?

 

[Dale Fye]

Usually, the syntax for referring to controls on a subform is:

Form.subformcontrolname.Form.controlname

Have you tried:

Forms(frmMain).Controls(IIF(....)).Form.[Text60]

I created a form (frm_with_subform).
I added two subforms ("sub_Numbers", "sub_Numbers2") to the form
I added a textbox (Text4) to the main form

Opened the form, and navigated to different records in the two subforms.
Then used the immediate window to print the value in the txt_Numbers field
of each of the subforms by changing the value in Text4, using the following:

?forms("frm_with_subform").Controls(iif(forms("frm_with_subform").Controls("Text4").value = 1,"sub_Numbers", "sub_Numbers2")).Form.txt_Numbers

Sometimes, readability is far more important than keeping your code compact.
Where are you using this? What does the rest of the code look like. I
think for readability, I would find another way (other than the IIF
statement).

If this is in the criteria line of a query, I might even go so far as to
write a function and call the function rather than have some funky construct
using the IIF in a WHERE clause.

HTH
Dale

 

[aft3rgl0w]

Hey Dale , the I had considered the thought of using a function but until now
would have only used it in 1 or 2 places... now however I have more use for
it and have created a simple function. Basically I have 2 subforms on a main
form, both used for displaying the same set of forms, however, one form
"floats" over the other one (i use code to make it visible or not, hence the
"floating) which is used when i need to keep the current subform open while
referencing something on a different form. since any subform could
potentially be opened in the "regular" subform or the "floating" subform, the
function checks if the floating for is visible, and if so, sets it's value to
that floating form, else, it's the regular one. (hope you're able to follow
so far)
this is the function:
Public Function selForm()
If Forms!frmmain!subfrmflt.Visible = True Then
selForm = "forms!frmMain!subfrmflt"
Else
selForm = "forms!frmMain!subfrm"
End If
End Function

only it returns a text string instead of a form name. I've tried different
combinations of syntax but nothing works
any suggestions? also, very important, how do i call this properly from
both a query (as part of the criteria) and in vb code? thanx for your help!!

Usually, the syntax for referring to controls on a subform is:

Form.subformcontrolname.Form.controlname

Have you tried:

Forms(frmMain).Controls(IIF(....)).Form.[Text60]

I created a form (frm_with_subform).
I added two subforms ("sub_Numbers", "sub_Numbers2") to the form
I added a textbox (Text4) to the main form

Opened the form, and navigated to different records in the two subforms.
Then used the immediate window to print the value in the txt_Numbers field
of each of the subforms by changing the value in Text4, using the following:

?forms("frm_with_subform").Controls(iif(forms("frm_with_subform").Controls("Text4").value = 1,"sub_Numbers", "sub_Numbers2")).Form.txt_Numbers

Sometimes, readability is far more important than keeping your code compact.
Where are you using this? What does the rest of the code look like. I
think for readability, I would find another way (other than the IIF
statement).

If this is in the criteria line of a query, I might even go so far as to
write a function and call the function rather than have some funky construct
using the IIF in a WHERE clause.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

hey all, I have a query that displays the content of a cd. on the main form
i work with, the media form can be displayed as a subform of either
Forms!frmMain!subfrm or
Forms!frmMain!subfrmflt
In the criteria for one of the fields, it references a value on the media
form ([Text60])
this is how i'm trying to set the criteria:
"[Forms]![frmMain]!["
&iif(forms!frmmain!subfrmflt.visible=true,"subfrmflt","subfrm") & "]![Text60]"
basically throwing an iif in the middle to determine whether the floating
form is visible. I know it will work if i start with iif(...); however I am
trying to keep things as compact as possible. i don't get any errors using
the criteria above, however, nothing displays either. Is there a "correct"
syntax to how I want the criteria layed out, or do i need to use the "long"
form?