Query criteria from either of two forms

[George Too]

Hi all,
I am creating a query where the criteria for one of the fields can come from either one of two forms depending on the form that is open. Both forms have a similar text box with a reference number so I want the query to grab that number from eiter form1 or form2. The problem I'm having is that the query "sees" the number in one of the forms but prompts for the number on the form that is not open. Is this doable?

Thanks,
George

 

[cable]

use a function instead?

that way the function could check if the form was open

Public Function IsLoaded(ByVal strFormName As String) As Boolean
' Purpose:      to see if a form is open (loaded)
' Arguments:    strFormName is the name of the form
' Notes         Returns True if the specified form is open in Form view or Datasheet view.
On Local Error GoTo IsLoadedError

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
    If Forms(strFormName).CurrentView <> 0 Then
        IsLoaded = True
    End If
End If

Exit Function
IsLoadedError:
    IsLoaded = False
End Function

 

[George Too]

Excuse my ignorance, but then how do I pass the value to the query? I will give it a try but still I was looking for a solution where I don't have to add too many steps for simplicity sake.

 

[cable]

ah right, you will need two functions.

The first would be the isloaded function i posted.

The second would look something like this:

public function MyCode

if isloaded("FormAName") then
mycode=FormA.text1
elseif isloaded("FormBName") then
mycode=formb.text2
end if

end function

Then in your criteria you could just put: MyCode()

 

[George Too]

I used your code and worked well. Thanks a bunch!