Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-14-2019, 03:16 AM   #1
Wysy
Newly Registered User
 
Join Date: Jul 2015
Posts: 160
Thanks: 2
Thanked 2 Times in 2 Posts
Wysy is on a distinguished road
parameter from Form if it is open

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

Wysy is offline   Reply With Quote
Old 02-14-2019, 03:20 AM   #2
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: parameter from Form if it is open

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 " " ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 02-14-2019 at 03:37 AM.
Gasman is offline   Reply With Quote
Old 02-14-2019, 03:44 AM   #3
Wysy
Newly Registered User
 
Join Date: Jul 2015
Posts: 160
Thanks: 2
Thanked 2 Times in 2 Posts
Wysy is on a distinguished road
Re: parameter from Form if it is open

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.

Wysy is offline   Reply With Quote
Old 02-14-2019, 03:50 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: parameter from Form if it is open

Quote:
Originally Posted by Wysy View Post
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-14-2019, 04:05 AM   #5
Wysy
Newly Registered User
 
Join Date: Jul 2015
Posts: 160
Thanks: 2
Thanked 2 Times in 2 Posts
Wysy is on a distinguished road
Re: parameter from Form if it is open

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?
Wysy is offline   Reply With Quote
Old 02-14-2019, 04:07 AM   #6
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,672
Thanks: 398
Thanked 637 Times in 618 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: parameter from Form if it is open

However that logic for the query criteria will not work, as if the form is not loaded, you get prompted for the form control.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 02-14-2019, 04:09 AM   #7
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,307
Thanks: 153
Thanked 1,690 Times in 1,662 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: parameter from Form if it is open

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 ?

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 02-14-2019, 04:15 AM   #8
Wysy
Newly Registered User
 
Join Date: Jul 2015
Posts: 160
Thanks: 2
Thanked 2 Times in 2 Posts
Wysy is on a distinguished road
Re: parameter from Form if it is open

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!

Wysy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Mystery parameter on form open steveg242 Forms 4 04-25-2017 01:01 PM
Parameter Form Will Not Open After Parameter Entered manybeats Forms 1 06-28-2012 06:04 AM
Open form with parameter query TomBP Forms 6 07-03-2011 10:24 AM
Open Form Parameter NHC General 6 08-25-2006 03:23 AM
Open form trying to use where parameter, somewhat lengthy. cominapart Modules & VBA 2 06-25-2003 12:01 PM




All times are GMT -8. The time now is 03:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World