parameter from Form if it is open

[Wysy]

Hi,
I would like to run a parameter query. The parameter comes from a form control. However if the form is not open i would like to see all the results.
I have found a post that described a method based on a function

Function IsFormLoaded(strForm As String) As Boolean

Dim frm As Form

For Each frm In Forms
If frm.Name = "frmServiceEntry" Then
IsFormLoaded = True
Exit Function
End If
Next

End Function

and after this adding the following to the query

IIF(IsFormLoaded("frmServiceEntry"), Forms!frmServiceEntry.ServiceID, " ")

ServiceID is an integer. I do not get it worked. It always return the truepart.
Any idea what am i doing wrong?
Thanks

[Gasman]

You are just looping through the Forms Collection.?

You need to test the .Isloaded property.

A quick google......

https://www.access-programmers.co.uk...ad.php?t=77119

In fact if I run that code it does not even go through the For next loop.?
I've discovered that happens is no form at all is open, so no collection.

So use strForm instead of hardcoding the form name. Then you will get True. However that link allows the same result without having to scroll through a possible lot of forms?
Also you will probably need "*", not " " ?

[Wysy]

I have checked that. The CurrentProject.Allforms(("xxx").isloaded gives an error if used with IIF in paramter for query.
I have changed the code and it still always returns true.

[Gasman]

Originally Posted by Wysy

I have checked that. The CurrentProject.Allforms(("xxx").isloaded gives an error if used with IIF in paramter for query.
I have changed the code and it still always returns true.

I don't think you can use it directly in a query, but in the function. Just a more efficient way of getting the same answer?

Show your new code please.

[Wysy]

Thank you so much for your help! Let me change my approach:
i would like to have a parameter query where the parameter is taken from frmService if it is open/loaded and and take no paramter in other words so all the results. How can it be done?

[Gasman]

However that logic for the query criteria will not work, as if the form is not loaded, you get prompted for the form control.?

[Minty]

How are you opening the query - put the check for the form value in there and set the parameter value in the query definition when you open it in VBA ?

[Wysy]

Woa. Thanks again. I think i see the solution by now. Somehow i got hooked on the former design and was not able to look out of the box... Thanks again!