Macro Condition for Option Group & Combo Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 30 Dec 2005 15:27 GMT

On a Print Report Dialog form, I have an Option Group to print reports. The
user selects which report they want to print. Below the option group, I have
a combo box for Division, then another combo box which filters the Region
based on the Division selected in the previous combo box. Currently, I have
a where condition in the macro, for the report selected, that allows the user
to run all divisions by not selecting anything in the combo boxes. Also in
the condition is the selection of Division then Region. I would like to add
the ability to also run just the division (if no region selected), but I am
not sure how to write the condition.

Action: OpenReport
Report Name: rptActual
View: Print Preview
Where Condition: =IIf([Forms]![frmBackupRptDialog]![cboRegion] Is
Null,"","[Region] = Forms![frmBackupRptDialog]![cboRegion]")

Reply to this Message

Steve Schapel - 30 Dec 2005 18:57 GMT

Karen,

It would probably be preferable to add the references to your Division
and Region comboboxes to the Criteria of the query that the report is
based on, rather than writing a complex Where Condition into the macro.
But, without testing, I am pretty sure it would be something like this
(as far as I know, it would never be applicable to use an IIf() function
in the way you have)...
Where Condition:
([Region]=[Forms]![frmBackupRptDialog]![cboRegion] Or
[Forms]![frmBackupRptDialog]![cboRegion] Is Null) And
([Division]=[Forms]![frmBackupRptDialog]![cboDivision] Or
[Forms]![frmBackupRptDialog]![cboDivision] Is Null)

Signature

Steve Schapel, Microsoft Access MVP

> On a Print Report Dialog form, I have an Option Group to print reports. The
> user selects which report they want to print. Below the option group, I have
[quoted text clipped - 11 lines]
> Where Condition: =IIf([Forms]![frmBackupRptDialog]![cboRegion] Is
> Null,"","[Region] = Forms![frmBackupRptDialog]![cboRegion]")

Reply to this Message

Karen - 30 Dec 2005 19:13 GMT

Thanks, this is what I needed. I got the macro from an Access book that
showed how to create a Print Report Dialog form and to use Option Groups,
List Boxes and macros on the form. I just needed to expand on the concept.

> Karen,
>
[quoted text clipped - 25 lines]
> > Where Condition: =IIf([Forms]![frmBackupRptDialog]![cboRegion] Is
> > Null,"","[Region] = Forms![frmBackupRptDialog]![cboRegion]")

Reply to this Message

Steve Schapel - 30 Dec 2005 19:47 GMT

Karen,

The overall concept of what you are doing is good, and I didn't mean to
imply otherwise. I just wanted to point out that putting the criteria
into the query would be easier than using the Where Condition argument
in the macro.

Signature

Steve Schapel, Microsoft Access MVP

> Thanks, this is what I needed. I got the macro from an Access book that
> showed how to create a Print Report Dialog form and to use Option Groups,
> List Boxes and macros on the form. I just needed to expand on the concept.

Reply to this Message

Karen - 30 Dec 2005 19:54 GMT

Thanks, I agree. It became more involved as I was working on the form.

> Karen,
>
[quoted text clipped - 6 lines]
> > showed how to create a Print Report Dialog form and to use Option Groups,
> > List Boxes and macros on the form. I just needed to expand on the concept.

Reply to this Message