Solved IsLoaded in query criteria?

[Oreynolds]

Hi,

Does anyone know if you can use the IsLoaded function in a query criteria?

I have tried:

IIf(IsLoaded([Forms]![Purchase Orders]),[Forms]![Purchase Orders]![OrderID],[Forms]![Purchase Orders NEW]![OrderID])

But I keep getting an error......!?

 

[Isaac]

Create a VBA function; refer to the function in the IIF statement

 

[Oreynolds]

Create a VBA function; refer to the function in the IIF statement

OK, so I have a cmd button that runs a report which uses the criteria I want as above. I have added the following code onto the cmd button and adjusted the criteria as below but still no luck, any ideas?

Private Sub Command284_Click()

DoCmd.RunCommand acCmdRefresh

Dim strFormName As String

If CurrentProject.AllForms("Purchase Orders").IsLoaded Then
strFormName = "Purchase Orders"
ElseIf CurrentProject.AllForms("Purchase Orders New").IsLoaded Then
strFormName = "Purchase Orders New"
End If

Criteria is now:

IIf(IsLoaded("strFormName"),[Forms]![Purchase Orders]![OrderID],[Forms]![Purchase Orders NEW]![OrderID])

 

[Minty]

I would only use one form for both New and Existing orders and then you remove the problem.
You can check if you are on a new record in the form using

If Me.NewRecord Then ...

To set controls or values that you may need on a new or existing record

 

[MajP]

More like

Public Function GetID()as long
  If CurrentProject.AllForms("Purchase Orders").IsLoaded Then
   GetID = Forms("Purchase Orders").[Order ID]
ElseIf CurrentProject.AllForms("Purchase Orders New").IsLoaded Then
   GetID = forms("Purchase Orders New").[Order id]
End If
end Function

 

[plog]

This sounds very weird. What form is this button on? I'm assumingy it is on whatever form is calling this function. which means you know what form is open. Further if it is one of those forms you can simply use Me! instead of the form's name.

OK, so I have a cmd button that runs a report which uses the criteria I want as above

This too is a convoluted way to achieve what you want. User clicks a button on a form which runs codes which opens a report which is based on a query which gets its criteria from the form. I would do this with a DoCmd.OpenReport instead.

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

The criteria is removed from the query, the user clicks a button code compiles the criteria to use and opens the report using that criteria. This also avoids the issue of no form being open.

Lastly, you're code does nothing other than set the value of a variable that is never used. You set strFormName in your code, but never do anything with it. Further, its not connected to that query field because you are calling IsLoaded() and the name of your function isn't IsLoaded() its an event on a button. If you were to pursue this path, which I don't recommend, you need to build an IsLoaded() function that returns True or False which would allow your IIF in the query to work.

 

[Isaac]

That's not what I meant.

Try something more along the lines of:

Function GetID() as long
if IsLoaded([Forms]![Purchase Orders])=true then
    GetID=[Forms]![Purchase Orders]![OrderID]
else
    GetID=[Forms]![Purchase Orders NEW]![OrderID]
end Function

And then refer to GetID() in your query...

(untested aircode, hope i got the syntaxes right)

 

[Isaac]

Okay majp beat me to the demo, fast fingers. the idea is you write a vba function that returns something and then refer directly to it..
and thank you for correcting the isloaded syntax