Where Condition - Need Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone! Using A02 on XP. I have a validation macro that checks for
rules and then will preview the report if the macro doesn't stop for a rule
violation. However, I need to preview the report for just the record showing
on my form.

I know how to make a button to print just the one by using this wordage:

DoCmd.OpenReport "rPlanAuditMain", acViewPreview, , "[AuditID]=" &
Me![AuditID]
DoCmd.RunCommand acCmdZoom100

If I just add the above wordage to the button running the macro, I get the
preview whether there are rule violations or not. I'm trying to add the
preview report at the end of the macro rather than have a validate button AND
a preview button as users may preview before testing the rules. (I can also
have the preview button visible or not visible based upon the macro button
being clicked but want to avoid 2 buttons.)

I have the macro working fine except for the Where Condition. I've tried
the expression builder and got:

[Reports]![rPlanAuditMain]![AuditID]=[Forms]![fPlanAuditMain]![AuditID]

But, when I run the macro, I get the dialog box asking AuditID.

Can someone help? I know it's something simple but can't figure it out.

Thanks in advance for any assistance!
 
Bonnie,

First comment to make here, I suppoise, is that this is not a macro. It
is a VBA procedure, which is a different kettle of fish entirely.

But since you're here :-) ...

Well, it is certainly possible to have the validation code and the
OpenReport method in the same procedure. Without knowing any details at
all about the validation, I can't be explicit. But it seems to me that
all you need to do is nest your OpenReport within an If...Then,
something along these lines...

If < your validation conditions > Then
DoCmd.OpenReport "rPlanAuditMain", acViewPreview, , "[AuditID]=" &
Me![AuditID]
DoCmd.RunCommand acCmdZoom100
Else
MsgBox "invalid data"
End If
 
Thanks Steve for the info. I was hoping to do it all with the macro but will
work in VB for it. Thank you very much for participating in the newsgroups.
I have learned SO MUCH from all you guys.
--
Bonnie W. Anderson
Cincinnati, OH


Steve Schapel said:
Bonnie,

First comment to make here, I suppoise, is that this is not a macro. It
is a VBA procedure, which is a different kettle of fish entirely.

But since you're here :-) ...

Well, it is certainly possible to have the validation code and the
OpenReport method in the same procedure. Without knowing any details at
all about the validation, I can't be explicit. But it seems to me that
all you need to do is nest your OpenReport within an If...Then,
something along these lines...

If < your validation conditions > Then
DoCmd.OpenReport "rPlanAuditMain", acViewPreview, , "[AuditID]=" &
Me![AuditID]
DoCmd.RunCommand acCmdZoom100
Else
MsgBox "invalid data"
End If

--
Steve Schapel, Microsoft Access MVP

Bonnie said:
Hi everyone! Using A02 on XP. I have a validation macro that checks for
rules and then will preview the report if the macro doesn't stop for a rule
violation. However, I need to preview the report for just the record showing
on my form.

I know how to make a button to print just the one by using this wordage:

DoCmd.OpenReport "rPlanAuditMain", acViewPreview, , "[AuditID]=" &
Me![AuditID]
DoCmd.RunCommand acCmdZoom100

If I just add the above wordage to the button running the macro, I get the
preview whether there are rule violations or not. I'm trying to add the
preview report at the end of the macro rather than have a validate button AND
a preview button as users may preview before testing the rules. (I can also
have the preview button visible or not visible based upon the macro button
being clicked but want to avoid 2 buttons.)

I have the macro working fine except for the Where Condition. I've tried
the expression builder and got:

[Reports]![rPlanAuditMain]![AuditID]=[Forms]![fPlanAuditMain]![AuditID]

But, when I run the macro, I get the dialog box asking AuditID.

Can someone help? I know it's something simple but can't figure it out.

Thanks in advance for any assistance!
Click to expand...
Click to expand...
 
That's good, Bonnie. Let us know if you need any further help.

It is certainly possible to use a macro for this... I was just taking my
lead from the VBA code you provided in your initial question! If you
need more explicit help, you would need to provide details of the data
validation procedure you are doing.
 
Hi again Steve,

Here are a couple of lines from my macro:

Condition:
[CI1] Is Null Or [CI2] Is Null Or [CI3] Is Null Or [CI4] Is Null Or [CI5] Is
Null
Action: Msgbox "You haven't completed all the...blah blah...check your data."
Cancel Event, Stop Macro

Condition:
[ShortPlanYearApplies]=Yes And ([SPY1] Is Null Or [SPY2] Is Null Or [SPY3]
Is Null Or [SPY4] Is Null Or [SPY5] Is Null)
Action: Msgbox "You haven't completed all the...blah blah...check your data."
CancelEvent, StopMacro

There are about a dozen segments that are just checking data entry (no
questions unanswered). The last line (for now) has no condition but the
Action is SetValue; Item - [PrevRpt].[Visible]; Expression - True (I have a
button with the print preview that I have Visible=False until the macro makes
it Visible=Yes IF no errors.

I would like to replace the last line with the preview report 'Where' my
record specific field [AuditID] is the link so I only print only the record
showing on my form. I just need to know how to complete the Where condition
part of the OpenReport. I can't get the syntax right. It opens the report
to preview but I get all the records in the table rather than just the record
showing on my form. The field [AuditID] is on my subform fPlanAuditSub. It
is an autogenerated incremented number field and is set when the record is
created.

Hope that I have explained it well enough. Let me know if you can help me
with the correct wordage.

Thanks for staying with the thread. I appreciate your help.
 
Back
Top