How have report show detail if a form checkbox is true

I have a report that has detail records showing.  One of the fields on the report record is chkbxPendingReturn.  But first a form is opened that has a field named chkbxPendingReturn on it.  If the forms checkbox is checked then I want the detail record to show on the report.  But of the forms checkbox is not checked then I do not want the record to display on the report.  

How can I do this?

--Steve
SteveL13Asked:
Who is Participating?
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
Yes. PendingReturn needs to be ON the report (it can have Visible=No if you don't want it to show)

replace "' ... put code in to construct vWhere -- we need to discuss this more" with:
   if nz(me.chkbxPendingReturn,false) = true then
      vWhere = "PendingReturn =true"
   end if

Open in new window

assuming PendingReturn  is a Yes/No field in the source table
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
terminology correction: forms and reports have controls, not fields. A control can contain a field.

on the report OPEN event:
me.detail.visible =  nz(forms!Formname!chkbxPendingReturn,true)

Open in new window

this will display or show the whole detail section. If you meant just one control, this can be changed.
0
 
SteveL13Author Commented:
This didn't work.  With the suggested code no detail records display if the checkbox on the form is false.  If I marked it true on the form, then all records display.

If the checkbox is true, then I only want the records to display on the report that have the field on the report chkbxPendingReturn as true.  If I mark it false then I want all of the records to display on the report.  I maybe didn't make it clear in the original post.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
oh, then you want the data filtered.  Are you using DoCmd.OpenReport?
0
 
SteveL13Author Commented:
Yes...

DoCmd.OpenReport "rptAudit", acViewPreview, "", "", acNormal
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "But first a form is opened that has a field named chkbxPendingReturn on it"

do you mean it has a control with that name?
What is the Control Source? (that is the field)

If the control is unbound, it won't have a control source -- still need to know which field name it correlates to
   'declare variable(s)
   dim vWhere as variant
   'initalize value(s)
   vWhere  = null
   ' ... put code in to construct vWhere -- we need to discuss this more

   DoCmd.OpenReport "rptAudit", acViewPreview,  , vWhere

Open in new window

0
 
SteveL13Author Commented:
The checkbox on the form is unbound.  The reports record source is a query.  That query has a field named PendingReturn.

Does that help?
0
 
SteveL13Author Commented:
Perfect!  Thank you very much.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.