Access: Query reference to the open Form

How do I make a query (Lets call it "qry_Deliveries") reference to an object on only the active/open form?

i.e. I have two forms Main Menu1 and Main Menu 2. Each have a textbox titled "DeliveryType"

When Main Menu1 is open (Main Menu2 closes automatically) I want the query to reference to the textbox on Main Menu1.
When Main Menu2 is open (Main Menu1 closes automatically) I want the query to reference to the textbox on Main Menu2.

Ideally I would need a formula that looks something like this in the criteria field of my query: iif(Main_Menu1 is open, =[forms]![main_menu1]![DeliveryType], iif(Main_Menu2 is open, =[forms]![main_menu1]![DeliveryType])

Will you help me make it work.
ouestqueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Screen.ActiveForm.[main_menu1].[DeliveryType]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick_RickardsCommented:
If you create a function such as FormIsLoaded()  - sample function in snippet below - you could do so with an expression like this...

IIF(FormIsLoaded("Form1"), Forms![Form1]![Combo1],IIF(FormIsLoaded("Form2"), Forms![Form2]![Combo1],IIF(FormIsLoaded("Form3"), Forms![Form3]![Combo1],"")))


Function FormIsLoaded(strForm As String) as Boolean
    FormIsLoaded = CurrentProject.AllForms(strForm).IsLoaded
End Function

Open in new window

0
TWBitCommented:
You can add  [Screen].[ActiveForm]![DeliveryType] in your query's criteria.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
TWBit --- see my first post :-)

mx
0
TWBitCommented:
Actually you had a reference to Main_Menu1, which wouldn't work when Main_Menu2 is loaded.  But you do get credit if it was up to me :)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, he has this:

iif(Main_Menu1 is open, =[forms]![main_menu1]![DeliveryType], iif(Main_Menu2 is open, =[forms]![main_menu1]![DeliveryType])

Where in main_menu1  appears in both cases. ... maybe a typo ?

mx
0
TWBitCommented:
Yes, I thought of that as a typo too.  But you really don't even need to use that do you?  You can just put [Screen].[ActiveForm]![DeliveryType] in your query and it will run for whichever for is loaded.

Now, if this is a common query that could be used by forms other than Main_Menu1 or Main_Menu2, then this wouldn't work and the IIF function would need to be called with IsOpen maybe.
0
Rick_RickardsCommented:
By relying on the Screen.ActiveForm object wouldn't one have to insure that the Form in question was not only loaded but that it was also the ActiveWindow?  Seems this would present a problem if a query were being run independantly.  In effect the Form could be loaded/open but if the query were the active window am I not correct in assuming that the Screen Object would fail to recognize the form as open not because it wasn't open but because it wasn't the active window.  Just food for thought.
0
TWBitCommented:
sorry, meant IsLoaded
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"You can just put [Screen].[ActiveForm]![DeliveryType] in your query and it will run for whichever for is loaded."

Yes.
0
ouestqueAuthor Commented:
Thanks for the advice guys!! Interesting discussion!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.