[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 924
  • Last Modified:

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.
0
ouestque
Asked:
ouestque
  • 4
  • 4
  • 2
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Screen.ActiveForm.[main_menu1].[DeliveryType]
0
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
"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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now