calling function as part of criteria

[aft3rgl0w]

hey everyone this is driving me nuts i can't figure out the correct syntax
basically I have a main form, where there are 2 subforms, the main one is
subfrm the secondary is subfrmflt. i have a few different forms that i call
up in the subform area, with queries for their recordsource. there is a box
on each of these forms that the query refers to (Text60). here's the catch:
since the subforms could posibly be opened in either subfrm or subfrmflt, I
need the query to be able to figure out which one is open and fill that into
the path:
Forms!frmMain!subfrm!Text60
I have a function that determines which form is visible and returns the name
of the form as a string ("subfrm" or "subfrmflt")
I need to be able to call it from a query AND in VBA code to fill in that
gap, but i constantly get errors or can't get it to recognize that it's a
function. i've tried
Like "Forms!frmmain!" & selSub() & "!Text60"
but no dice.... any ideas as to how to get this to work?

 

[Marshall Barton]

hey everyone this is driving me nuts i can't figure out the correct syntax
basically I have a main form, where there are 2 subforms, the main one is
subfrm the secondary is subfrmflt. i have a few different forms that i call
up in the subform area, with queries for their recordsource. there is a box
on each of these forms that the query refers to (Text60). here's the catch:
since the subforms could posibly be opened in either subfrm or subfrmflt, I
need the query to be able to figure out which one is open and fill that into
the path:
Forms!frmMain!subfrm!Text60
I have a function that determines which form is visible and returns the name
of the form as a string ("subfrm" or "subfrmflt")
I need to be able to call it from a query AND in VBA code to fill in that
gap, but i constantly get errors or can't get it to recognize that it's a
function. i've tried
Like "Forms!frmmain!" & selSub() & "!Text60"
but no dice....

Try this kind of syntax:

Like Forms!frmmainControls(" & selSub() & ")!Text60

Make sure your function is returning the subform **control**
name, which might be different from its SourceObject.

Since that criteria does not appear to use a wildcard, you
should use = insttead of Like.

 

[aft3rgl0w]

hey thanx for the reply, i got it working in VBA code with
x = forms!frmmain.controls(selsub())!text60

however i'm still having issues when using it in the criteria section of a
query
when i try what you suggested:
Forms!frmmainControls(" & selSub() & ")!Text60

it gives me an error: "The expression you entered has an invalid . or !
operator or invalid parentheses" and the ! right before Text60 is
highlighted. still doesn't seem to be pickig up the function (which returns
as a string either "subfrm" or "subfrmflt", the name of the active visible
form)

any further suggestions? i'm dying to get this to work!!
thanks again

 

[aft3rgl0w]

that doesn't work either.... i get the same error message but the . before
Form.Text60 now highlights. it doesn't seem to like being broken and
concatenated. Or there's some special way of doing i'm not aware of.
if i try this:
[forms]![frmmain].controls(selsub())!form.[text60]
or this:
[forms]![frmmain].controls(selsub())![text60]

same error, the ! right after the ) is always highlighted. this is getting
frustrating!! it works fine in vba code...

thanks for your continued help on this btw any other ideas?

 

[Marshall Barton]

that doesn't work either.... i get the same error message but the . before
Form.Text60 now highlights. it doesn't seem to like being broken and
concatenated. Or there's some special way of doing i'm not aware of.
if i try this:
[forms]![frmmain].controls(selsub())!form.[text60]
or this:
[forms]![frmmain].controls(selsub())![text60]

same error, the ! right after the ) is always highlighted. this is getting
frustrating!! it works fine in vba code...

You're right. I forgot that queries get confused by the ( )
syntax because they think there should be a function anme,
not an object reference. Sorry for wasting your time.

I think you will have to change your function to return the
text box's value instead of the subform control name. For
example,

Public Function YourFunc(strCtlName As String)
Dim strSubFrm As String
. . .
strSubFrm = ...
YourFunc = Me(strSubFrm).Form(strCtlName)
End Function

Then the query can use:

YourFunc("Text60")

instead of the reference we've been trying to to use.

 

[aft3rgl0w]

IT WORKED!!! THANK YOU THANK YOU THANK YOU !!!!!!!
you've just helped me solve a long-running and irritating problem and now
everything works as it should!!

that doesn't work either.... i get the same error message but the . before
Form.Text60 now highlights. it doesn't seem to like being broken and
concatenated. Or there's some special way of doing i'm not aware of.
if i try this:
[forms]![frmmain].controls(selsub())!form.[text60]
or this:
[forms]![frmmain].controls(selsub())![text60]

same error, the ! right after the ) is always highlighted. this is getting
frustrating!! it works fine in vba code...

You're right. I forgot that queries get confused by the ( )
syntax because they think there should be a function anme,
not an object reference. Sorry for wasting your time.

I think you will have to change your function to return the
text box's value instead of the subform control name. For
example,

Public Function YourFunc(strCtlName As String)
Dim strSubFrm As String
. . .
strSubFrm = ...
YourFunc = Me(strSubFrm).Form(strCtlName)
End Function

Then the query can use:

YourFunc("Text60")

instead of the reference we've been trying to to use.