parameter from Form if it is open (1 Viewer)

Wysy

Registered User.
Local time
Today, 06:45
Joined
Jul 5, 2015
Messages
333
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

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,041
You are just looping through the Forms Collection.?

You need to test the .Isloaded property.

A quick google......

https://www.access-programmers.co.uk/forums/showthread.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. :banghead:

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 " " ?
 
Last edited:

Wysy

Registered User.
Local time
Today, 06:45
Joined
Jul 5, 2015
Messages
333
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

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,041
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

Registered User.
Local time
Today, 06:45
Joined
Jul 5, 2015
Messages
333
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

Enthusiastic Amateur
Local time
Today, 13:45
Joined
Sep 21, 2011
Messages
14,041
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

AWF VIP
Local time
Today, 13:45
Joined
Jul 26, 2013
Messages
10,354
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

Registered User.
Local time
Today, 06:45
Joined
Jul 5, 2015
Messages
333
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!
 

Users who are viewing this thread

Top Bottom