Solved

Make Access Sub-report visible or not visible based on record number in detail section of main report

Posted on 2013-01-23
16
1,837 Views
Last Modified: 2013-02-05
Hello Experts !!

I have several sub-reports all present with in the detail section of an Access report.
Some sub-reports need only show in even # records, and others only once (in detail record 1).

What VBA code can I use, and in which event do I do this?
Seems I need a counter (to count which detail record is current), and write code around this counter.

Any Ideas appreciated,

Larry
0
Comment
Question by:Larry Biederman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38810473
Use the Current Event of your main form.

The VBA to display a subform on even records :


 
       Me.MySubformControlName.Visible = (Me.RecordID MOD 2 = 0)

Open in new window


Or for just the first record:

 Me.MySubformControlName.Visible = (Me.RecordID = 1)

Open in new window


etc...
0
 

Author Comment

by:Larry Biederman
ID: 38810812
mbizup,

Thanks!  Will give a try.
How does "mod" function?
-Larry
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38810902
<<
How does "mod" function?
>>

Mod is an arithmetic function (which happens to be included in VBA) which will give you the remainder of division between two numbers.

So...

x mod y is the remainder from x/y

To test if a number is odd or even, you can divide it by two and see if there is a remainder

eg:  4/2 = 2 with a remainder of 0
       5/2 = 2 with a remainder of 1

Since Mod gives you the remainder of the division:
       4 Mod 2 = 0
       5 Mod 2 = 1

And in general...

--  an even number mod 2 will give you 0
--  an odd number mod 2 will give you 1

(That's your math lesson for today :-)  )
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Larry Biederman
ID: 38810946
I'm get error on (Me.RecordID = 1)

Should it be substituted for and actual object (textbox etc)?
 
To make invisible on records 2 on should I use something like this:

if (Me.RecordID = 1) then
  Me.MySubformControlName.Visible = true
else
  Me.MySubformControlName.Visible = false
end if

Thanks, Larry
0
 

Author Comment

by:Larry Biederman
ID: 38810989
Thanks for math lesson.

I do remember mod from my vb6 days, but didn't know if use was different here.
Thanks!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38811122
>> Should it be substituted for and actual object (textbox etc)?

Not necessarily a textbox (although that will work too) ... but it does need to be a valid field name from the underlying table or query.

What is the actual name of the field that you are using to determine whether to hide/show the form?  If you substitute that field name, it should work.  

You'll also have to subsitute the correct name of your subform.

Your block of code for hiding records that are not record 1 looks good... you just need to subsitute the correct fieldname for RecordID and subform for MySubformControlName.
0
 

Author Comment

by:Larry Biederman
ID: 38812167
Having Problems:

I'm using this code:

Private Sub Report_Current()

If Me.TxtNozzle1 = 1 Then
  Me.ShellRpt_sub.Visible = True
Else
  Me.ShellRpt_sub.Visible = False
End If

End Sub

TxtNozzle1 is a text box (located in details section) bound to a field in the query that feeds the details section of report. I have four records.  The value of TxtNozzle1 will be 1,2,3,4 as each detail record is printed.

Problem 1: I have pressed an f9 on the first line of code (If....), so it will stop when this module executes.  It never does !!

Problem 2: the value of Txtnozzle1 (when it appears on the printout is 4,8,12,16.

First problem is only one I'm concerned about, as I can use value 4, to make sub-report visible.

Any idea why "Private Sub Report_Current()" code never executes?  It is located in main report, not sub-report

Thanks, Larry
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38812269
Yikes!   I just realized we are talking about a report not a form.

In a report, use the Detail section's format event. Instead.   If that doesn't help,  post a sample database.   Actual data is not important if it is sensitive.
0
 

Author Comment

by:Larry Biederman
ID: 38812654
Thanks. Will try again in morning
0
 

Author Comment

by:Larry Biederman
ID: 38815396
mbizup,

OK, got most of it working!
Another sub-report has recordset with 4 records. It has the same TxtNozzle1 column in each record.  How do I use TxtNozzle1 as filter so sub-report selects only that row, each time another detail record is printed?

So, when detail record 1 is printed, it selects row where TxtNozzle1 =1.
When detail record 2 is printed, it selects row where TxtNozzle1 =2, etc.

I see the 'filter' and 'filter on load' properties, which may be useful.
Your thoughts....

Thanks, Larry
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38815930
Hi -

I'm having a hard time picturing that one...  can you maybe arrange your next post, using numbers, to visually/graphically show what you are trying to accomplish?
0
 
LVL 61

Accepted Solution

by:
mbizup earned 400 total points
ID: 38816374
Ah!  I think I understand what your're saying - you want to filter your subreport down to just the record that matches the parent report's TxtNozzle1, right?

You might be able to do this without code... by setting the Master/child links on that subreport to include whatever field txtNozzle1 contains.  If you do that, the main report will automatically control the subreport records as you are describing.

Have you tried it that way yet?
0
 

Author Comment

by:Larry Biederman
ID: 38835984
Mbizup,

thanks, I will give that idea a try today.
-Larry
0
 

Author Comment

by:Larry Biederman
ID: 38857391
Master/child works!  
Finally, in which procedure do I put code to make these sub-reports visible or not visible?

With that, I'll award you the points.
Thanks,
-Lbiederman
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 400 total points
ID: 38857406
Try the code in the Format Event of the main report's detail section.

(aka: main report's Detail Format Event)
0
 

Author Closing Comment

by:Larry Biederman
ID: 38857480
Its all working!
Now I just need a table to control which sub-report is visible or not visible.

Thanks Much,
Lbiederman
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question