Solved Query expression Iif to choose between one form control or another form control (1 Viewer)

Larkin

New member
Local time
Today, 12:05
Joined
May 29, 2022
Messages
19
Hey all,

DB background: consists of 5 tables, multiple forms, reports and queries. This db is being designed with low tech end users in mind, there for everything is basically form operated.

The issue: There is a report that is generated when an order is entered, this report is pulled from a query that combines all tables. This query is designed to be operated with a criteria that filters one of the tables primary keys to return a single record. The report is generated on order entry, but also needs to be generated on a status form as well. In a attempt to avoid two versions of the report and two versions of the query I am attempting to create an expression in the query to pick which form it checks for the value to use as criteria. I have attempted multiple different expressions, and I do have the expression functional except it still pops the parameters box for the unloaded form. If you null it out the expression continues as expected. I am looking to have it not show the enter parameter box and have you null it out to continue.

Expr: Iif(IsNull([Forms]![FormName]![ControlName]),[Forms]![FormName2]![ControlName2],[Forms]![FormName]![ControlName])

TL;DR: The above expression works, but will still pop the parameter box for the first form if it is not loaded. I have also tried IsMissing, IsObject, IsEmpty as hail Mary's. If I have to create a module I will, was just hoping to avoid it.

Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Perhaps you could consider using a TempVar for your criteria. Whichever form is used to open the report, make it set the TempVar.
 

Larkin

New member
Local time
Today, 12:05
Joined
May 29, 2022
Messages
19
Hi. Welcome to AWF!

Perhaps you could consider using a TempVar for your criteria. Whichever form is used to open the report, make it set the TempVar.
That moment where you are so invested in making something work the way you want it to, you ignore the easy solutions. Thank you, I shall set that up tomorrow morning!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:05
Joined
Oct 29, 2018
Messages
21,358
That moment where you are so invested in making something work the way you want it to, you ignore the easy solutions. Thank you, I shall set that up tomorrow morning!
Good luck! Let us know how it goes.
 

Larkin

New member
Local time
Today, 12:05
Joined
May 29, 2022
Messages
19
Worked like a charm. Thank you again. I was so stuck doing it the complicated way, I didnt even think about using a TempVar for it, which I had a TempVar in one of the forms that already had the required data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 19, 2002
Messages
42,971
You can only reference a form field if the form is open. Using a Tempvar will work but it needs to be set.

Another alternative is rather than including the criteria in the report's RecordSource query, use the WHERE part of the OpenReport method.
 

Larkin

New member
Local time
Today, 12:05
Joined
May 29, 2022
Messages
19
You can only reference a form field if the form is open. Using a Tempvar will work but it needs to be set.

Another alternative is rather than including the criteria in the report's RecordSource query, use the WHERE part of the OpenReport method.
Pat, yeah I am aware of that, and that was what I was looking for was effectively telling the query to ignore criteria from non-loaded forms. I ended up just using TempVar as suggested.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 19, 2002
Messages
42,971
That's fine. I was only explaining the problem and a standard alternative for people who find this post later since the suggestion was "try this" with no explanation of why what you were doing would not work.
 

Users who are viewing this thread

Top Bottom