Query Criteria that Checks if a Form is Open

[PeakH]

I have a query that currently prompts the user to enter a confirmation number [ConfID] whenever it is run. I would like to change the criteria, so that IF the Form that is used to enter records is open, it pulls the ConfID from the current record on the form, and otherwise continues to prompt the user to enter the ConfID.

I have tried variations of =IIf(CurrentProject.AllForms("F-OrderConf").IsLoaded=TRUE, [Forms]![F-OrderConf]![ConfID], [Enter ConfID])
but am told that "IsLoaded" is an invalid syntax.

I don't know how to code, so am hoping this can be solved using the expression builder.

[Bulzie]

Why not just but button on the Form so that it will run the query based on the current record each time instead of them having to put in a number. Are both the Form and Query based on the same Table?

[aytee111]

In the criteria line in your query, type GetValue().

Then add this function in a new module in VBA (change the names to match your database):

Public Function GetValue()

    If CurrentProject.AllForms("Form1").IsLoaded = True Then
        GetValue = Forms!Form1!f1
    Else
        GetValue = InputBox("Enter Value")
    End If

End Function

[PeakH]

Yes the Query and Form are based on the same tables (more than one). The Query is effectively only run when a connected report is opened for printing.

The button that is currently on the Confirmation Form doesn't actually run the query, but opens a new PrintOptions form that prompts the user to select what report they would like to print (There are a series of queries and reports all based on the same tables). The user can also print (open the PrintOptions form) directly from the Main Menu without opening the Confirmation Form at all (which is the circumstance that I would still want the user to enter ConfID).

Would it be possible to create an identical PrintOptions form with print buttons that run the query based on the open Confirmation Form? And how would I get the buttons to do that when the Query still has the [Enter ConfID] Criteria?

[PeakH]

I didn't see the response from aytee111 before responding myself, but your suggestion worked!

Thank you!
And that was my first go at VBA and modules. (Not as scary as I thought)

[aytee111]

A whole new world - go get 'em!