Is there a way to set the criteria on a query based on if a form is open or not? (1 Viewer)

[hubcap750]

Hi All,

What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed. I tried using a function on the OnLoad event of the form to set a variable called IsOpen to 1 if the form is open, and reset that variable to 0 when the form closes, but when I tried using the variable in an IIF statement in the query criteria, I got a "Its too complex" error.

Here's what I really want to do. I have a very complex form with multiple tabs and subforms. The subforms populate based on a query of what is selected and loaded into a textbox control on the first tab of the form. The first tab has a subform that is based on the main table. Rather than recreate that form, I want to copy it and change the rowsource on the first tab to a subset of the main table, and tell the query to use the textbox on the new form so I don't have to go and replicate all the other subforms. Is there a way to do that or am I just screwed?

 

[MarkK]

What I want to do is set the criteria of a query to the value on a form if the form is open, and set it to a different value if the form is closed.

In the query you can call a function, like . . .

SELECT * FROM tblMyTable WHERE Field1 = MyFunctionCall()

In your function, you can check if the form is open, if so, use that, or else, use something else, like . . .

Function MyFunctionCall() As Long
   Const FN as string = "YourFormName"

   If CurrentProject.AllForms(FN).IsLoaded Then
[COLOR="Green"]      'in this case the form is open, so we use it[/COLOR]
      MyFunctionCall = Forms(FN).SomeFormValue
   Else
[COLOR="Green"]      'in this case the form is closed so we do something different[/COLOR]
      MyFunctionCall = SomeDifferentValue()
   End If
End Function

Makes sense?

 

[vbaInet]

Fyi, keep in the back of your mind that IsLoaded will also return True when the form is in Design View. To properly check the open state of an object, one would use one of the SysCmd constants.

 

[hubcap750]

Getting closer. The function works great, does what it is suppose to. Only problem is that the query runs when the form is opening so I get an "invalid use of null" error. Since I know what the problem is I just inserted a on error resume next in there and that seems to have fixed it. Thanks guys, you saved me a ton of work!